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: Index usage
hi, pradhan,I don't see any kind of differences with your two queries, butI suggest you to use hint in your query to activate index.Regards,Sony - Original Message - DATE: Thu, 08 Jan 2004 07:09:26 From: "bhabani s pradhan" [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Cc: Hi All,I have the query like:SELECT *FROM tab1WHERE UPPER(col1) 'abc';Obviously, the function based index i have is not hetting used because of the ineqality.When using SELECT *FROM tab1WHERE 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!Login To Lycos Mail
RE: Index usage
Am I missing something? It looks to me like these queries are the same... -Original Message- Sent: Thursday, January 08, 2004 9:09 AM To: Multiple recipients of list ORACLE-L 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi 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: 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:
Re: Index usage
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).
Re: Index usage
No. The index should get used.Thequery 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 tablescan. What is the cardinality like?It is strange thatRULE 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 thepredicate is to perform a full table scan. Since this predicate condition forcesa full table scan on ta, which will retrieve the ta.c1 column values at the sametime, there is no need to use an index. In fact, an additional index accesswould decrease the query performance.Daniel Finkbhabani 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. FinkINET: [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).! Do you Yahoo!? Free Pop-Up Blocker - Get it now
Re: Index usage
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: Index usage
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: Index Usage ?!
Once again thanx a lot Tanel for spending your precious time to make me understand. Regards, Jp. 28-07-2003 22:59:25, Tanel Poder [EMAIL PROTECTED] wrote: The point is, that index access is cheap in reality, but CBO thinks it's very expensive and chooses next best executin plan in it's opinion, but this opinion is actually wrong, because unset optimizer_ parameters for example. One more thing, your test query returned no rows, it could be that with index access Oracle didn't even have to visit any table blocks because no relevant keys found in index. But when you'll have keys matching your query conditions in future, index access might get slower as well (here's where clustering_factor comes into play). Without analyzing, the index cost in reality is still low, and with super-optimistic default statistics CBO luckily picks that path. No, according plan in your orignal post, index range scan was used. Btw, I submitted a simple research about CBO table/index access costs to comp.databases.oracle.server yesterday under subject 2 Oracel doubts, you might want to read that one as well to get more understanding on your issue. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Index Usage ?!
My only doubt is: 1.After analyzing the index,CBO doesn't read the index because it is costlier in terms of I/O and other resources. The point is, that index access is cheap in reality, but CBO thinks it's very expensive and chooses next best executin plan in it's opinion, but this opinion is actually wrong, because unset optimizer_ parameters for example. One more thing, your test query returned no rows, it could be that with index access Oracle didn't even have to visit any table blocks because no relevant keys found in index. But when you'll have keys matching your query conditions in future, index access might get slower as well (here's where clustering_factor comes into play). 2.But without analyzing,CBO uses the index now,which is supposed to be coslty,and hence incurs the same I/O and other resources . Without analyzing, the index cost in reality is still low, and with super-optimistic default statistics CBO luckily picks that path. 3.when the role of CBO is over,the h/w resources comes into play to read the index.even now it reads the full index and the response is faster.how is it so ? No, according plan in your orignal post, index range scan was used. Btw, I submitted a simple research about CBO table/index access costs to comp.databases.oracle.server yesterday under subject 2 Oracel doubts, you might want to read that one as well to get more understanding on your issue. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: Index Usage ?!
Hi Tanel, thanks a lot for your wonderful explanation. sorry to pester u you again with my doubts on the first day of the week. My only doubt is: 1.After analyzing the index,CBO doesn't read the index because it is costlier in terms of I/O and other resources. 2.But without analyzing,CBO uses the index now,which is supposed to be coslty,and hence incurs the same I/O and other resources . 3.when the role of CBO is over,the h/w resources comes into play to read the index.even now it reads the full index and the response is faster.how is it so ? hope i am clear in telling u what i had understood ?! plz. excuse me if i'm sound silly and stupid. of the above 3 points,where am i wrong ? Regards, Jp. 25-7-2003 21:24:45, Tanel Poder [EMAIL PROTECTED] wrote: Hi! Your original post shows that when index was not analyzed, optimizer used default statistics and found out that index scan is quite cheap. Also, no sorting had to be done, since descending index range scan could be used to satisfy your order by clause. Whe index was analyzed, then CBO actually saw, that index isn't that good as defaul values showed, the number of leaf blocks was higher and also the clustering factor was probably much-mugh higher, meaning that for any non-unique index key value it has to visit several different data blocks to get all matching rows. That means lot's of IOs. Eventually CBO decided that it's cheaper to do let say 1600 multiblock (plus one segment header + possibly bitmap) reads directly and scan through the whole table than to traverse through index branches, scan leaf blocks and visit every data block individually. So, with not-analyzed index, CBO had nothing else to do, than to be super-optimistic about the index (#LB, CLUF) thus using index in your case. But when analyzed, CBO had accurate data, but did make bad decision because too pessimistic values for optimizer_index_cost_adj and optimizer_index_caching. You should set them at session level for testing to let say 50 and 90, but read the document below for understanding those parameters. http://www.evdbt.com/SearchIntelligenceCBO.doc Cheers, Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Index Usage ?!
Hi! but there are seperate indexes on column PREF, FLAG and SEX. when those indexes are used ,cost=999. whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC, FLAG,SEX,PREF,ENTDAY) is used , cost=7. 1.whys there is a huge difference in COST ? As Wolfgang said, since your IDX_PROFILE_SHINKI index is unanalyzed, CBO is using default values, which look quite sexy costwise. Are your other indexes analyzed? 2.does it mean that a composite index is better than individual ones ? Always depends. But I use them a lot. Optimizerwise - they might be bigger in sense of bytes blocks, but again if they contain all columns required in qurey, that no table access is required, they can speed up lookups short range scans significantly. Tanel. Kindly throw some light on this. Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Tanel Poder 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: Index Usage ?!
Tanel, quoteAre your other indexes analyzed?/quote Yes. Case 1: If IDX_PROFILE_SHINKI is analyzed,CBO decides that index scan is costlier. Case 2: Now IDX_PROFILE_SHINKI is not analyzed (which works fine as of now), CBO takes default values and decides that index scan is cheaper. As per case 1,the I/O to read the index will be costlier. So CBO decides not to use the index. But as per case 2 (existing situation): After the decision is made by CBO, it still needs to read the index and then fetch table data. So the same I/O to read the index is going to happen as in case 1. * correct me if i'm wrong * Aren't they contradictory ?! So how does case 2 benefit in terms of CPU or memory consumption ? and there is a big diff. in response time between both cases...how ? ...totally confused :( Regards, Jp. 25-07-2003 18:34:23, Tanel Poder [EMAIL PROTECTED] wrote: Hi! but there are seperate indexes on column PREF, FLAG and SEX. when those indexes are used ,cost=999. whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC, FLAG,SEX,PREF,ENTDAY) is used , cost=7. 1.whys there is a huge difference in COST ? As Wolfgang said, since your IDX_PROFILE_SHINKI index is unanalyzed, CBO is using default values, which look quite sexy costwise. Are your other indexes analyzed? 2.does it mean that a composite index is better than individual ones ? Always depends. But I use them a lot. Optimizerwise - they might be bigger in sense of bytes blocks, but again if they contain all columns required in qurey, that no table access is required, they can speed up lookups short range scans significantly. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Index Usage ?!
Thanx a lot Irfan. let me also go thro' metalink and try out something. Regards, Jp. 25-07-2003 18:46:51, Irfan Khan [EMAIL PROTECTED] wrote: From what i understand when u analyze , use the keyword of size to set the histogram buckets Generally it should be no of distinct values in a column + som ball park no. say ten. U can play around with this analyzing with histograms. I tried and it worked for me and so it should for you as well. I will do more research and will get back to you. Irfan , Khan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Index Usage ?!
Hi! Your original post shows that when index was not analyzed, optimizer used default statistics and found out that index scan is quite cheap. Also, no sorting had to be done, since descending index range scan could be used to satisfy your order by clause. Whe index was analyzed, then CBO actually saw, that index isn't that good as defaul values showed, the number of leaf blocks was higher and also the clustering factor was probably much-mugh higher, meaning that for any non-unique index key value it has to visit several different data blocks to get all matching rows. That means lot's of IOs. Eventually CBO decided that it's cheaper to do let say 1600 multiblock (plus one segment header + possibly bitmap) reads directly and scan through the whole table than to traverse through index branches, scan leaf blocks and visit every data block individually. So, with not-analyzed index, CBO had nothing else to do, than to be super-optimistic about the index (#LB, CLUF) thus using index in your case. But when analyzed, CBO had accurate data, but did make bad decision because too pessimistic values for optimizer_index_cost_adj and optimizer_index_caching. You should set them at session level for testing to let say 50 and 90, but read the document below for understanding those parameters. http://www.evdbt.com/SearchIntelligenceCBO.doc Cheers, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 25, 2003 2:54 PM Tanel, quoteAre your other indexes analyzed?/quote Yes. Case 1: If IDX_PROFILE_SHINKI is analyzed,CBO decides that index scan is costlier. Case 2: Now IDX_PROFILE_SHINKI is not analyzed (which works fine as of now), CBO takes default values and decides that index scan is cheaper. As per case 1,the I/O to read the index will be costlier. So CBO decides not to use the index. But as per case 2 (existing situation): After the decision is made by CBO, it still needs to read the index and then fetch table data. So the same I/O to read the index is going to happen as in case 1. * correct me if i'm wrong * Aren't they contradictory ?! So how does case 2 benefit in terms of CPU or memory consumption ? and there is a big diff. in response time between both cases...how ? ...totally confused :( Regards, Jp. 25-07-2003 18:34:23, Tanel Poder [EMAIL PROTECTED] wrote: Hi! but there are seperate indexes on column PREF, FLAG and SEX. when those indexes are used ,cost=999. whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC, FLAG,SEX,PREF,ENTDAY) is used , cost=7. 1.whys there is a huge difference in COST ? As Wolfgang said, since your IDX_PROFILE_SHINKI index is unanalyzed, CBO is using default values, which look quite sexy costwise. Are your other indexes analyzed? 2.does it mean that a composite index is better than individual ones ? Always depends. But I use them a lot. Optimizerwise - they might be bigger in sense of bytes blocks, but again if they contain all columns required in qurey, that no table access is required, they can speed up lookups short range scans significantly. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Tanel Poder 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: Index Usage ?!
Hi! 25-07-2003 18:46:51, Irfan Khan [EMAIL PROTECTED] wrote: From what i understand when u analyze , use the keyword of size to set the histogram buckets Generally it should be no of distinct values in a column + som ball park no. say ten. Max nr. of buckets is 254 per column, thus in some conditions you can't have a bucket for each value. But more importantly, histograms aren't used with queries using bind values since during next query the bind variable could be set to completely different value and in order to compute a more appropriate execution plan we would have to go through the most expensive parse phase again. Also, when not using bind variables (which is a bad idea in OLTP because additional parses), using histograms makes each individual parse (which involves analyzing predicates on histogrammed columns) even slower. OTOH, histograms are good for processing the data in large quantities, but bad or even unusable in high concurrency OLTP environments. Of course maybe 10G already has some kind of pre-parsing enchancements where multiple execution plans are calculated for each possible bind variable value range (bucket). We'll see ;) Tanel. U can play around with this analyzing with histograms. I tried and it worked for me and so it should for you as well. I will do more research and will get back to you. Irfan , Khan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Tanel Poder 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: Index Usage ?!
Hi! the env. is 9.2.0.3/win2k-sp3. it is built on "profile" table . the index has the columns "entpc,sex,flag,pref,entday" in it. the order of the columns in the index is also the same as i have mentioned. 1.so ,does it mean that "idx_profile_shinki" is a bad index. 2.if it is bad , why does cbo select this index ? did you analyze your table in addition to index as well? first time you were probably using RBO, which always counts index access better than table access. but after analyzing your segments CBO thinks that full table scan is better because of optimizer_index_cost_adj and optimizer_index_caching parameters. what are your values of those parameters? (also db_file_multiblock_read_count). 3.if it is good , why does elapsed time increase after analyzing this index ? 4.the leading column (entpc) of the index is not there in the beginning of "where" clause. then how is the index used ? it was not said so in a perf tuning book by "richard j. niemiec" doesn't matter, as long as your column is somewhere in the where clause. (note than in 9i indexes can sometimes be used even when first column(s) of index aren't in where clauses - it's called index skip scanning). If you already have analyzed your indexes and tables, have set the parameters, then run alter session set events '10053 trace name context forever, level 1'; then run your statements and send me the trace file from udump. Tanel. It's eating my brains.Kindly explain me Gurus. Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Tanel Poder 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: Index Usage ?!
Hi Tanel, quote did you analyze your table in addition to index as well? first time you were probably using RBO, which always counts index access better than table access. /quote i have analyzed PROFILE table also and hope it's CBO by default in 9i. anyway,it is CBO right from the beginning in my case here. SQLselect num_rows,avg_row_len,chain_cnt from user_Tables where table_name='PROFILE'; NUM_ROWS AVG_ROW_LEN CHAIN_CNT BLOCKS --- - -- 736820 168 42 17407 quotebecause of optimizer_index_cost_adj and optimizer_index_caching parameters./quote optimizer_index_cost_adj = 100 optimizer_index_caching = 0 db_file_multiblock_read_count = 16 quoteit's called index skip scanning/quote Thanx for the info Tanel. I was not knowing this. As u said ,I have attached the Trace file also. Kindly throw some light on this Tanel. Regards, Jp. memb_ora_2400.trc Description: Binary data
Re: Index Usage ?!
The trace seems to be from when the index is not analyzed. The CBO then uses defaults for the index statistics - leaf_blocks=25 and clustering factor=800. These defaults are much lower than when the index is analyzed and the resulting cost for using the index is very low (7 compared to 1676 for a full scan). When you analyze the index, the statistics will be orders of magnitude larger - I estimate that the clustering factor will be 300,000, and therefore the cost of using the index exceeds that of the full scan ( still 1676 ). There are two things you can do Leave the index un-analyzed if it works for you ( I have a few tables where I use that trick) Set optimizer_index_cost_adj to a value lower than 100 - again if it works for you. Test that it does not adversely affect other queries. Many advocate that it should be set lower but I have not had any luck with it. At 04:24 AM 7/24/2003 -0800, you wrote: Hi Tanel, quote did you analyze your table in addition to index as well? first time you were probably using RBO, which always counts index access better than table access. /quote i have analyzed PROFILE table also and hope it's CBO by default in 9i. anyway,it is CBO right from the beginning in my case here. SQLselect num_rows,avg_row_len,chain_cnt from user_Tables where table_name='PROFILE'; NUM_ROWS AVG_ROW_LEN CHAIN_CNT BLOCKS --- - -- 736820 168 42 17407 quotebecause of optimizer_index_cost_adj and optimizer_index_caching parameters./quote optimizer_index_cost_adj = 100 optimizer_index_caching = 0 db_file_multiblock_read_count = 16 quoteit's called index skip scanning/quote Thanx for the info Tanel. I was not knowing this. As u said ,I have attached the Trace file also. Kindly throw some light on this Tanel. Regards, Jp. Content-Disposition: attachment; filename==?iso-2022-jp?Q?memb=5Fora=5F2400.trc?= Content-Type: application/octet-stream Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Index Usage ?!
Thanx Breitling. quoteLeave the index un-analyzed if it works for you/quote as u said,index IDX_PROFILE_SHINKI is not analyzed now. that works fine. but there are seperate indexes on column PREF, FLAG and SEX. when those indexes are used ,cost=999. whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC, FLAG,SEX,PREF,ENTDAY) is used , cost=7. 1.whys there is a huge difference in COST ? 2.does it mean that a composite index is better than individual ones ? Kindly throw some light on this. Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Index usage in 8.1.6
Murali If I'm not mistaken, the first Oracle version that offers real support for this is Oracle9i. I think what you are doing is about all that can be done -- capture SQL, run explain plan on it and make a list of the indexes that are used. If you look around, there are scripts to help and packages that automate this. But in the end you still have to answer the question of how long do you wait to see if an index isn't used before you drop it -- a day, a week, a month? There is always that danger that there will be a really critical report that is only run on irregular occasions that will need that index. For example, a manufacturing plant tends to go through a production season. The applications running at the first of the season are different from those run toward the end. If you pick a month at the first of the season, you may drop indexes needed toward the end. If you have a definite indication that your application is over-indexed, this effort may be worth your trouble. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, April 01, 2003 10:59 AM To: Multiple recipients of list ORACLE-L How do you monitor if the indexes are being used in 8i? Individually the tkprof and explain plans do the trick. However from a running system, how can the index usage be determined? If the index is being used? How many times did the index get used during a specific time frame etc. Regards Menon _ Do you Yahoo!? Yahoo! Tax http://us.rd.yahoo.com/finance/mailsig/*http://tax.yahoo.com Center - File online, calculators, forms, and more -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: Index usage in 8.1.6
Murali , I have attached a script. i use the same to find index usage. hope this helps. Regards, Jp. FindIndexUsage.sql Description: Binary data
RE: Index Usage Monitoring
Hi, Why not to use otrace? Of cource, you may need some space to save trace results, but you'll definitely get complete statistics. Vadim Gorbounov Oracle DBA -Original Message- Sent: Tuesday, January 30, 2001 3:57 PM To: Multiple recipients of list ORACLE-L We have a purchased application with over 1,300 indexes. Can someone suggest a method to monitor the system to determine which indexes are actively being used over time? I'm assuming that some are old/not necessary and would like to save the overhead of maintaining them. Oracle 8.0.6 Patrick Prince email: [EMAIL PROTECTED] Omaha Public Power District voice: (402) 636-3762 444 S 16th St. Mall, Omaha, NE 68102fax: (402) 636-3931 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: PRINCE, PATRICK W. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Vadim Gorbounov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Index Usage Monitoring
Hi, Eveleen, you a right, x$bh is the veiw, which contain important information about database buffer. This means, you will find here references to only those database blocks, which are currently in buffer, i.e., most recently used. You even can identify "hot" (often updated) blocks, because Oracle reconstructs block image in memory for different queries, and this will be different records in x$bh. I use the following query select FILE#, DBABLK, count(*) from x$bh group by FILE#, DBABLK having count(*) 10 order by 3 desc; Top line blocks are subjects to revise segment parameters. But this doesn't not completely the same task, as in question. Vadim Gorbounov Oracle DBA -Original Message- Sent: Monday, February 05, 2001 1:57 PM To: Multiple recipients of list ORACLE-L I do know there is a way to tell which indexes are accessed most recently by query x$bh view. Is this what you want or something different? Sorry I forgot the initial posting of this issue. Eveleen Please respond to [EMAIL PROTECTED] Vadim Gorbounov [EMAIL PROTECTED] on 02/05/2001 09:30:47 AM Message - From: Vadim Gorbounov [EMAIL PROTECTED] on 02/05/2001 03:30 PM GMT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: Eveleen Xu/NNIB/NNNG) Subject: RE: Index Usage Monitoring Hi, Why not to use otrace? Of cource, you may need some space to save trace results, but you'll definitely get complete statistics. Vadim Gorbounov Oracle DBA -Original Message- Sent: Tuesday, January 30, 2001 3:57 PM To: Multiple recipients of list ORACLE-L We have a purchased application with over 1,300 indexes. Can someone suggest a method to monitor the system to determine which indexes are actively being used over time? I'm assuming that some are old/not necessary and would like to save the overhead of maintaining them. Oracle 8.0.6 Patrick Prince email: [EMAIL PROTECTED] Omaha Public Power District voice: (402) 636-3762 444 S 16th St. Mall, Omaha, NE 68102fax: (402) 636-3931 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: PRINCE, PATRICK W. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Vadim Gorbounov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Vadim Gorbounov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mai
Re: Index Usage Monitoring
Pine Cone Systems has software to do this. There is also another company that has software to do this, but I can't recall their name at the moment. If you talk to Pine Cone, ask them who their competitors are. Be forewarned, this stuff is very expensive. Jared On Tue, 30 Jan 2001, PRINCE, PATRICK W. wrote: We have a purchased application with over 1,300 indexes. Can someone suggest a method to monitor the system to determine which indexes are actively being used over time? I'm assuming that some are old/not necessary and would like to save the overhead of maintaining them. Oracle 8.0.6 Patrick Prince email: [EMAIL PROTECTED] Omaha Public Power District voice: (402) 636-3762 444 S 16th St. Mall, Omaha, NE 68102fax: (402) 636-3931 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: PRINCE, PATRICK W. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Index Usage Monitoring
V$access appears to be pretty unreliable. V$db_object_cache does not appear to carry indexes but does have some good information on tables. -Original Message- Sent: Wednesday, January 31, 2001 2:51 PM To: Multiple recipients of list ORACLE-L And starting with the time immemorial, there is the V$ACCESS table which shows the parse locks that the application has. It also shows the indexes (indices) used by the application. -Original Message- Sent: Wednesday, January 31, 2001 2:42 PM To: Multiple recipients of list ORACLE-L Hi ! Starting from Oracle9i there will be a new view called V$OBJECT_USAGE (Name may change ??) can be used to monitor the index usage (!!) = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Index usage for a table
On Tuesday 30 January 2001 23:47, [EMAIL PROTECTED] wrote: run a query using explain plan ... and use sql trace oli -Original Message- Alan Sent: Tuesday, January 30, 2001 4:40 PM To: Multiple recipients of list ORACLE-L Is there anyway to find out which indexes are being used and which ones are not being used for any given table? Thanks in Advance Alan -- --- Oliver Artelt, System- und Datenbankadministration --- cubeoffice GmbH Co.KG # jordanstrasse 7 # 39112 magdeburg telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19 email: [EMAIL PROTECTED] # web: http://www.cubeoffice.de --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oliver Artelt INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
