Re: Index usage

Hi,

    It cound be possible that without Hint, oracle will choose FTS for second SQL, because with col3 clause, if using index, oracle will have to do a range scan on index ind1 and than table access by rowid.     If CBO thinks that col1='val1' will get a lot of rows then doing FTS may be cheaper.But with hint, oracle should be able to pick that index.     

Sample:
00:48:18 system_at_CAT9> create table test as select * from dba_tables;

Table created.
00:48:45 system_at_CAT9> create index ind1 on test(owner,table_name) compute statistics;

Index created.
00:49:39 system_at_CAT9> select 'x' from test where owner='PUBLIC';

no rows selected

Elapsed: 00:00:00.03

Execution Plan

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=5 Bytes=15)    1 0 INDEX (FAST FULL SCAN) OF 'IND1' (NON-UNIQUE) (Cost=2 Card

          =5 Bytes=15)
00:50:00 system_at_CAT9> select 'x' from test where owner='PUBLIC' and tablespace_name='SYSTEM';

no rows selected

Elapsed: 00:00:00.00

Execution Plan

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=20)    1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=4 Card=1 Bytes=20)

00:51:14 system_at_CAT9> select /*+index(test ind1)*/ 'x' from test where owner='PUBLIC' and tablespace_name='SYSTEM';

no rows selected

Elapsed: 00:00:00.01

Execution Plan

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=20)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=5 Card=1 Byt       es=20)
   2    1     INDEX (FULL SCAN) OF 'IND1' (NON-UNIQUE) (Cost=3 Card=5)

• Original Message ----- To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 24, 2003 9:59 PM

Hi All,

Merry Christmas to all.

I have this interesting problem..

For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x'
FROM tab ta
WHERE ta.c1='val1';
(gives index ind1 range scan)

But for

SELECT 'x'
FROM tab ta
WHERE ta.c1='val1'
AND ta.c3 = 'val2';
(gives FTS)

index ind1 is not being used. c3 is a nonindexed column.

I have already tried index(ta ind1) , RULE hints.

The table and the index are analyzed.

What cud be the reason for that?

Regards,
B S Pradhan  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: zhu chao
  INET: chao_ping_at_vip.163.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).