| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: Index usage
Content-type: text/html;
charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
<P>=0A<BR>=0AHi,<BR>=0A<BR>=0ANice example and it should behave similarly i=
n my case also. I have to research more..<BR>=0A<BR>=0A<BR>=0ARegards,<BR>=
=0AB S Pradhan<BR>=0A<BR>=0A-------------------<BR>=0AOn Wed, 24 Dec 2003 z=
hu chao wrote :<BR>=0A>Hi,<BR>=0A> It cound be possible= that without Hint, oracle will choose FTS for second SQL, because with col= 3 clause, if using index, oracle will have to do a range scan on index ind1= and than table access by rowid.<BR>=0A> If CBO thinks tha= t col1=3D'val1' will get a lot of rows then doing FTS may be cheaper.But wi= th hint, oracle should be able to pick that index.<BR>=0A><BR>=0A><BR= >=0A>Sample:<BR>=0A>00:48:18 system_at_CAT9> create table test = as select * from dba_tables;<BR>=0A><BR>=0A>Table created.<BR>=0A>= 00:48:45 system_at_CAT9> create index ind1 on test(owner,table_name) comput= e statistics;<BR>=0A><BR>=0A>Index created.<BR>=0A>00:49:39 system= @CAT9> select 'x' from test where owner=3D'PUBLIC';<BR>=0A><BR>=0A>=;no rows selected<BR>=0A><BR>=0A>Elapsed: 00:00:00.03<BR>=0A><BR>=
---------------<BR>=0A> 0 SELECT STATEM= ENT Optimizer=3DALL_ROWS (Cost=3D2 Card=3D5 Bytes=3D15)<BR>=0A> &n= bsp; 1 0 INDEX (FAST FULL SCAN) OF 'IND1' (NON-UNIQUE) = (Cost=3D2 Card<BR>=0A> =3D5 Bytes=3D1= 5)<BR>=0A>00:50:00 system_at_CAT9> select 'x' from test where owner=3D'P= UBLIC' and tablespace_name=3D'SYSTEM';<BR>=0A><BR>=0A>no rows selecte= d<BR>=0A><BR>=0A>Elapsed: 00:00:00.00<BR>=0A><BR>=0A>Execution = Plan<BR>=0A>----------------------------------------------------------<B= R>=0A> 0 SELECT STATEMENT Optimizer=3DA= LL_ROWS (Cost=3D4 Card=3D1 Bytes=3D20)<BR>=0A> 1 &nbs= p; 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=3D4 Card=3D1 Bytes=3D20)<BR= >=0A><BR>=0A>00:51:14 system_at_CAT9> select /*+index(test ind1)*/ 'x= ' from test where owner=3D'PUBLIC' and tablespace_name=3D'SYSTEM';<BR>=0A&g= t;<BR>=0A>no rows selected<BR>=0A><BR>=0A>Elapsed: 00:00:00.01<BR>=
=0A><BR>=0A>Execution Plan<BR>=0A>--------------------------------=
--------------------------<BR>=0A> 0 SE= LECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D5 Card=3D1 Bytes=3D20)<BR>=0A&g= t; 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T= EST' (Cost=3D5 Card=3D1 Byt es=3D20)<BR>=0A> = 2 1 INDEX (FULL SCAN) OF 'IND1' (NON-UNIQ= UE) (Cost=3D3 Card=3D5)<BR>=0A><BR>=0A>----- Original Message -----<B= R>=0A>To: Multiple recipients of list ORACLE-L<BR>=0A>Sent: Wednesday= , December 24, 2003 9:59 PM<BR>=0A><BR>=0A><BR>=0A><BR>=0A>Hi A= ll,<BR>=0A><BR>=0A>Merry Christmas to all.<BR>=0A><BR>=0A>I hav= e this interesting problem..<BR>=0A><BR>=0A>For this query index ind1= on (c1,c2) columns is getting used.<BR>=0A>SELECT 'x'<BR>=0A> FROM t= ab ta<BR>=0A>WHERE ta.c1=3D'val1';<BR>=0A>(gives index ind1 range sca= n)<BR>=0A><BR>=0A>But for<BR>=0A><BR>=0A>SELECT 'x'<BR>=0A> = FROM tab ta<BR>=0A>WHERE ta.c1=3D'val1'<BR>=0A>AND ta.c3 =3D 'val2';<= BR>=0A>(gives FTS)<BR>=0A>index ind1 is not being used. c3 is a nonin= dexed column.<BR>=0A><BR>=0A>I have already tried index(ta ind1) , RU= LE hints.<BR>=0A><BR>=0A>The table and the index are analyzed.<BR>=0A= ><BR>=0A><BR>=0A><BR>=0A>What cud be the reason for that?<BR>=
tcity.com<BR>=0A>San Diego, California -- Mai= ling list and web hosting services<BR>=0A>------------------------------= ---------------------------------------<BR>=0A>To REMOVE yourself from t= his mailing list, send an E-Mail message<BR>=0A>to: ListGuru_at_fatcity.com= (note EXACT spelling of 'ListGuru') and in<BR>=0A>the message BODY, inc= lude a line containing: UNSUB ORACLE-L<BR>=0A>(or the name of mailing li= st you want to be removed from). You may<BR>=0A>also send the HELP= command for other information (like subscribing).<BR>=0A=0A</P>=0A<br><br>=
=0AHi,=0A=0ANice example and it should behave similarly in my case also. I =
have to research more..=0A=0A=0ARegards,=0AB S Pradhan=0A=0A---------------= ----=0AOn Wed, 24 Dec 2003 zhu chao wrote :=0A>Hi,=0A> It cound be poss= ible 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.=0A> If CBO thinks that col1=3D'val= 1' will get a lot of rows then doing FTS may be cheaper.But with hint, orac= le should be able to pick that index.=0A>=0A>=0A>Sample:=0A>00:48:18 system= @CAT9> create table test as select * from dba_tables;=0A>=0A>Table created= .=0A>00:48:45 system_at_CAT9> create index ind1 on test(owner,table_name) comp= ute statistics;=0A>=0A>Index created.=0A>00:49:39 system_at_CAT9> select 'x' f= rom test where owner=3D'PUBLIC';=0A>=0A>no rows selected=0A>=0A>Elapsed: 00= :00:00.03=0A>=0A>Execution Plan=0A>----------------------------------------= ------------------=0A> 0 SELECT STATEMENT Optimizer=3DALL_ROWS (Cos= t=3D2 Card=3D5 Bytes=3D15)=0A> 1 0 INDEX (FAST FULL SCAN) OF 'IND1'= (NON-UNIQUE) (Cost=3D2 Card=0A> =3D5 Bytes=3D15)=0A>00:50:00 sys=tem_at_CAT9> select 'x' from test where owner=3D'PUBLIC' and tablespace_name=
cution Plan=0A>----------------------------------------------------------=sed. c3 is a nonindexed column.=0A>=0A>I have already tried index(ta ind1) = , RULE hints.=0A>=0A>The table and the index are analyzed.=0A>=0A>=0A>=0A>W= hat cud be the reason for that?=0A>=0A>=0A>=0A>Regards,=0A>B S Pradhan=0A>=
=0A> 0 SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D4 Card=3D1 Byt=
es=3D20)=0A> 1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=3D4 Card=3D1 B= ytes=3D20)=0A>=0A>00:51:14 system_at_CAT9> select /*+index(test ind1)*/ 'x' fr= om test where owner=3D'PUBLIC' and tablespace_name=3D'SYSTEM';=0A>=0A>no ro= ws selected=0A>=0A>Elapsed: 00:00:00.01=0A>=0A>Execution Plan=0A>----------= ------------------------------------------------=0A> 0 SELECT STATE= MENT Optimizer=3DALL_ROWS (Cost=3D5 Card=3D1 Bytes=3D20)=0A> 1 0 TA= BLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3D5 Card=3D1 Byt es=3D20)=
=0A> 2 1 INDEX (FULL SCAN) OF 'IND1' (NON-UNIQUE) (Cost=3D3 Card=
=3D5)=0A>=0A>----- Original Message -----=0A>To: Multiple recipients of lis=
t ORACLE-L=0A>Sent: Wednesday, December 24, 2003 9:59 PM=0A>=0A>=0A>=0A>Hi = All,=0A>=0A>Merry Christmas to all.=0A>=0A>I have this interesting problem.= .=0A>=0A>For this query index ind1 on (c1,c2) columns is getting used.=0A>S= ELECT 'x'=0A> FROM tab ta=0A>WHERE ta.c1=3D'val1';=0A>(gives index ind1 ran= ge scan)=0A>=0A>But for=0A>=0A>SELECT 'x'=0A> FROM tab ta=0A>WHERE ta.c1=3D= 'val1'=0A>AND ta.c3 =3D 'val2';=0A>(gives FTS)=0A>index ind1 is not being u=
aq.net=0A>--=0A>Author: zhu chao=0A> INET: chao_ping_at_vip.163.com=0A>=0A>F= at City Network Services -- 858-538-5051 http://www.fatcity.com=0A>San D= iego, California -- Mailing list and web hosting services=0A>-------= --------------------------------------------------------------=0A>To REMOVE= yourself from this mailing list, send an E-Mail message=0A>to: ListGuru_at_fa= tcity.com (note EXACT spelling of 'ListGuru') and in=0A>the message BODY, i= nclude a line containing: UNSUB ORACLE-L=0A>(or the name of mailing list yo= u want to be removed from). You may=0A>also send the HELP command for othe=r information (like subscribing).=0A
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: bhabani s pradhan INET: bhabaniindia_at_rediffmail.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).Received on Thu Dec 25 2003 - 05:59:25 CST
 |
 |