Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Partition elimination/pruing

    Hi,

    I have partition table INVOICE_LINE(55 partitions) This table is having LOCAL composite index on
    TWO COLUMNS DB_CODE & FISCAL_WEEK. My problem is why partition pruing is not happing.
    Query
    PHP Code:
    Select from
    db_master
    .db_code=invoice_line.db_code 
    PHP Code:
    --------------------------------------------------------------------------------------
    Id  Operation            |  Name         Rows  Bytes Cost  PstartPstop |
    --------------------------------------------------------------------------------------
    |   
    SELECT STATEMENT     |               |  9212K|  1757M78653 |       |       |
    |*  
    |  HASH JOIN           |               |  9212K|  1757M78653 |       |       |
    |   
    |   TABLE ACCESS FULL  DB_MASTER     |    62 |  6200 |     |       |       |
    |   
    |   PARTITION RANGE ALL|               |       |       |       |     |    55 |
    |   
    |    TABLE ACCESS FULL INVOICE_LINE  |  9212K|   878M78528 |     |    55 |
    -------------------------------------------------------------------------------------- 
    The explain plan show that optimiser is searching all 55 partitions.Why it is not eliminating
    Partitions?

    Thanks in Advance
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Some of your select statement appears to be missing:
    Select * from
    db_master.db_code=invoice_line.db_code

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Sorry typo mistake. The actual sql query is
    PHP Code:
    SQL>explain plan for
      
    2   Select from
      3    DATAP
    .DB_MASTER ,
      
    4    DATAP.INVOICE_LINE
      5    Where DB_MASTER
    .DB_CODE=INVOICE_LINE.DB_CODE;

    Explained
    explain plan
    PHP Code:
    SQL>select plan_table_output from table(dbms_xplan.display('plan_table',null,'ALL'));

    --------------------------------------------------------------------------------------
    Id  Operation            |  Name         Rows  Bytes Cost  PstartPstop |
    --------------------------------------------------------------------------------------
    |   
    SELECT STATEMENT     |               |  9212K|  1757M78653 |       |       |
    |*  
    |  HASH JOIN           |               |  9212K|  1757M78653 |       |       |
    |   
    |   TABLE ACCESS FULL  DB_MASTER     |    62 |  6200 |     |       |       |
    |   
    |   PARTITION RANGE ALL|               |       |       |       |     |    55 |
    |   
    |    TABLE ACCESS FULL INVOICE_LINE  |  9212K|   878M78528 |     |    55 |
    --------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       
    access("DB_MASTER"."DB_CODE"="INVOICE_LINE"."DB_CODE"

    Thanks in Advance
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Why do you expect partition pruning? The query joins all INVOICE_LINE rows to their corresponding DB_MASTER rows. Therefore I would expect that all partitions of INVOICE_LINE would be involved in the query.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It can only do partition pruning if you specify values for the leading columns in the partition key. Unfortunately your query does a join on the leading partition key column but doesn actually specify a value i.e. INVOICE_LINE.DB_CODE=234.

    Alan

Posting Permissions

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