Search code examples
sqloracle-databasedatabase-partitioning

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 );
Solution

  • 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.