Re: optimizer problem in 8.1.5

Hi.

 I don't think it's a bug.
 In my opinion thats the way optimizer ( you are using COST based )  is supposed to work:
  For the first query it decides that only 16 rows will be retrieved   from 'AST_URL_OBJECT' , so it uses NESTED LOOP join to get data from   the 'AST_URL_SUMMARY'.
  For the second quere leading '%' forces optimizer to consider ALL   rows of 'AST_URL_OBJECT', so it uses HASH join.  The reason for bad performance of first query is VERY BAD selectivity  of 'ASI_URL_SUMMARY_SIDX' index on 'AST_URL_SUMMARY' table ( for each  selected row from 'AST_URL_OBJECT' table Oracle scans 128241 rows  (Cost=537 Card=128241) of 'AST_URL_SUMMARY' table.  Check why index selectivity is that bad ( What fields are included in  that index? How many distinct values exist for each one? ).  That index is supposed to contain at least 2 fields:

    ASC_URL_TO_KEY and ASC_DOC_ROOT_ID.  Consider adding ASC_DATE_HOUR as third field in that index.  How many rows exist in table 'AST_URL_OBJECT' ?  Try defining an index on ASC_URL_OBJECT_NAME field.  Don't forget to re-ANALYZE tables/indexes after changing them.

 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 Mon Apr 24 2000 - 00:00:00 CDT