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.org