Partition Pruning Does Not Work With Non Equlaity Operator (Doc ID 977955.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.

Symptoms

When non equality predicate is used in a query against a list partitioned table, then no partition pruning occurs.

The following example demonstrates the issue:

create table jb (id varchar2(20))
PARTITION BY LIST (ID) (
PARTITION closed VALUES ('CLOSED'),
PARTITION notclosed VALUES (DEFAULT)
);

Query with non-equality predicate (id!='CLOSED') scans all partitions.

select * from jb where id!='CLOSED';

Execution plan:
-------------------------------------------------
| Id | Operation        | Name | Pstart | Pstop |
-------------------------------------------------
|  0 | SELECT STATEMENT  |      |        |      |
|  1 | PARTITION LIST ALL|      | 1      |    2 |
|* 2 | TABLE ACCESS FULL| JB    | 1      |    2 |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<>'CLOSED')


Query with equality predicate (id='CLOSED') scans 1 partition only:

select * from jb where id='CLOSED';
Execution plan:
---------------------------------------------------
| Id | Operation           | Name | Pstart| Pstop |
---------------------------------------------------
| 0 | SELECT STATEMENT     |      |       |       |
| 1 | PARTITION LIST SINGLE|      | KEY   | KEY   |
| 2 | TABLE ACCESS FULL    | JB   | 1     | 1     |
---------------------------------------------------


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms