Re: optimizer problem in 8.1.5

Hi.

 In order to verify if optimizer made correct decision we need to  know what indexes exist.
 Post results of:
  SELECT table_name, index_name, column_name from user_ind_columns   WHERE table_name in ('AST_URL_SUMMARY','AST_URL_OBJECT')   ORDER BY 1,2,column_position;
 I strongly suspect index 'ASI_URL_SUMMARY_SIDX'.  Check if better index exists ( or may be define one ).

 HTH. Michael.

In article <8cj4m1$t5s$1_at_nnrp1.deja.com>,   wbgunter_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Apr 08 2000 - 00:00:00 CDT