Oracle picks the wrong driving table?

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Wed, 29 Oct 2003 20:29:11 GMT
Message-ID: <3fa01d77.452936998@nyc.news.speakeasy.net>

Can somebody help me understand why oracle picks the wrong driving table here? I've been inserting too many ORDERED and RULE hints over the past few days.

SELECT pr.player_id

            FROM roster_cache_1 rc, player_research pr
            WHERE 
               pr.player_id = rc.player_id
 	      AND rc.user_id = 26

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=75479 Bytes=1132185)

   1 0 NESTED LOOPS (Cost=2 Card=75479 Bytes=1132185)    2 1 INDEX (FULL SCAN) OF 'PLAYER_RESEARCH_PLAYER_ID'
(UNIQUE) (Cost=6 Card=1008 Bytes=3024)

   3    1     INDEX (RANGE SCAN) OF 'ROSTER_CACHE_I_UP_1' (NON-UNIQUE)
       2153  consistent gets

SELECT /*+ ORDERED */ pr.player_id 
            FROM roster_cache_1 rc, player_research pr
            WHERE 
               pr.player_id = rc.player_id
 	      AND rc.user_id = 26

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=75479

Bytes=1132185)

   1 0 NESTED LOOPS (Cost=2 Card=75479 Bytes=1132185)    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ROSTER_CACHE_1'
(Cost=1 Card=7488 Bytes=89856)

   3 2 INDEX (RANGE SCAN) OF 'ROSTER_CACHE_I_1' (NON-UNIQUE)
(Cost=3 Card=7488)

   4 1 INDEX (UNIQUE SCAN) OF 'PLAYER_RESEARCH_PLAYER_ID'
(UNIQUE)

         20 consistent gets

init.ora parameters u might be interested in:

hash_area_size                       integer 65536
hash_join_enabled                    boolean TRUE
hash_multiblock_io_count             integer 0
object_cache_optimal_size            integer 102400
optimizer_features_enable            string  8.1.7
optimizer_index_caching              integer 90
optimizer_index_cost_adj             integer 10
optimizer_max_permutations           integer 80000
optimizer_mode                       string  CHOOSE
optimizer_percent_parallel           integer 0
db_file_multiblock_read_count        integer 32

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Wed Oct 29 2003 - 14:29:11 CST