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 -> Re: Doubt on optimizer costs when using date and subquery on dual

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

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 01 Feb 2007 08:54:36 -0800
Message-ID: <1170348871.314428@bubbleator.drizzle.com>


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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Feb 01 2007 - 10:54:36 CST

Original text of this message

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