Re: CBO vs RBO

Hi,
 first - this depends on your optimiser goal:  response time (FIRST_ROWS) or throughput (ALL_ROWS)

 second - for small tables a full scan can be more efficcient than index scan and table access by rowid.

Karsten

In article <8amed2$dms$1_at_nnrp1.deja.com>, 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 16 2000 - 00:00:00 CST