Re: optimizer problem in 8.1.5

I assume that you have an index on "UO.ASC_URL_OBJECT_NAME". Keep in mind that the index is essentially a sorted list and quick searches depends on the sort. If you use the '%' wildcard in the front of criteria on this indexed field, the optimizer has no idea where to find it in the sorted order. Therefore, it scans.

<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:00:00 CDT