Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table in an Oracle database, partitioned by a field. In our application we need to query data that is scatered across 2 partitions. Can you please tell me which method is better:

  • making multiple selects with UNION ALL like: select * from table where field = 'A' UNION ALL select * from table where field = 'B'

  • making one query: select * from table where field in ('A','B')

share|improve this question
    
The optimiser should automatically do partition pruning if your partitioning strategy is sound. Shouldn't need to code around it. –  Phil Jun 26 at 18:20
    
Yeah I thought this was how it worked - you shouldn't have to jump through too many hoops just because your table is partitioned –  Andrew Brennan Jun 30 at 15:52

1 Answer 1

Oracle will take care of partition pruning when using ** making one query: select * from table where field in ('A','B')

Here is an example:

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

explain plan for
select * from q1_sales_by_region
where state IN ('OR','FL')
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2523828506

------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |    41 |     2   (0)| 00:00:01 |       |    |
|   1 |  PARTITION LIST INLIST|                    |     1 |    41 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 |   TABLE ACCESS FULL   | Q1_SALES_BY_REGION |     1 |    41 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATE"='NY' OR "STATE"='OR')

The KEY(INLIST) designation for the partition start and stop keys specifies that an IN list predicate.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.