Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Doubt on optimizer costs when using date and subquery on dual

Doubt on optimizer costs when using date and subquery on dual

From: Lucho <luciofer_at_gmail.com>
Date: 1 Feb 2007 08:08:49 -0800
Message-ID: <1170346129.306395.275070@v45g2000cwv.googlegroups.com>


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:



SELECT /*+ PARALLEL(iw, 4) */ *
FROM ods_wo_wmin06_fb iw
WHERE iw.srce_sys_id = 1121
AND iw.cpos_time_perd_date = '26-JAN-2007';

PLAN_TABLE_OUTPUT



| Id | Operation | Name | Rows | Bytes |
Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

| 0 | SELECT STATEMENT | | 258K| 94M|
71 | | | | | |
| 1 | TABLE ACCESS FULL | ODS_WO_WMIN06_FB | 258K| 94M|
71 | 118 | 118 | 27,00 | P->S | QC (RAND) | QUERY 2:

SELECT /*+ PARALLEL(iw, 4) */ *
FROM ods_wo_wmin06_fb iw
WHERE iw.srce_sys_id = 1121
AND iw.cpos_time_perd_date = to_date((SELECT '26-JAN-2007' FROM DUAL),'DD-MON-YYYY'); PLAN_TABLE_OUTPUT

| Id | Operation | Name | Rows | Bytes |
Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

| 0 | SELECT STATEMENT | | 204K|
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US