Help me to understand the Query Explain!!

[gsprince]

Hello:

I have the following two scenarios. I want to understand why the index is not being used in the first scenario.

Note: there is primary key index on (CNY#, RECORD#) on both the tables.

PK_CH (CNY#, RECORD#)
PK_CV (CNY#, RECORD#)

Tot rows in CH: 22,470 (ie, less than 2% per CNY#)
Tot rows in CV: 22,477 (ie, less than 2% per CNY#)


Thank you,
Prince..


Scen I:
--------
select
ch.CNY#,
ch.RECORD#,
STATUS,
COMPANYNAME,
FIRSTNAME,
LASTNAME,
MI,
ch.NAME
from cv, ch
where
ch.vrec# = cv.record# and
ch.cny# = cv.cny#
and cv.cny# = 1718 ;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=81 Card=280 Bytes=38
920)

1 0 HASH JOIN (Cost=81 Card=280 Bytes=38920)
2 1 TABLE ACCESS (FULL) OF 'CH' (Cost=22 Card=278 B
ytes=11398)

3 1 TABLE ACCESS (FULL) OF 'CV' (Cost=54 Card=27
5 Bytes=26950)



Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
345 consistent gets
0 physical reads
0 redo size
1272 bytes sent via SQL*Net to client
313 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed



Scen II:
---------
select
ch.CNY#,
ch.RECORD#,
STATUS,
COMPANYNAME,
FIRSTNAME,
LASTNAME,
MI,
ch.NAME
from cv, ch
where
ch.vrec# = cv.record# and
ch.cny# = cv.cny#
and cv.cny# = 1718 ;


no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CH'
3 2 INDEX (RANGE SCAN) OF 'PK_CH' (UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'CV'
5 4 INDEX (UNIQUE SCAN) OF 'PK_CV' (UNIQUE)



Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1272 bytes sent via SQL*Net to client
313 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

[padmam]

Hi, 23rd June 2001 16:47 hrs chennai

There are many situations in which the optimizer cannot use an Index although it would have been better or efficient.

The other way would be to rewrite the SQL statement to force using the index.

I noted that first time from the query it is known that it uses CBO and whereas second time due to the RULE hint you have given it has chosen RBO.

The other scenarios

If you have added primary key after the first Query executed the chances are there to display the output as shown by you or else read this link carefully for the problem of oracle on sometimes with CBO

http://www.fors.com/orasupp/rdbms/misc/35934_1.HTM

For more info

http://www.dbasupport.com/forums/sho...threadid=10108


Please feel to raise your further doubts in this thread.

Cheers

Padmam

[padmam]

Hi, 23rd June 2001 18:03 hrs chennai

read this details also it will help you to understand more on Optimizersrelated to our problem.

http://technet.oracle.com/support/bb...ontent/736.htm

cheers

padmam