Re: Re: Index usage
sorry, the second query uses equality operator.. WHERE UPPER(col1) = 'xyz'; index hint is not helping. regards, B S Pradhan -- On Thu, 08 Jan 2004 zions swordfish wrote : hi, pradhan, I don't see any kind of differences with your two queries, but I suggest you to use hint in your query to activate index. Regards, Sony - Original Message - DATE: Thu, 08 Jan 2004 07:09:26 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Cc: Hi All, I have the query like: SELECT * FROM tab1 WHERE UPPER(col1) 'abc'; Obviously, the function based index i have is not hetting used because of the ineqality. When using SELECT * FROM tab1 WHERE UPPER(col1) 'abc'; index is getting used. How can I possibly use index in the 1st case. Thanks and Regards, B S Pradhan Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005
Re: Re: Re: Index usage
Thanks. Regards, B S Pradhan - On Fri, 26 Dec 2003 zhu chao wrote : Hi, To see why oracle choose FTS, alter session set events '10053 trace name context forever,level 2'; You can do alter session to change index_adj and optimizer_index_caching to change only your session, or using hint. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, December 25, 2003 8:09 PM Hi All, Agreed.. and it should behave that way i.e if (cost of ind1 scan + then based on c1's selection table access for c3) (direct table access for c1 and c1) then oracle will use FTS with cost based optimization. So, w/o a hint that is expected. But why it is not picking the index in my case i donot know. Also, can optimizer_index_cost_adj help? Its 100 now. Also that affects the whole DB, so is there any way to set it for this particular query ? Thanks for all the inputs. Regards, B S Pradhan On Wed, 24 Dec 2003 Mike Spalinger wrote : The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3. Mike anu wrote: No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index. 2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck. Daniel W. Fink [EMAIL PROTECTED] wrote: You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote: 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: Daniel W. Fink 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). ! Do you Yahoo!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger 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). -- 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,
Re: Re: Index usage
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) 10 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) 10 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) 10 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=5 Card=1 Byt es=20) 21 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).
Re: Re: Index usage
Hi All, Agreed.. and it should behave that way i.e if (cost of ind1 scan + then based on c1's selection table access for c3) (direct table access for c1 and c1) then oracle will use FTS with cost based optimization. So, w/o a hint that is expected. But why it is not picking the index in my case i donot know. Also, can optimizer_index_cost_adj help? Its 100 now. Also that affects the whole DB, so is there any way to set it for this particular query ? Thanks for all the inputs. Regards, B S Pradhan On Wed, 24 Dec 2003 Mike Spalinger wrote : The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3. Mike anu wrote: No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index. 2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck. Daniel W. Fink [EMAIL PROTECTED] wrote: You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote: 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: Daniel W. Fink 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). ! Do you Yahoo!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger 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).
Re: Re: Index usage
I think you should start by posting the exactSQL with exact execution plan to us, there's no point in wild guessing... Tanel. - Original Message - From: bhabani s pradhan To: Multiple recipients of list ORACLE-L Sent: Thursday, December 25, 2003 2:09 PM Subject: Re: Re: Index usage Hi All,Agreed.. and it should behave that way i.eif (cost of ind1 scan + then based on c1's selection table access for c3) (direct table access for c1 and c1) then oracle will use FTS with cost based optimization.So, w/o a hint that is expected.But why it is not picking the index in my case i donot know.Also, can optimizer_index_cost_adj help? Its 100 now. Also that affects the whole DB, so is there any way to set it for this particular query ?Thanks for all the inputs.Regards,B S PradhanOn Wed, 24 Dec 2003 Mike Spalinger wrote :The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3.Mikeanu wrote:No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index.2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck."Daniel W. Fink" [EMAIL PROTECTED] wrote: You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote: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 taWHERE 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: Daniel W. Fink 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).!Do you Yahoo!?Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mike Spalinger INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California-- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Re: Index usage
Hi, To see why oracle choose FTS, alter session set events '10053 trace name context forever,level 2'; You can do alter session to change index_adj and optimizer_index_caching to change only your session, or using hint. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, December 25, 2003 8:09 PM Hi All, Agreed.. and it should behave that way i.e if (cost of ind1 scan + then based on c1's selection table access for c3) (direct table access for c1 and c1) then oracle will use FTS with cost based optimization. So, w/o a hint that is expected. But why it is not picking the index in my case i donot know. Also, can optimizer_index_cost_adj help? Its 100 now. Also that affects the whole DB, so is there any way to set it for this particular query ? Thanks for all the inputs. Regards, B S Pradhan On Wed, 24 Dec 2003 Mike Spalinger wrote : The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3. Mike anu wrote: No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index. 2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck. Daniel W. Fink [EMAIL PROTECTED] wrote: You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote: 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: Daniel W. Fink 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). ! Do you Yahoo!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger 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). -- 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:
