Hello guys,
Please help me on this
CREATE TABLE PARTTAB1(
ordid NUMBER,
PARTCOL DATE,
DETAILS NUMBER,
AMOUNT NUMBER)
PARTITION BY RANGE(PARTCOL)
SUBPARTITION BY HASH(DETAILS) SUBPARTITIONS 2
(PARTITION q1 VALUES LESS THAN(TO_DATE('01-01-2000','DD-MM-YYYY')),
PARTITION q2 VALUES LESS THAN(TO_DATE('01-07-2005','DD-MM-YYYY'))
);
ALTER TABLE "etl"."PARTTAB1" ADD CONSTRAINT "PARTTAB1_PK"
PRIMARY KEY ("DETAILS","PARTCOL") using index local
EXEC dbms_stats.gather_table_stats('etl','PARTTAB1');
explain plan for
select ordid,details
from parttab1
where partcol between '01-01-2000' and '01-01-2001'
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE ITERATOR| | 1 | 35 | 2 (0)| 00:00:01 | KEY | KEY |
| 3 | PARTITION HASH ALL | | 1 | 35 | 2 (0)| 00:00:01 | 1 | 2 |
|* 4 | TABLE ACCESS FULL | PARTTAB1 | 1 | 35 | 2 (0)| 00:00:01 | KEY | KEY |
PLAN shows local index not used. and Table access full..
Please explain
S