oracle 一次从多个分区中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19523061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select from several partitions at once
提问by Andrey
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.
请原谅我的英语。我有 2 个表，都按日期间隔分区，但在不同的字段上。两个表中都有大量记录（每个分区约 100kk）。第一个表在快速磁盘表空间中保留它的最后 3 个（按日期）分区，其他分区在慢速磁盘表空间中。我还有一些处理数据的系统。它并行执行进程，每个进程通过select语句从第一个表中获取数据并将处理后的数据放入第二个表中。所以我只需要从“快速”（！）分区中选择第一个表中的数据以将其放入第二个表中。但是第二个表在其他（日期也是）字段上分区。当进程并行执行时，当不同的进程试图将数据放入第二个表中的同一分区时，我会陷入死锁。
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.
好的解决方案是，每个进程只从“快速”分区（但一次全部）获取数据，仅获取第二个表中一个分区的数据。在这种情况下，每个进程都会将数据推送到一个分区中。但我不知道该怎么做。
If I make
如果我做
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
will OracleDB use local indexes on partitions in FIRST_TABLE to resolve where-clause? How will this way affect the performance?
OracleDB 会在 FIRST_TABLE 中的分区上使用本地索引来解析 where 子句吗？这种方式将如何影响性能？
Any ideas to solve my problem?
有什么想法可以解决我的问题吗？
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.
PS 关于如何在一个选择语句中从多个分区中选择数据有很多问题，但我没有找到对我的情况有用的答案。
回答by Justin Cave
You almost never want to use the PARTITIONclause when querying a partitioned table. You almost always want to specify a predicate that allows Oracle to do partition pruning on its own.
您几乎从不想PARTITION在查询分区表时使用该子句。您几乎总是希望指定一个谓词，允许 Oracle 自行进行分区修剪。
SELECT t.column1, t.column2
FROM first_table t
WHERE t.partitioned_date_column >= <<date that delimits fast partitions>>
AND t.column3 = 'someVal'
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.
当您在表分区的日期列上指定谓词时，Oracle 可以自动确定需要访问哪些分区。
回答by mucio
you can get deadlock if you are trying to execute three queries in parallel on your own, for example running at the same time:
如果您尝试自己并行执行三个查询，例如同时运行，则可能会出现死锁：
insert into t2 select from t1 partition ("P1");
and then in another shell/window/job:
然后在另一个外壳/窗口/作业中：
insert into t2 select from t1 partition ("P2")
If you query
如果你查询
select *
from t1
where date_column_used_for_partition >= 3_dates_ago
Oracle will select only the three partitions that you need and you won't need to use UNION.
Oracle 将只选择您需要的三个分区，您不需要使用UNION.
In this way you can put your INSERT INTO... SELECTstatement in a single query and you don't need to worry about deadlock, the Oracle engine will know in which partition of the second table it has to insert the data and he will manage the inserts for you.
通过这种方式，您可以将您的INSERT INTO... SELECT语句放在单个查询中，您无需担心死锁，Oracle 引擎将知道它必须在第二个表的哪个分区中插入数据，他将为您管理插入。
