Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Doubt on optimizer costs when using date and subquery on dual
Lucho wrote:
> 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.
Well for one thing hyou are doing a totally unnecessary SELECT from dual. This will work:
AND iw.cpos_time_perd_date = TO_DATE('26-JAN-2007');
But I'd also recommend patching your instance to the current release and examining your indexing strategy depending on cardinality and clustering factor.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Feb 01 2007 - 10:35:43 CST
![]() |
![]() |