How does one do a SQL select over multiple partitions?

Question

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

Answer 1

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.

Answer 2

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.