Why CBO choose wrong way?

From: Zhai Jingmin <zhai_jingmin_at_yahoo.com>
Date: Fri, 27 May 2005 19:45:01 -0700 (PDT)
Message-ID: <20050528024501.45177.qmail@web53207.mail.yahoo.com>

Hi,list

   My query is  

    select a.* from peak_present a,present b     where a.present_id=b.id
    and a.peak_id=:1
    order by a.id;  

The table a has 39M rows ,and table b is very small,I have a index on a.peak_id,all the statistics are current.The problem is the CBO choose following execution plan and the index on peak_id is not used .If I delete the 'order by' line,theCBO can use the index on peak_id column.Actually the peak_id column is almost unique.The query is very fast if it can use the index on peak_id column.Why the CBO order the table so early before filter out right rows? My optimizer* parameters list:  

optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.1.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     30
optimizer_mode                       string      FIRST_ROWS

db_file_multi_block_read_count 16  

PLAN_TABLE_OUTPUT

---

|   0 | SELECT STATEMENT             |                 |     1 |    39 |   147K

(1)| 00:26:59 |
 

|   1 |  NESTED LOOPS                |                 |     1 |    39 |   147K

(1)| 00:26:59 |
 

|* 2 | TABLE ACCESS BY INDEX ROWID| PEAK_PRESENT | 1 | 35 | 147K
(1)| 00:26:59 |
 

| 3 | INDEX FULL SCAN | PK_PEAK_PRESENT | 39M| | 107K
(2)| 00:19:39 |
 

PLAN_TABLE_OUTPUT

---

 

|* 4 | INDEX UNIQUE SCAN | PK_PRESENT | 1 | 4 | 0
(0)| 00:00:01 |
                    

---

Yahoo! Mail
 Stay connected, organized, and protected. Take the tour

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 27 2005 - 22:50:03 CDT