Partition Pruning Not Working Through View With Cast Functions (Doc ID 341807.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.6 to 11.2.0.1 [Release 9.2 to 11.2]
Information in this document applies to any platform.

Symptoms

PROBLEM:
--------
A simple select against a partitioned table produces an execution plan that
exhibits partition pruning.  But if a view is created using cast functions,
then the same query against the view produces an execution plan that hits all
partitions.


TEST CASE:
----------

create table a(c1 varchar2(10),c2 varchar2(10),c3 varchar2(8))
partition by range(c3)
(
partition a1 values less than ('20050101'),
partition a2 values less than ('20050201'),
partition a3 values less than ('20050301'),
partition a4 values less than ('20050401')
);

insert into a values('cesar','sanchez','20041205');
insert into a values('cesar','sanchez','20050105');
insert into a values('cesar','sanchez','20050205');
insert into a values('cesar','sanchez','20050305');

commit;

create index ai on a(cast(c3 as char(10))) local;
create view av as
select
cast(c1 as char(10)) c_1,
cast(c2 as char(10)) c_2,
cast(c3 as char(10)) c_3
from a;

SQL> explain plan for
    select * from av where c_3='20050305';
Explained.
SQL> @?/rdbms/admin/utlxpls
-----------------------------------------------------------------------
|Id| Operation                          |Na |R|Bytes| Cost| Psta| Psto|
-----------------------------------------------------------------------
| 0| SELECT STATEMENT                   |   |3|  60 |   1 |     |     |
| 1|  PARTITION RANGE ALL               |   | |     |     |   1 |   4 |
| 2|   TABLE  ACCESS BY LOCAL INDEX ROWID| A |3|  60 |   1 |   1 |   4 |
|*3|    INDEX RANGE  SCAN                | AI|1|     |   5 |   1 |   4 |
-----------------------------------------------------------------------

 

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