Partition Pruning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Partition Pruning

  1. #1
    Join Date
    Jun 2001
    Posts
    40
    Dear All

    I am attempting to implement partition pruning on an 8.1.7.0.1 database running on SuSE Linux Professional 7.0. However the optimizer is choosing not to recognise the partitions. I have verified that the optimizer_mode=choose and also I've have analyzed the partitioned tables (compute not estimate statistics) but this seems to make no difference. I even tried this simple example:
    ========================================
    drop table cb_test;

    create table cb_test (id number(5), part number(5), texte varchar2(50))
    PARTITION BY RANGE ( part)
    (PARTITION part_q1 VALUES LESS THAN ( 1000 ),
    PARTITION part_q2 VALUES LESS THAN ( 2000),
    PARTITION max values less than (maxvalue));

    BEGIN
    FOR I in 1 .. 99999 LOOP
    INSERT INTO cb_test VALUES (i, i, to_char(i)||'TOTO');
    END LOOP;
    END;
    /

    analyze table cb_test compute statistics;

    explain plan set statement_id = 'bsw' for
    select * from cb_test where part = 500;

    select operation, options, object_name
    from plan_table
    where statement_id = 'bsw';
    ========================================
    The output is as follows:
    ========================================
    Table dropped.


    Table created.


    PL/SQL procedure successfully completed.


    Table analyzed.


    Explained.


    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ ------------------------------ ------------------------------
    SELECT STATEMENT
    TABLE ACCESS FULL CB_TEST
    SELECT STATEMENT
    TABLE ACCESS FULL CB_TEST
    SELECT STATEMENT
    TABLE ACCESS FULL CB_TEST
    SELECT STATEMENT
    TABLE ACCESS FULL CB_TEST
    ========================================

    My database is not running parallel query, but my understanding is that this should make no difference. Could anybody out there offer any advice? Has anyone managed partition pruning without parallel query?

    Any help would be much appreciated

    Thanks

    Austin


  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try to experiment with each partition on different tablespace.

  3. #3
    Join Date
    Jun 2001
    Posts
    40
    Thanks for the advice. It turned out that it was doing the pruning after all, it just wasn't appearing to in my explain plan query. I discovered this with sql_trace. The Tkprof output showed that the full table scan stopped and started at partition 1! Looks like paralell query is not mandatory for partition pruning to work.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    select '| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |' as "Plan Table" from dual
    union all
    select '--------------------------------------------------------------------------------' from dual
    union all
    select rpad('| '||substr(lpad(' ',1*(level-1))||operation||
    decode(options, null,'',' '||options), 1, 27), 28, ' ')||'|'||
    rpad(substr(object_name||' ',1, 9), 10, ' ')||'|'||
    lpad(decode(cardinality,null,' ',
    decode(sign(cardinality-1000), -1, cardinality||' ',
    decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
    decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
    trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
    lpad(decode(bytes,null,' ',
    decode(sign(bytes-1024), -1, bytes||' ',
    decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
    decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
    trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
    lpad(decode(cost,null,' ',
    decode(sign(cost-10000000), -1, cost||' ',
    decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
    trunc(cost/1000000000)||'G'))), 8, ' ') || '|' ||
    lpad(decode(partition_start, 'ROW LOCATION', 'ROWID',
    decode(partition_start, 'KEY', 'KEY', decode(partition_start,
    'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6),
    'NUMBER', substr(substr(partition_start, 8, 10), 1,
    length(substr(partition_start, 8, 10))-1),
    decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|' ||
    lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L',
    decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
    'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
    'NUMBER', substr(substr(partition_stop, 8, 10), 1,
    length(substr(partition_stop, 8, 10))-1),
    decode(partition_stop,null,' ',partition_stop)))))||' ', 7, ' ')||'|' as "Explain plan"
    from plan_table
    start with id=0 and timestamp = (select max(timestamp) from plan_table
    where id=0)
    connect by prior id = parent_id
    and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
    and prior timestamp <= timestamp
    union all
    select '--------------------------------------------------------------------------------' from dual
    /

    Run the above query after a statement is explained.

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