Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Doubt on optimizer costs when using date and subquery on dual
Hi All,
I have a doubt on how the optimizer calculates the cost after a slight
change on a simple query.
I provide both queries, in the second one I just replaced the string
'26-JAN-2007' by TO_DATE((SELECT '26-JAN-2007' FROM DUAL),'DD-MON-
YYYY').
For me it is almost the same but the cost raises considerably, I
cannot explain it.
My questions:
Why do I have such differences when using a literal and a select on
DUAL? Is this normal?
In the second query, why the PARTITION RANGE ITERATOR has such cost
while in the first it seems to find the proper partition with lower
cost?
Additional info:
Database version: Oracle9i Enterprise Edition Release 9.2.0.5.0 -
64bit
OS version: HP-UX bdhp4361 B.11.11 U 9000/800 1333061776
My table is partitioned by srce_sys_id and cpos_time_perd_date, it
has a total of 119 partitions and that date corresponds to partition
#118, the first query shows it.
Please ask for additional information if neccessary.
QUERY 1:
PLAN_TABLE_OUTPUT
67M| 5979 | | | | | |
| 1 | PARTITION RANGE ITERATOR| | |
| | KEY | KEY | 28,00 | PCWP | |
| 2 | TABLE ACCESS FULL | ODS_WO_WMIN06_FB | 204K|
67M| 5979 | KEY | KEY | 28,00 | P->S | QC(RAND) |
| 3 | TABLE ACCESS FULL | DUAL | 164 |
| 2 | | | 28,00 | PCWP | |
Thanks a lot in advance,
Lucho.
Received on Thu Feb 01 2007 - 10:08:49 CST