Cost based optimiser (on 8.1.7.4)

Hi

I'm having problems with the cost based optimiser and hoped someone could get me back on the straight and narrow.

I have the following simple SQL statement:

SELECT  pj.div


,       pj.num

,       pj.ver

FROM    proj pj


,       sect ps

WHERE   ps.drawing LIKE :L_draw
AND     pj.num = ps.num
AND     pj.ver = ps.ver
AND     pj.div = ps.div

The proj table contains 200,000(ish) rows and the sect one contains 1 million(ish), they share the num, div and ver columns (1 num, div, ver combination on proj could equate to 20 records on sect).

Both tables have been analyzed with compute statistics. There is an index on sect.drawing and an index on proj.num, div and ver.

The plan for this statement is:

 ID|Operation            |Options                  |Object
---|---------------------|-------------------------|-------------------------
  1|  MERGE JOIN         |                         |
  2|    INDEX            |FULL SCAN                |PK_PROJ_1
  3|    SORT             |JOIN                     |
  4|      TABLE ACCESS   |BY INDEX ROWID           |SECT
  5|        INDEX        |RANGE SCAN               |SECT_1

It uses the index on sect.drawing successfully but full scans the one on proj even though it can use the div,num and ver columns on sect to be quite specific.

If I make it use the RBO ("/*+ RULE */") I get the plan I want (I've timed them and its many times better):

 ID|Operation            |Options                  |Object
---|---------------------|-------------------------|-------------------------
  1|  NESTED LOOPS       |                         |
  2|    TABLE ACCESS     |BY INDEX ROWID           |SECT
  3|      INDEX          |RANGE SCAN               |SECT_1
  4|    INDEX            |UNIQUE SCAN              |PK_PROJ_1

I've tried using a hint of "/*+ INDEX(PROJ PK_PROJ_1) */" but this makes no difference.

If I switch the "LIKE" to "=" then I get the same plan as the rule based optimiser. I'm guessing that the CBO is pessimistic and works out a plan assuming :L_draw is going to be very vague ("%") where the RBO has a much brighter outlook and trusts me to use reasonable values for :L_draw.

Can anyone enlighten me as to what is going on?

TIA Neil Received on Wed Aug 28 2002 - 09:21:14 CDT