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).
