Re: optimizer problem in 8.1.5

I wouldn't start shouting 'bug' immediately. Building an optimizer always returning the access path _you_ think is right is extreemly difficult. Because the left % the optimizer decided it can't use an index on the second table, which results in 2 full table scans, which are processed faster. This in turn may have be favored by several init.ora parameters.
As we can't see the primary and foreign keys here, we can't tell you whether the optimizer made a correct decision.
Apart from that the query doesn't return the same numbers of results (refer to cardinality)
and is possibly wrong too, assuming you meant to start the between at '00'. Now you are implictly using the hour part of the sysdate.

Hth,

Sybrand Bakker, Oracle DBA

<wbgunter_at_my-deja.com> wrote in message news:8cj4m1$t5s$1_at_nnrp1.deja.com...
> Here's some sql and the corresponding execution plan:
>
> SELECT to_number(to_char(us.asc_date_hour, 'dd'),'99') as day,
> nvl(sum(us.asc_page_cnt),0) as pages
> FROM ast_url_summary us, ast_URL_OBJECT uo
> WHERE us.asc_date_hour
> between to_date('2000/03/30',
> 'yyyy/mm/dd') and
> to_date('2000/03/30 23',
> 'yyyy/mm/dd hh24')
> and UO.ASC_URL_OBJECT_NAME like
> '/tools%'
> and US.ASC_URL_TO_KEY =
> UO.ASC_URL_TO_KEY
> and US.ASC_DOC_ROOT_ID =
> UO.ASC_DOC_ROOT_ID
> GROUP by to_number(to_char(us.asc_date_hour,
> 'dd'),'99')
> ORDER by day ;
>
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9512 Card=2 Bytes=350)
> 1 0 SORT (GROUP BY) (Cost=9512 Card=2 Bytes=350)
> 2 1 NESTED LOOPS (Cost=9507 Card=2 Bytes=350)
> 3 2 TABLE ACCESS (FULL) OF 'AST_URL_OBJECT' (Cost=899
> Card=16 Bytes=1664)
> 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'AST_URL_SUMMARY'
> (Cost=538 Card=128241 Bytes=9105111)
> 5 4 INDEX (RANGE SCAN) OF 'ASI_URL_SUMMARY_SIDX'
> (NON-UNIQUE) (Cost=537 Card=128241)
>
> SELECT to_number(to_char(us.asc_date_hour, 'dd'),'99') as day,
> nvl(sum(us.asc_page_cnt),0) as pages
> FROM ast_url_summary us, ast_URL_OBJECT uo
> WHERE us.asc_date_hour
> between to_date('2000/03/30',
> 'yyyy/mm/dd') and
> to_date('2000/03/30 23',
> 'yyyy/mm/dd hh24')
> and UO.ASC_URL_OBJECT_NAME like
> '%/tools%'
> and US.ASC_URL_TO_KEY =
> UO.ASC_URL_TO_KEY
> and US.ASC_DOC_ROOT_ID =
> UO.ASC_DOC_ROOT_ID
> GROUP by to_number(to_char(us.asc_date_hour,
> 'dd'),'99')
> ORDER by day ;
>
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12341 Card=25
> Bytes=4375)
> 1 0 SORT (GROUP BY) (Cost=12341 Card=25 Bytes=4375)
> 2 1 HASH JOIN (Cost=12253 Card=6412 Bytes=1122100)
> 3 2 TABLE ACCESS (FULL) OF 'AST_URL_OBJECT' (Cost=899
> Card=64204 Bytes=6677216)
> 4 2 TABLE ACCESS (FULL) OF 'AST_URL_SUMMARY' (Cost=11153
> Card=128241 Bytes=9105111)
>
> The only difference between the two queries is the addition of the
> leading '%' in the like clause of the second query. This produces a
> tremendous speed difference - the second query returns in about a minute
> and the first hasn't returned in a hour of processing.
>
> Is this kind of thing a known bug with the optimizer in 8.1.5?
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Apr 07 2000 - 00:23:11 CDT