partition pruning with local index

[manoranjand]

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