一次从几个分区中选择 [英] Select from several partitions at once

问题描述

请问我的英语水平. 我有2个表，两个表都按日期间隔分区，但是在不同的字段上. 两个表中都有大量记录(每个分区中约100kk). 第一个表在快速discks表空间中保留3个最后(按日期)分区，其他表在慢速discks表空间中保持. 我也有一些系统来处理数据.它并行执行进程，每个进程都通过select语句从第一个表中获取数据，并将处理后的数据放入第二个表中. 因此，我只需要从快速"(！)分区的第一个表中选择数据，即可将其放入第二个表中. 但是第二张表也分区在其他(日期)字段上.当并行执行的进程在不同的进程试图将数据放入第二张表的同一分区时会出现死锁.

Excuse me for my english. I have 2 tables, both partitioned by date interval, but on different fields. There is a big amount of records in both tables(~100kk in each partition). First table keep it's 3 last(by date) partitions in fast discks tablespace, others partitions is in slow discks tablespace. Also I have some system, which processing data. It execute processes in parallel, each one get data from first table by select statement and put processed data into second table. So I need select data from first table only from "fast"(!) partitions to put it in second table. But second table partitioned on other(date too) field. And when processes executing in parallel I get deadlocks when different processes trying to put data into the same partition in 2nd table.

对于每个进程来说，好的解决方案是仅从快速"分区(但一次全部)中获取数据，而仅从第二个表中的一个分区获取数据.在这种情况下，每个进程都会将数据推送到一个分区中.但是我不知道该怎么做.

Good solution is for each process take data from only "fast" partitions(but all of them in one time) only data for one partition in 2nd table. In this case each process will push data in one partition. But I don't have any idea how to do it.

如果我做

select t.field1, t.field2 from (select * from FIRST_TABLE partition("P1") union all
select * from FIRST_TABLE partition("P2") union all
select * from FIRST_TABLE partition("P3")) t
where t.field3='someVal' --Indexed field in FIRST_TABLE 

OracleDB会在FIRST_TABLE的分区上使用本地索引来解决从句吗?这种方式将如何影响性能?

will OracleDB use local indexes on partitions in FIRST_TABLE to resolve where-clause? How will this way affect the performance?

有什么想法可以解决我的问题吗?

Any ideas to solve my problem?

PS关于如何在一个选择语句中从多个分区中选择数据有很多问题，但是我没有找到对我的情况有用的答案.

PS It's a lot of questions about how to select data from several partitions in one select-statement, but I didn't found answer usefull for my situation.

推荐答案

查询分区表时，您几乎永远不想使用PARTITION子句.您几乎总是想指定一个谓词，以允许Oracle自己进行分区修剪.

You almost never want to use the PARTITION clause when querying a partitioned table. You almost always want to specify a predicate that allows Oracle to do partition pruning on its own.

SELECT t.column1, t.column2
  FROM first_table t
 WHERE t.partitioned_date_column >= <<date that delimits fast partitions>>
   AND t.column3 = 'someVal'

在对表进行分区的日期列上指定谓词时，Oracle可以自动确定需要访问的分区.

When you specify a predicate on the date column that the table is partitioned on, Oracle can automatically determine which partition(s) need to be accessed.

这篇关于一次从几个分区中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！