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

[mike9]

Oracle verison?

[manoranjand]

9i/10g

I have tested in both 9i rel2 and 10g rel1

Cheers
manoranjan

[hrishy]

Hi

I would be interested in seeing the autotrace for both the statements.

regards
Hrishy

[manoranjand]

The o/p already given to you are from
"set autotrace traceonly explain" only. In case you need any additional dat a please let me know

Thank You

[hrishy]

Hi

Well the answer is simple both the queries are different.

select count(*) from emptest where deptno=10;

I see that you have not created a index on deptno :-)

The query that is equivalent of
select count(*) from emptest
is
select count(*) from emptest partition(p1);

(dont look at the result i am saying optimizer wise they are equivalent now thats a term i made up)

I hope it answers your question

regards
Hrishy

[manoranjand]

Hrishy Thanks for your reply. I got my answer from the following link
http://asktom.oracle.com/pls/ask/f?p...:1493222538541

.... Actually for non-prefixed index Oracle CBO is not geared up to handle this special and rare case

Cheers

[hrishy]

Hi

Yeah not only oracle i dont think so any database can do what you are asking for.

regards
Hrishy