Re: Doubt on optimizer costs when using date and subquery on dual

On Feb 1, 8:35 am, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

DA Morgan thanks for your fast answer.
I know it is absolutely innecessary, but I am simplifying my real problem here trying to find whats happening. In my real case, Instead of SELECT '26-JAN-2007' FROM DUAL I am selecting a date from a table dynamically, like: SELECT MAX(START_DATE) start_date FROM cpos_wmin06_fb_time_perd_mv which is a very small table.

Now my doubt is more elementary, regarding having a very small subquery there and having just a date for comparison, why such difference for the optimizer.

Regards,
Lucho. Received on Thu Feb 01 2007 - 10:46:07 CST