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

Thread: partition pruning with local index

Threaded View

  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

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