Hi, Nice example and it should behave similarly in my case also. I have to research more..
Regards, B S Pradhan ------------------- On Wed, 24 Dec 2003 zhu chao wrote : >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).
