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 [EMAIL PROTECTED]> create table test as select * from dba_tables; Table created. 00:48:45 [EMAIL PROTECTED]> create index ind1 on test(owner,table_name) compute statistics; Index created. 00:49:39 [EMAIL PROTECTED]> 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 [EMAIL PROTECTED]> 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 [EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).