Not using Partition in Explain plan

[samdba]

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.

[bensr]

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.

[Orca777]

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

[samdba]

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,

[samdba]

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,

[Calvin_Qiu]

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.

[samdba]

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,

[Orca777]

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

[samdba]

Hi Orca777,

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

Thanks,

Samir.

[Calvin_Qiu]

Have you seen PSTART and PSTOP in your EXPLAIN PLAN?

See Also: Oracle 9i Database Perfornamce Guide and Reference P9-11.