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