Re: CBO vs RBO

pollachi_at_my-deja.com wrote:
>
> Can some one explain what is going on here?
>
> select count(1) from ordersummary s, orderdetail d
> where s.principalid = :b0
> and s.order_date between :b1 and :b2
> and d.orderid = s.orderid
>
> Cost based : (Elapsed: 00:00:02.40 )
> Query Plan
> -------------------------------------------------------
> SELECT STATEMENT Cost = 1226
> SORT AGGREGATE
> NESTED LOOPS
> TABLE ACCESS FULL ORDERSUMMARY
> INDEX RANGE SCAN ORDERDETAIL_ORDERID_IDX
>
> Rule based : (Elapsed: 00:00:00.29)
> Query Plan
> -------------------------------------------------------
> SELECT STATEMENT Cost =
> SORT AGGREGATE
> NESTED LOOPS
> TABLE ACCESS BY INDEX ROWID ORDERSUMMARY
> INDEX RANGE SCAN ORDERSUMMARY_PRINCIPALID_IDX
> INDEX RANGE SCAN ORDERDETAIL_ORDERID_IDX
>
> Obviously RBO is performing well.
> Why isn't CBO using the indexes on ordersummary table?
>
> I rebuilt these indexes and then did a compute statistics on them.
> Still, I get the same result with CBO.
> I checked out oracle's metalink. They recommend using CBO there is also
> a mention that if the tables are analyzed with estimate statistics
> the indexes should be analyzed with compute statistics.
> Here both the tables and the indexes were analyzed with compute
> statistics.
>
> I am using Oracle version 8.0.5 on AIX
>
> Can some one point out if I am doing something wrong here?
> Any help will be much appreciated.
>
> TIA.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

CBO is basically deciding that using the index is not as efficient as a full scan. Try the FIRST_ROWS hint and see if that improve things. Alternatively you could explicitly code the INDEX hint.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse