CBO vs RBO

From: <pollachi_at_my-deja.com>
Date: 2000/03/14
Message-ID: <8amed2$dms$1@nnrp1.deja.com>#1/1

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. Received on Tue Mar 14 2000 - 00:00:00 CST