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

From: Lucho <luciofer_at_gmail.com>
Date: 1 Feb 2007 09:29:36 -0800
Message-ID: <1170350976.204895.147520@j27g2000cwj.googlegroups.com>

On Feb 1, 8:54 am, DA Morgan <damor..._at_psoug.org> wrote:
> Lucho wrote:
> > 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.

> I'd suggest posting real code if you want real answers. ;-)
> --
> 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 -

You are right Morgan, here is the real code:

QUERY 1:

SELECT /*+ PARALLEL(iw, 4) */
*
FROM ods_wo_wmin06_fb iw
WHERE iw.srce_sys_id = 1121
AND iw.cpos_time_perd_date = (SELECT MAX(START_DATE) start_date

                                         FROM

cpos_wmin06_fb_time_perd_mv);
PLAN_TABLE_OUTPUT

| Id  | Operation                |  Name                        |

|   0 | SELECT STATEMENT         |                              |
204K|    67M|  5979 |       |       |        |      |            |
|   1 |  PARTITION RANGE ITERATOR|
|       |       |       |   KEY |   KEY | 31,00  | PCWP |            |
|   2 |   TABLE ACCESS FULL      | ODS_WO_WMIN06_FB             |
204K|    67M|  5979 |   KEY |   KEY | 31,00  | P->S | QC (RAND)  |
|   3 |    SORT AGGREGATE        |                              |
1 |     8 |       |       |       | 31,00  | PCWP |            |
|   4 |     TABLE ACCESS FULL    | CPOS_WMIN06_FB_TIME_PERD_MV  |
119 |   952 |     2 |       |       | 31,00  | PCWP |            |

Now compare with this one, where I am providing the date:

QUERY 2

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';

|   0 | SELECT STATEMENT     |                   |   258K|    94M|
71 |        |       |        |      |            |

| 1 | TABLE ACCESS FULL | ODS_WO_WMIN06_FB | 258K| 94M| 71 | 118 | 118 | 32,00 | P->S | QC (RAND) | Cost in query 1 is 5979, in query 2 is 71. Cost to get the MAX date in first query is only 2. Do you have a clue why do I have such a difference?

Regards Received on Thu Feb 01 2007 - 11:29:36 CST