-
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
-
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
-
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
-
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
-
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
-
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
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|