optimizer problem in 8.1.5

From: <wbgunter_at_my-deja.com>
Date: Thu, 06 Apr 2000 22:56:08 GMT
Message-ID: <8cj4m1$t5s$1@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 Thu Apr 06 2000 - 17:56:08 CDT