partition pruning with local index
Hi all,
System Info
-----------
Oracle version : 9i/10g
OS : All
-----------
My question is why "select count(*) from emptest where deptno=10;" is doing a full scan(of single partition).
1) It should do a partition pruning for deptno=10 (Which its doing)
2) Then it should go to the local index for that partiton and shd have done a fast full index scan to get the count(*) ,instead of doing a full table scan
My Reasoning:
- When you do a simple count(*) its doing a fast full index scan (all partition).
- when you do count(*) where deptno=10 it should have done a partiton pruning with deptno 10 and then it shd go to the linked local partition index to get it. i.e. it shd have done a fast full index scan (single partition-the local index partition which is linked to table partition with deptno 10).
ANY HELP PLEASE ???
Detail example below :
create table emptest(
EMPNO NUMBER not null,
ENAME VARCHAR2(50),
JOB CHAR(50),
MGR NUMBER,
HIREDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
)
PARTITION BY list (deptno)
(
PARTITION p1 VALUES (10),
PARTITION p2 VALUES (20),
PARTITION p3 VALUES (30)
)
/
begin
for i in 1..50000 loop
insert into emptest values(i,dbms_random.string(NULL,50),
'MGR',i+1,sysdate,10005,923,10) ;
if mod(i,1000)=0 then
commit;
end if;
end loop ;
end;
/
CREATE INDEX emptest_idx ON emptest(empno) LOCAL
(PARTITION ip1,
PARTITION ip2,
PARTITION ip3)
/
exec dbms_stats.gather_table_stats(USER,'EMPTEST',cascade=>true) ;
===============
SQL> select count(*) from emptest ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=288 Card=1)
1 0 SORT (AGGREGATE)
2 1 PARTITION LIST (ALL) (Cost=288 Card=130000)
3 2 INDEX (FAST FULL SCAN) OF 'EMPTEST_IDX' (INDEX) (Cost=
288 Card=130000)
================
SQL> select count(*) from emptest where deptno=10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2519 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 PARTITION LIST (SINGLE) (Cost=2519 Card=130000 Bytes=390
000)
3 2 TABLE ACCESS (FULL) OF 'EMPTEST' (TABLE) (Cost=2519 Ca
rd=130000 Bytes=390000)
==================
Regards,
Manoranjan