The best way to query a partitioned table in Oracle

oracleunion

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

Best Answer

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.

Related Question