partition pruning with local index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: partition pruning with local index

  1. #1
    Join Date
    Mar 2006
    Posts
    5

    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
    Last edited by manoranjand; 03-13-2006 at 01:47 AM. Reason: Add Oracle Version

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    Oracle verison?

  3. #3
    Join Date
    Mar 2006
    Posts
    5
    9i/10g

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

    Cheers
    manoranjan

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

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

    regards
    Hrishy

  5. #5
    Join Date
    Mar 2006
    Posts
    5
    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

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    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
    Last edited by hrishy; 03-13-2006 at 07:33 AM.

  7. #7
    Join Date
    Mar 2006
    Posts
    5
    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

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

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

    regards
    Hrishy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width