Select from several partitions at once
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'
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.
Andrey
Updated on July 09, 2022Comments
-
Andrey almost 2 years
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.
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?
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.
-
Andrey over 10 yearsWhat if I will need to move one of older partition into fast tablespace and reprocess data(sorry, I didn't wrote about this restriction in question)? This way doesn't allow to define separated partition? not laying one after another.
-
Justin Cave over 10 years@Andrey - I'm not sure how that changes anything. You would still want to specify a predicate on
partitioned_date_column
that specifies which partitions need to be read from. If you are saying that the date ranges are potentially non-contiguous, you may need a couple of predicates that areOR
'd together rather than a>=
or you may need anIN
list. -
Andrey over 10 yearsIf I write
AND( g.create_date >= to_date('2013-09-01','yyyy-mm-dd') OR g.create_date >= to_date('2013-06-01','yyyy-mm-dd') )
it take all partitions after 2013-06-01(because it relate to condition), isn't it? -
Justin Cave over 10 years@Andrey - Well, yes, if you code it that way. If you want everything since 9/1 and everything from 6/1 to 6/30, for example, you'd want the
OR
condition to be aBETWEEN
or a>= date '2013-06-01' and <= date '2013-06-30'
. Your condition would need to match whatever set of date values you want to process. -
Andrey over 10 yearsIs this caused by the optimizer, how to get data from table - from exect partitions or full scan whole table to check the condition? How can I check that in case of
and( g.create_date between to_date('2013-09-01','yyyy-mm-dd') and add_months(to_date('2013-09-01','yyyy-mm-dd'),1) or g.create_date between to_date('2013-06-01','yyyy-mm-dd') and add_months(to_date('2013-06-01','yyyy-mm-dd'),1) )
condition, DB using only partitions I specified? It is unacceptable to using somehow of slow disks(performance is critical). Sory for my meticulousness, I just want to be shure=) -
Justin Cave over 10 years@Andrey - The query plan will show that partition elimination is taking place. You'll see a
PSTART
andPSTOP
ofKEY
and the predicate will show the predicate. -
Andrey over 10 yearsFor query
SELECT * FROM FIRST_TABLE g, SOME_OTHER_TABLE s WHERE flag_entry = 'Y' and g.COLUMN1 = s.COLUMN1 and g.COLUMN2 = s.COLUMN2 and g.COLUMN3 between to_date('2013-07-01','yyyy-mm-dd') and add_months(to_date('2013-07-01','yyyy-mm-dd'),1) --SECOND_TABLE (OUT) partitioned by same value and( g.create_date between to_date('2013-09-01','yyyy-mm-dd') and add_months(to_date('2013-09-01','yyyy-mm-dd'),1) or g.create_date between to_date('2013-06-01','yyyy-mm-dd') and add_months(to_date('2013-06-01','yyyy-mm-dd'),1) )
-
Andrey over 10 yearsI see plan like this(in PL/SQLDeveloper), : It did not fit in the previous post, here is only part about FIRST_TABLE.
PX RECEIVE --PX SEND HASH SYS :TQ10001 ----PX BLOCK ITERATOR ------TABLE ACCESS FULL SCH FIRST_TABLE
Here is noPSTART
orPSTOP
in it. Is it possible to make new lines in post? Can not find how. -
Justin Cave over 10 years@Andrey - You can certainly edit your post. I'd use
dbms_xplan
to generate your query plan rather than relying on any GUI. Most GUIs do not display all the columns that are available. -
Andrey over 10 years@Justin Cave - Thank You, You realy help me)
-
Matthew Moisen almost 5 years@JustinCave Would you please explain "You almost never want to use the PARTITION clause when querying a partitioned table."? For example I like to launch 1 thread against each partition for batched jobs, and will often use the PARTITION clause for queries. Is this no good?