hello everone,
I have question about subpartitions, as you can see my example, I have table with list subpartiton. Now when I write a query include NOT in or not equal for subpartition column, I expect that Oracle will not read these subpartitions but in execution plan I've seen that all of subpartitions are processed. here is an example:
CREATE TABLE T (ID NUMBER, CREATE_DATE DATE, REC_TYPE VARCHAR2(2))
PARTITION BY RANGE (CREATE_DATE)
INTERVAL (INTERVAL '1' MONTH)
SUBPARTITION BY LIST (REC_TYPE)
SUBPARTITION TEMPLATE
(
SUBPARTITION SUB_P_KN VALUES ('ZZ'),
SUBPARTITION SUB_P_KG VALUES ('RR'),
SUBPARTITION SUB_P_DEFAULT VALUES (DEFAULT)
)
(
PARTITION P1 VALUES LESS THAN (TO_DATE('01/01/2012','DD/MM/YYYY'))
);
CREATE SEQUENCE SEQ;
INSERT INTO T VALUES (SEQ.NEXTVAL,TO_DATE('01/01/2012', 'DD/MM/YYYY'),'RR');
INSERT INTO T VALUES (SEQ.NEXTVAL,TO_DATE('05/03/2012', 'DD/MM/YYYY'),'RR');
INSERT INTO T VALUES (SEQ.NEXTVAL,TO_DATE('03/02/2012', 'DD/MM/YYYY'),'RR');
INSERT INTO T VALUES (SEQ.NEXTVAL,TO_DATE('03/02/2012', 'DD/MM/YYYY'),'ZZ');
INSERT INTO T VALUES (SEQ.NEXTVAL,TO_DATE('06/03/2012', 'DD/MM/YYYY'),'ZZ');
INSERT INTO T VALUES (SEQ.NEXTVAL,TO_DATE('18/03/2012', 'DD/MM/YYYY'),'WE');
INSERT INTO T VALUES (SEQ.NEXTVAL,TO_DATE('15/01/2012', 'DD/MM/YYYY'),'ZZ');
INSERT INTO T VALUES (SEQ.NEXTVAL,TO_DATE('31/01/2012', 'DD/MM/YYYY'),'ER');
CREATE INDEX I_t ON T(CREATE_DATE,REC_TYPE) LOCAL;
exec dbms_stats.gather_table_stats(user, 'T');
explain plan for
SELECT * FROM T WHERE CREATE_DATE = TO_DATE('31/01/2012', 'DD/MM/YYYY') AND REC_TYPE not IN ('RR','ZZ'); -- or REC_TYPE != 'RR' is same
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 14 | 2 (0)| 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST ALL | | 1 | 14 | 2 (0)| 00:00:01 | 1 | 3 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 1 | 14 | 2 (0)| 00:00:01 | 4 | 6 |
|* 4 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 | 4 | 6 |
------------------------------------------------------------------------------------------------------------
All 3 subpartition has been red by oracle. why ? I give that ZZ and RR types are not included ?
DB is 11g R2 on IBM AIX server.
Edited by: Mustafa Kalaycı on Jul 26, 2012 11:07 PM