Not using Partition in Explain plan
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Not using Partition in Explain plan

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Unhappy

    Hi,

    Oracle 81730/NT.

    I just converted one non-partitioned table into range based partitioned one with effective_date as partitioned column.

    Now, I am running foll. query.

    select * from gl_je_lines
    where effective_date >= to_date('01-JAN-2001')
    and effective_date < to_date('31-JAN-2001')

    Explain Plan
    ---------------
    SELECT STATEMENT (RULE)
    PARTITION RANGE ITERATOR
    TABLE ACCESS FULL GL_JE_LINES (Analyzed)


    What is the meaning of PARTITION RANGE ITERATOR?

    Does it use the partition or full table?

    Thanks in Adv.



    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    What are the partition clauses.
    When there is 1 partition for each date, then it has to access multiple partitions and the iterator seems right to me.
    Regards
    Ben de Boer

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;
    The Patition Range Iterator
    scan all partitions which satisfy the where-clause on the partition-criteria;
    Due to the query every selected partiton is scanned full
    ;

    In the explain-plan there also should the start and stop-pertiition id, so you can see how many partitions are involved;

    Orca

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Bensr,

    There are quarterly partition for the current year and another partition for previous years data.

    So, how to enforce the query to look into only those partitions where data satisfying the condition exist?

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Orca,

    In the plan table, it shows 'KEY' in start and stop-pertiition id. Foll. is the E.P.

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=RULE (Cost=2324 Card=5602 Bytes=3
    9214)

    1 0 PARTITION RANGE (ITERATOR)
    2 1 TABLE ACCESS (FULL) OF 'GL_JE_LINES' (Cost=2324 Card=560
    2 Bytes=39214)

    and Foll. is the partition info.

    partition by range (effective_date)
    (partition gl_je_lines_p0 values less than (to_date('01-JUL-2001','DD-MON-YYYY')),
    partition gl_je_lines_p1 values less than (to_date('01-SEP-2001','DD-MON-YYYY')),
    partition gl_je_lines_p2 values less than (to_date('01-DEC-2001','DD-MON-YYYY')),
    partition gl_je_lines_p3 values less than (to_date('01-APR-2002','DD-MON-YYYY')),
    partition gl_je_lines_p4 values less than (to_date('01-JUL-2002','DD-MON-YYYY')))


    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Partition Range Iterator means using more than one partition but not all them to complete your query, so that it will fully scan one by one.

    If it use all partitions, that will be Partition Range All. If it use only one, Partition Range will not appear in EXPLAIN PLAN.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  7. #7
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Calvin_Qiu,

    Based on my query

    select * from gl_je_lines
    where effective_date >= to_date('01-JAN-2001')
    and effective_date < to_date('31-JAN-2001')

    It should not use all the partitions as this range of data will be in only one partition.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  8. #8
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Originally posted by samdba
    Hi Orca,

    In the plan table, it shows 'KEY' in start and stop-pertiition id. Foll. is the E.P.



    Thanks,
    Ok;
    KEY means that at RUNTIME of the SQL the Optimzer can find the right partition. He cannot find it yet due to date-conversion-function-calls;

    In your case you are right only one partition will be scanned.
    -> gl_je_lines_p0

    Orca

  9. #9
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Orca777,

    Finally, what is the action plan? I did not get it from your answer.

    Thanks,

    Samir.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  10. #10
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Have you seen PSTART and PSTOP in your EXPLAIN PLAN?

    See Also: Oracle 9i Database Perfornamce Guide and Reference P9-11.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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