CODEWITHSUNDEEP
sqloracle-databasedatabase-partitioning
Yusufk
Yusufk

Reputation: 1172

How does one do a SQL select over multiple partitions?

Is there a more efficient way than:

select * from transactions partition( partition1 ) 
union all 
select * from transactions partition( partition2 ) 
union all 
select * from transactions partition( partition3 );

Upvotes: 13

Views: 59532

Answers (2)

erbsock
erbsock

Reputation: 1217

Can you provide additional context? What are your predicates? What makes you think that you need to explicitly tell the optimizer to go against multiple partitions. You may have the wrong partition key in use, for example.

Upvotes: -2

Justin Cave
Justin Cave

Reputation: 231651

It should be exceptionally rare that you use the PARTITION( partitionN ) syntax in a query.

You would normally just want to specify values for the partition key and allow Oracle to perform partition elimination. If your table is partitioned daily based on TRANSACTION_DATE, for example

SELECT *
  FROM transactions
 WHERE transaction_date IN (date '2010-11-22', 
                            date '2010-11-23', 
                            date '2010-11-24')

would select all the data from today's partition, yesterday's partition, and the day before's partition.

Upvotes: 16

Related Questions