Re: Avoiding full table scan
Downloaded and read last night.
An excellent paper, as Tim stated.
Now if I could just get an opportunity to do something like that...
The downside of working for a small company.
Jared
Tim Gorman [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/09/2003 06:09 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: Avoiding full table scan
Same author (Jeff Maresh) has also published a new paper on physical structure of data warehouses to accommodate the life cycle of data. It is fantastic.
I've published both papers ("Managing the Data Lifecycle" and "In Defense of FULL table scans") on my website at "http://www.evdbt.com/papers.htm". The "FULL table scan" paper is excellent, but I think the "Data Lifecycle" paper is ground-breaking, covering topics that have not yet been treated appropriately. I highly recommend them both...
on 10/9/03 10:54 AM, Goulet, Dick at [EMAIL PROTECTED] wrote:
Jack,
In a recent copy of SELECT magazine there is a discussion in defense of full table scans. I believe you might find it VERY interesting. Although I was aware of some of what the author spoke he put it in a vein that makes extreme sense.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
From: Jack van Zanen [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L
Subject: Avoiding full table scan
Hi All,
I wish to avoid a full tablescan on the following data
V. Zanen
Zanen
Van Zanen
..
..
..
Lot's more data
Select * from table where upper(name) like '%ZANEN%'
I could create a function based index on upper(name) but this does not take care of the % and like operator.
Oracle has this (I believe it's called) context stuff that you can index varchar fields etc. Is this the (only possible?) way to go??
TIA
Jack
Avoiding full table scan
Title: Avoiding full table scan Hi All, I wish to avoid a full tablescan on the following data V. Zanen Zanen Van Zanen ... ... ... Lot's more data Select * from table where upper(name) like '%ZANEN%' I could create a function based index on upper(name) but this does not take care of the % and like operator. Oracle has this (I believe it's called) context stuff that you can index varchar fields etc. Is this the (only possible?) way to go?? TIA Jack
RE: Avoiding full table scan
Title: Avoiding full table scan Jack, In a recent copy of SELECT magazine there is a discussion in defense of full table scans. I believe you might find it VERY interesting. Although I was aware of some of what the author spoke he put it in a vein that makes extreme sense. Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Jack van Zanen [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 10:49 AMTo: Multiple recipients of list ORACLE-LSubject: Avoiding full table scan Hi All, I wish to avoid a full tablescan on the following data V. Zanen Zanen Van Zanen ... ... ... Lot's more data Select * from table where upper(name) like '%ZANEN%' I could create a function based index on upper(name) but this does not take care of the % and like operator. Oracle has this (I believe it's called) context stuff that you can index varchar fields etc. Is this the (only possible?) way to go?? TIA Jack
Re: Avoiding full table scan
Jack van Zanen wrote: Hi All, I wish to avoid a full tablescan on the following data V. Zanen Zanen Van Zanen ... ... ... Lot's more data Select * from table where upper(name) like '%ZANEN%' I could create a function based index on upper(name) but this does not take care of the % and like operator. Oracle has this (I believe it's called) context stuff that you can index varchar fields etc. Is this the (only possible?) way to go?? TIA Jack If you index name and put all the columns from the SELECT list into the index, my guess is that you will get an index fast full scan, which may not be that bad. Basically depends on how many blocks you have to wade through. Otherwise I don't see any other solution than Intermedia. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Avoiding full table scan
Title: Re: Avoiding full table scan Same author (Jeff Maresh) has also published a new paper on physical structure of data warehouses to accommodate the life cycle of data. It is fantastic. Ive published both papers (Managing the Data Lifecycle and In Defense of FULL table scans) on my website at http://www.evdbt.com/papers.htm. The FULL table scan paper is excellent, but I think the Data Lifecycle paper is ground-breaking, covering topics that have not yet been treated appropriately. I highly recommend them both... on 10/9/03 10:54 AM, Goulet, Dick at [EMAIL PROTECTED] wrote: Jack, In a recent copy of SELECT magazine there is a discussion in defense of full table scans. I believe you might find it VERY interesting. Although I was aware of some of what the author spoke he put it in a vein that makes extreme sense. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- From: Jack van Zanen [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 10:49 AM To: Multiple recipients of list ORACLE-L Subject: Avoiding full table scan Hi All, I wish to avoid a full tablescan on the following data V. Zanen Zanen Van Zanen ... ... ... Lot's more data Select * from table where upper(name) like '%ZANEN%' I could create a function based index on upper(name) but this does not take care of the % and like operator. Oracle has this (I believe it's called) context stuff that you can index varchar fields etc. Is this the (only possible?) way to go?? TIA Jack
Re: oracle full table scan
Would you please? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 3:28 PM Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out
Re: oracle full table scan
A zip file of the run_stats scripts can be downloaded from http://www.cybcon.com/~jkstill/download/run_stats.zip Jared Igor Neyman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/04/2003 06:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: oracle full table scan Would you please? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 3:28 PM Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask
Full table scan difference
List - As I mentioned earlier, a new manager came from a site that regularly rebuilt tables. I protested the value of this, but his response was well, at least you could test it. So on a test instance I've been doing a CTAS to create a reorganized copy of the table in the same tablespace (LMT with uniform extents, autoextend on). As expected, the number of blocks is very close. Then I've been doing a FTS (select count(*)) on both tables. Usually the time is very close. However, on one large table, the original is much faster -- 18-sec. vs. 27-sec. 21,349 blocks vs. 21179. Same plan. Does anyone have a theory for this discrepancy? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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: Full table scan difference
1. Dial 1-0-0-4-6. 2. Look at the latching and pinning statistics promoted at asktom, ixora, and jlcomp. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Chicago, London, Reykjavik, Ottawa, Denver - Visit www.hotsos.com for schedule details... - IOUG-A Live 2003, Orlando, 10am Monday 28 April: Oracle Operational Timing Data -Original Message- WILLIAMS Sent: Friday, April 04, 2003 2:49 PM To: Multiple recipients of list ORACLE-L List - As I mentioned earlier, a new manager came from a site that regularly rebuilt tables. I protested the value of this, but his response was well, at least you could test it. So on a test instance I've been doing a CTAS to create a reorganized copy of the table in the same tablespace (LMT with uniform extents, autoextend on). As expected, the number of blocks is very close. Then I've been doing a FTS (select count(*)) on both tables. Usually the time is very close. However, on one large table, the original is much faster -- 18-sec. vs. 27-sec. 21,349 blocks vs. 21179. Same plan. Does anyone have a theory for this discrepancy? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap 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: Full table scan difference
I wrote an LIO monitor, that should help to show what kind of LIO you are doing. It could help explain the difference in time. Give it a try and let me know what you see. Download it from oraperf.com Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, April 04, 2003 10:49 PM List - As I mentioned earlier, a new manager came from a site that regularly rebuilt tables. I protested the value of this, but his response was well, at least you could test it. So on a test instance I've been doing a CTAS to create a reorganized copy of the table in the same tablespace (LMT with uniform extents, autoextend on). As expected, the number of blocks is very close. Then I've been doing a FTS (select count(*)) on both tables. Usually the time is very close. However, on one large table, the original is much faster -- 18-sec. vs. 27-sec. 21,349 blocks vs. 21179. Same plan. Does anyone have a theory for this discrepancy? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk 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: Full table scan difference
Cary, Denny - Thanks very much. I was running out of ideas on a Friday afternoon, then I just ran out. Have a good weekend, will try these suggestions Monday. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, April 04, 2003 3:54 PM To: Multiple recipients of list ORACLE-L 1. Dial 1-0-0-4-6. 2. Look at the latching and pinning statistics promoted at asktom, ixora, and jlcomp. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Chicago, London, Reykjavik, Ottawa, Denver - Visit www.hotsos.com for schedule details... - IOUG-A Live 2003, Orlando, 10am Monday 28 April: Oracle Operational Timing Data -Original Message- WILLIAMS Sent: Friday, April 04, 2003 2:49 PM To: Multiple recipients of list ORACLE-L List - As I mentioned earlier, a new manager came from a site that regularly rebuilt tables. I protested the value of this, but his response was well, at least you could test it. So on a test instance I've been doing a CTAS to create a reorganized copy of the table in the same tablespace (LMT with uniform extents, autoextend on). As expected, the number of blocks is very close. Then I've been doing a FTS (select count(*)) on both tables. Usually the time is very close. However, on one large table, the original is much faster -- 18-sec. vs. 27-sec. 21,349 blocks vs. 21179. Same plan. Does anyone have a theory for this discrepancy? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap 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: 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: oracle full table scan
Perhaps you need to do a trace to determine the real cause of you problems. Full table scans are not necessarily the problem. When you have trace for the program and the explain plain you have of the executing SQL you will have a better idea than assuming you need indexes to stop full table scans. Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Arvind Kumar [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/04/2003 02:58 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:oracle full table scan Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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: 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: oracle full table scan
Hi Arvind, A little test for you. You have a table that contains 10,000,000 rows that is packed tightly into 1,000,000 data blocks. You have an index that has a level of 4 and has 10,000 leaf blocks. The table is well striped across a number of devices and you have 4 CPUs on the box. You write a simple select statement that queries the table based on the indexed column and *just 10%* of the data needs to be retrieved. You determine that the CBO has performed a full table scan. Do you break out into a nervous sweat or do you sigh thank goodness and worry about something else instead ? Cheers Richard (let me know if you want to know the comparative costs ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 2:58 PM Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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: Richard Foote 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: oracle full table scan
Title: RE: oracle full table scan To answer the original question ... 1. use following query to see which tables are part of FTS ... it is a point in time information. (Query from www.ixora.com I think). SELECT usr.name oowner, ob.name oname FROM ( SELECT obj FROM sys.X_$BH WHERE TO_NUMBER(bitand(flag, POWER(2,19))) 0 GROUP BY obj) bh, sys.obj$ ob, sys.USER$ usr WHERE ob.dataobj# = bh.obj AND ob.owner# = usr.USER# ORDER BY usr.name, ob.name / 2. FTS can happen for many reasons ... if Oracle is performing FTS on a small table, that's the way to do it. Remember when you create an index Oracle had to perform 2 IOs, one for INDEX lookup and (if required) one for Table lookup. Sometimes associated costs dictate that a FTS is cheaper than the combined cost (of index lookup and table lookup), so Oracle prefers that. One upon a time, I used to think on the same lines, but the bright minds on this list have time and again proven that FTS, isn't a bad thing after all. Sometimes it is, but not ALL the times. Creating indexes is not the solution, a careful analysis of the logic implemented in the SQL is also required, and you will be surprised that, just by making the query changes, the performance gain can be achieved. PS: Stephane, you probably have this on the top of your Oracle Myth list ... right? YMMV Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 2:58 PM Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: oracle full table scan
Hi Arvind,
I don't judge full table scan is good or not necessary bad.
this is the script might answer your question.
-joan
The following scripts provide information on the full table scan
activity.
If your application is OLTP only, having long full table scans can be an
indicator of having missing or incorrect indexes or untuned SQL.
#
drop table Full_Table_Scans
/
create table Full_Table_Scans as
select ss.username||'('||se.sid||') ' User Process,
sum(decode(name,'table scans (short tables)',value)) Short Scans,
sum(decode(name,'table scans (long tables)', value)) Long Scans,
sum(decode(name,'table scan rows gotten',value)) Rows Retreived
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
OR name like '%table scans (long tables)%'
OR name like '%table scan rows gotten%' )
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') ';
column User Process format a20;
column Long Scans format 999,999,999;
column Short Scans format 999,999,999;
column Rows Retreived format 999,999,999;
column Average Long Scan Length format 999,999,999;
ttitle ' Table Access Activity By User '
select User Process, Long Scans, Short Scans, Rows Retreived
from Full_Table_Scans
order by Long Scans desc;
Richard Foote wrote:
Hi Arvind,
A little test for you.
You have a table that contains 10,000,000 rows that is packed tightly
into 1,000,000 data blocks.
You have an index that has a level of 4 and has 10,000 leaf blocks.
The table is well striped across a number of devices and you have 4
CPUs on the box.
You write a simple select statement that queries the table based on
the indexed column and *just 10%* of the data needs to be retrieved.
You determine that the CBO has performed a full table scan.
Do you break out into a nervous sweat or do you sigh thank goodness
and worry about something else instead ?
Cheers
Richard (let me know if you want to know the comparative costs ;)
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, April 03, 2003 2:58 PM
Dear All,
is there any way to find which tables (table name) are
suffering from
full table scan ,so that i can create indexes on them to enhance the
performance.
Thanks
Arvind
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Arvind Kumar
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: Richard Foote
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: Joan Hsieh
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: oracle full table scan
Hello Arvind, Thursday, April 3, 2003, 5:58:38 AM, you wrote: AK Dear All, AK is there any way to find which tables (table name) are suffering from AK full table scan ,so that i can create indexes on them to enhance the AK performance. AK Thanks AK Arvind AK -- AK Please see the official ORACLE-L FAQ: http://www.orafaq.net Use SQL_TRACE feature to find all statements which used FTS. -- Best regards, Alexmailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alex Andriyashchenko 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: oracle full table scan
Tables are not suffering, they're rather cruel and coldhearted. As in the Tom Godwin's story, The Cold Equations, it's always the users who pay the price. You might try with tuning the SQL statements that access tables. Occasionally, that does the trick. -Original Message- Sent: Wednesday, April 02, 2003 11:59 PM To: Multiple recipients of list ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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: Gogala, Mladen 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: oracle full table scan
Arvind - If you want to locate tables that are being scanned and the SQL
statement, I have found the following script posted by Mohammed to work
quite effectively.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
REM From: Mohammed Shakir [mailto:[EMAIL PROTECTED]
REM Sent: Thursday, October 10, 2002 5:14 PM
REM To: Multiple recipients of list ORACLE-L
REM Subject: RE: Table Scans
REM Try the following script. I am not sure where I found it on the web.
REM However, this script I use to find the bottlenecks in the system.
REM Run it while your application is running.
REM Look for wait event 'db_file_scattered_read'.
REM Check the related SQL.
REM You can remove other wait events if you do not need them.
set echo off feedback off timing off pause off
set pages 100 lines 500 trimspool on trimout on space 1 recsep each
col sid format 990
col program format a15 word_wrap
col event format a8 word_wrap
col ospid format 990 heading Srvr|PID
col name format a15 word_wrap heading OBJECT NAME
col sql_text format a30 word_wrap
select /*+ rule */
w.sid,
w.event,
s.program,
p.spid ospid,
e.owner || '.' || e.segment_name || ' (' || e.segment_type || ')'
name,
a.sql_text
fromsys.v_$sqlarea a,
sys.dba_extents e,
sys.v_$process p,
sys.v_$session s,
sys.v_$session_wait w
where w.event in ('write complete waits',
'latch free',
'log buffer space',
'free buffer waits',
'buffer busy waits',
'db file scattered read',
'db file sequential read',
'library cache pin',
'log file switch completion',
'enqueue',
'log file parallel write',
'db file parallel write',
'log file sync',
'file open',
'direct path write',
'library cache lock')
and s.sid = w.sid
and p.addr = s.paddr
and e.file_id = to_number(w.p1)
and to_number(w.p2) between e.block_id and (e.block_id + (e.blocks -
1))
and a.address (+) = s.sql_address;
-Original Message-
Sent: Wednesday, April 02, 2003 10:59 PM
To: Multiple recipients of list ORACLE-L
Dear All,
is there any way to find which tables (table name) are suffering from
full table scan ,so that i can create indexes on them to enhance the
performance.
Thanks
Arvind
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Arvind Kumar
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: 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: oracle full table scan
Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 05:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan To answer the original question ... 1. use following query to see which tables are part of FTS ... it is a point in time information. (Query from www.ixora.com I think). SELECT usr.name oowner, ob.name oname FROM ( SELECT obj FROM sys.X_$BH WHERE TO_NUMBER(bitand(flag, POWER(2,19))) 0 GROUP BY obj) bh, sys.obj$ ob, sys.USER$ usr WHERE ob.dataobj# = bh.obj AND ob.owner# = usr.USER# ORDER BY usr.name, ob.name / 2. FTS can happen for many reasons ... if Oracle is performing FTS on a small table, that's the way to do it. Remember when you create an index Oracle had to perform 2 IOs, one for INDEX lookup and (if required) one for Table lookup. Sometimes associated costs dictate that a FTS is cheaper than the combined cost (of index lookup and table lookup), so Oracle prefers that. One upon a time, I used to think on the same lines, but the bright minds on this list have time and again proven that FTS, isn't a bad thing after all. Sometimes it is, but not ALL the times. Creating indexes is not the solution, a careful analysis of the logic implemented in the SQL is also required, and you will be surprised that, just by making the query changes, the performance gain can be achieved. PS: Stephane, you probably have this on the top of your Oracle Myth list ... right? YMMV Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 2:58 PM Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: oracle full table scan
Title: RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Subject: RE: oracle full table scan Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 05:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: oracle full table scan To answer the original question ... 1. use following query to see which tables are part of FTS ... it is a point in time information. (Query from www.ixora.com I think). SELECT usr.name oowner, ob.name oname FROM ( SELECT obj FROM sys.X_$BH WHERE TO_NUMBER(bitand(flag, POWER(2,19))) 0 GROUP BY obj) bh, sys.obj$ ob, sys.USER$ usr WHERE ob.dataobj# = bh.obj AND ob.owner# = usr.USER# ORDER BY usr.name, ob.name / 2. FTS can happen for many reasons ... if Oracle is performing FTS on a small table, that's the way to do it. Remember when you create an index Oracle had to perform 2 IOs, one for INDEX lookup and (if required) one for Table lookup. Sometimes associated costs dictate that a FTS is cheaper than the combined cost (of index lookup and table lookup), so Oracle prefers that. One upon a time, I used to think on the same lines, but the bright minds on this list have time and again proven that FTS, isn't a bad thing after all. Sometimes it is, but not ALL the times. Creating indexes is not the solution, a careful analysis of the logic implemented in the SQL is also required, and you will be surprised that, just by making the query changes, the performance gain can be achieved. PS: Stephane, you probably have this on the top of your Oracle Myth list ... right? YMMV Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 2:58 PM Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: oracle full table scan
Comparing users to Marilyn Cross. Naive, not overly bright, sentenced to death. Is that too harsh for users? Jared Gogala, Mladen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 07:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Tables are not suffering, they're rather cruel and coldhearted. As in the Tom Godwin's story, The Cold Equations, it's always the users who pay the price. You might try with tuning the SQL statements that access tables. Occasionally, that does the trick. -Original Message- Sent: Wednesday, April 02, 2003 11:59 PM To: Multiple recipients of list ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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: Gogala, Mladen 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: 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: oracle full table scan
Jared, I like you more and more every day. -Original Message- Sent: Thursday, April 03, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Comparing users to Marilyn Cross. Naive, not overly bright, sentenced to death. Is that too harsh for users? Jared Gogala, Mladen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 07:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Tables are not suffering, they're rather cruel and coldhearted. As in the Tom Godwin's story, The Cold Equations, it's always the users who pay the price. You might try with tuning the SQL statements that access tables. Occasionally, that does the trick. -Original Message- Sent: Wednesday, April 02, 2003 11:59 PM To: Multiple recipients of list ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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: Gogala, Mladen 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: 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: Gogala, Mladen 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: oracle full table scan
Jamadagni, Rajendra wrote: PS: Stephane, you probably have this on the top of your Oracle Myth list ... right? YMMV Raj Indeed, together with 'always replace NOT IN with NOT EXISTS ...' - another case today ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: oracle full table scan
only for some. unfortunately not for most of mine --- [EMAIL PROTECTED] wrote: Comparing users to Marilyn Cross. Naive, not overly bright, sentenced to death. Is that too harsh for users? Jared Gogala, Mladen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 07:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Tables are not suffering, they're rather cruel and coldhearted. As in the Tom Godwin's story, The Cold Equations, it's always the users who pay the price. You might try with tuning the SQL statements that access tables. Occasionally, that does the trick. -Original Message- Sent: Wednesday, April 02, 2003 11:59 PM To: Multiple recipients of list ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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: Gogala, Mladen 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: 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!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: oracle full table scan
If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared view.sql Description: Binary data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. grants.sql Description: Binary data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. run_stats.sql Description: Binary data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Runstats.sql This is the test harness I use to try out different ideas. It shows two vital sets of statistics for me The elapsed time difference between two approaches. It very simply shows me which approach is faster by the wall clock How many resources each approach takes. This can be more meaningful then even the wall clock timings. For example, if one approach is faster then the other but it takes thousands of latches (locks), I might avoid it simply because it will not scale as well. The way this test harness works is by saving the system statistics and latch information into a temporary table. We then run a test and take another snapshot. We run the second test and take yet another snapshot. Now we can show the amount of resources used by approach 1 and approach 2. Requirements In order to run this test harness you must at a minimum have: Access to V$STATNAME, V$MYSTAT, and V$LATCH If you want to use the view as I have, you must be granted select DIRECTLY on SYS.V_$STATNAME, SYS.V_$MYSTAT, and SYS.V_$LATCH. It will not work to have select on these via a ROLE. You can still run the test harness, you just will not be using the view STATS I have below (substitute in the query text in the PLSQL block where I reference the view STATS). The ability to create a table -- run_stats -- to hold the before, during and after information. You should note also that the LATCH information is collected on a SYSTEM WIDE basis. If you run this on a multi-user system, the latch information may be technically incorrect as you will count the latching information for other sessions - not just your
RE: oracle full table scan
Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. view.sql Description: Binary data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing
RE: oracle full table scan
I got the attachments... --- [EMAIL PROTECTED] wrote: Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. ATTACHMENT part 2 application/octet-stream name=view.sql The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary
RE: oracle full table scan
Did you look at them... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: oracle full table scan 04/03/2003 02:08 PM Please respond to ORACLE-L I got the attachments... --- [EMAIL PROTECTED] wrote: Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists
RE: oracle full table scan
not closely enough :) my only excuse is that I had spent a few hours in a rah rah, we are wonderful meeting today after we laid off 400 people last week. brain dead. must get caffeine... immediately! --- Ron Thomas [EMAIL PROTECTED] wrote: Did you look at them... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: oracle full table scan 04/03/2003 02:08 PM Please respond to ORACLE-L I got the attachments... --- [EMAIL PROTECTED] wrote: Sorry, the attachments didn't make it, though they were only text. I can put them some accessible via the web if anyone wants them. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 11:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'. I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. should a small lookup table BE an index (IOT)? ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask
oracle full table scan
Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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: oracle full table scan
Arvind, You probably need to look into Explain Plans or Tracing since they both show what objects are used in queries. Explain Plans are useful to see what a query is likely to do, Tracing is useful when you don't have access to the queries or suspect that what the query is doing will be different to what the explain plan says. There are many, many tools to help you achieve this goal - try www.orafaq.net/tools perhaps. Also, I am curious why someone working at a company called SQL Star International has to go to a list to find this answer? Arvind Kumar [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rintl.comcc: Sent by: Subject: oracle full table scan [EMAIL PROTECTED] m 03/04/2003 14:58 Please respond to ORACLE-L Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arvind Kumar 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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: oracle full table scan
Dear All, is there any way to find which tables (table name) are suffering from full table scan ,so that i can create indexes on them to enhance the performance. Thanks Arvind A time for indexed access; and a time for full scans. A time for nested loops; a time for hash joins. The Ecclesiastes, 3:8.3.0.5 Equating full scans to bad performance is not always true. It's more a matter of how much data you browse through compared to what you ultimately want to return. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Full table scan
Of course it will do full table scans! You're not limiting your join to any set of values, you are trying to read two whole tables. Because of the multiblock reads, full table scans and hash join will be more efficient then a nested loops plan where one table is read sequentially and the other is read thru the PK index. Optimizer made the rigtht decision. -Original Message- From: Venu Gopal Andem [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 11:09 PM To: Multiple recipients of list ORACLE-L Subject: Full table scan List, I have the following scnario: I'm doing a select from 2 tables joining them using a primary key- ref key on the tables and the explain plan is showing 2 full table scans... Even after analyzing the tables and the Optimizer mode is set to CHOOSE. SELECT a.col1, b.col2 FROM table1 a, table2 b WHERE a.col1 = b.col1 Any comments... Thanks in advance, Venu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen 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: Full table scan
Title: RE: Full table scan Venu, How may records? If it's just a few hundred, a full table scan might be faster. Throw a RULE hint at it and see what happens then. What version of Oracle? CBO in 7.3.4 wasn't all that smart. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Venu Gopal Andem [SMTP:[EMAIL PROTECTED]] List, I have the following scnario: I'm doing a select from 2 tables joining them using a primary key- ref key on the tables and the explain plan is showing 2 full table scans... Even after analyzing the tables and the Optimizer mode is set to CHOOSE. SELECT a.col1, b.col2 FROM table1 a, table2 b WHERE a.col1 = b.col1 Any comments... Thanks in advance, Venu
Full table scan
List, I have the following scnario: I'm doing a select from 2 tables joining them using a primary key- ref key on the tables and the explain plan is showing 2 full table scans... Even after analyzing the tables and the Optimizer mode is set to CHOOSE. SELECT a.col1, b.col2 FROM table1 a, table2 b WHERE a.col1 = b.col1 Any comments... Thanks in advance, Venu **Disclaimer** Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited.
Re: Full table scan
At 09:08 PM 2/4/2003, you wrote: I'm doing a select from 2 tables joining them using a primary key- ref key on the tables and the explain plan is showing 2 full table scans... Even after analyzing the tables and the Optimizer mode is set to CHOOSE. SELECT a.col1, b.col2 FROM table1 a, table2 b WHERE a.col1 = b.col1 Unless you have an index on (table2.col1, table2.col2), Oracle is going to have to go to table b for col2. Assuming that most rows in table2 will be involved in the results of the join, it's cheaper for Oracle to do a FTS on table2 than to do the join using only the PK indexes and then look up the value of col2 in the table. Justin Cave Distributed Database Consulting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Justin Cave 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: Full table scan
Hi Venu, Is it a.col1 as primary key of table1 and b.col1 as primary key of table2 ? if yes try this, select /*+ FIRST_ROWS */ a.col1, b.col2 from table1 a, table2 b where a.col1 = b.col1 Rgrds, Sony -Original Message- From: Venu Gopal Andem [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 11:09 AM To: Multiple recipients of list ORACLE-L Subject: Full table scan List, I have the following scnario: I'm doing a select from 2 tables joining them using a primary key- ref key on the tables and the explain plan is showing 2 full table scans... Even after analyzing the tables and the Optimizer mode is set to CHOOSE. SELECT a.col1, b.col2 FROM table1 a, table2 b WHERE a.col1 = b.col1 Any comments... Thanks in advance, Venu File: InterScan_Disclaimer.txt -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto 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: FULL TABLE SCAN?
Use Excel. K. ;O) -Original Message- Sent: 07 January 2003 16:29 To: Multiple recipients of list ORACLE-L Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: Thomas, Kevin 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: FULL TABLE SCAN?
I think you are new to the list. All '_' parameters given as a solution without a warning that their use can be dangerous, are meant as fun and nothing else. Regards Naveen -Original Message- Sent: Wednesday, January 08, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Is it good to use this (_full_table_scan=FALSE)? If there are less than (say about 1 lakh records), a full scan of table is better. How efficient is the usage of the above by setting it to false. Rgds, Satya On Tue, 07 Jan 2003 Koivu, Lisa wrote : _full_table_scan=FALSE -Original Message- Sent: Tuesday, January 07, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: Satya V Prakash 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: Naveen Nahata 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: FULL TABLE SCAN?
Title: RE: FULL TABLE SCAN? Don't use tables, just use views ... view scans are better and faster, because by definition views are just definitions, so it will be faster. Memory scans are faster than disk access by a factor of (say) 10,000 (if you believe manufacturer's claims). 8:) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: 07 January 2003 16:29 To: Multiple recipients of list ORACLE-L Hi How to avoid FULL TABLE SCAN? Thx -seema This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
FULL TABLE SCAN?
Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: FULL TABLE SCAN?
Title: RE: FULL TABLE SCAN? _full_table_scan=FALSE -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 07, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: FULL TABLE SCAN? Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: FULL TABLE SCAN?
1. Set optimizer_mode to RULE. 2. Make sure all statements have a WHERE clause. 3. Dont use functions in the equality clauses. 4. Create an index on each and every column you have in the database. Take my advice. I dont use it anyway :))) Raj Seema Singh oracledbam@hoTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tmail.comcc: Sent by: Subject: FULL TABLE SCAN? [EMAIL PROTECTED] om January 07, 2003 11:28 AM Please respond to ORACLE-L Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: 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: FULL TABLE SCAN?
At great personal risk, I will tell you some secrets. There are many options. 1) Don't query data. To really enforce this, remove select, insert, update and delete privileges from all users. 2) If you must query data, use an index and only an index. Create indexes that cover all possible combinations of data in the table. 3) If you see a FULL TABLE SCAN coming your way, grab your cellphone and appear to be in deep conversation with someone and totally oblivious to everything around you (this is especially good when driving in heavy traffic). 4) If the FULL TABLE SCAN comes up to you and begins talking, pretend like you don't speak SQL. And, just so I am not crucified by those with 0 sense of humor... read the previous posts on FTS! -Original Message- Sent: Tuesday, January 07, 2003 9:29 AM To: Multiple recipients of list ORACLE-L Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: Fink, Dan 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: FULL TABLE SCAN?
Title: RE: FULL TABLE SCAN? A Where clause in your SQL and indexes that support the Where clause are a good place to start. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Seema Singh [SMTP:[EMAIL PROTECTED]] Hi How to avoid FULL TABLE SCAN? Thx -seema
RE: FULL TABLE SCAN?
Create a meaningful index and keep your stats up to date. Dave The OT list rules -Original Message- Sent: Tuesday, January 07, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: Farnsworth, Dave 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: FULL TABLE SCAN?
Seema: Use an index, obviously. If and index exists, ensure that the query uses it. The select columns order should match the order of the index columns. Or try using a HINT. Sometimes a full scan is not a bad thing. Are there less than 100,000 rows in the table? Sometimes the optimizer will execute a full table scan because it is faster that way. RWB Seema Singh [EMAIL PROTECTED]@fatcity.com on 01/07/2003 10:28:53 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: 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: FULL TABLE SCAN?
Title: RE: FULL TABLE SCAN? It's a bit buggy in 8i! -Original Message-From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 1:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: FULL TABLE SCAN? Is that backported to 8i and 7.3 ??? :) ;) Thanks. - Kirti -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 11:05 AMTo: Multiple recipients of list ORACLE-LSubject: RE: FULL TABLE SCAN? _full_table_scan=FALSE -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 07, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: FULL TABLE SCAN? Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: FULL TABLE SCAN?
Is it good to use this (_full_table_scan=FALSE)? If there are less than (say about 1 lakh records), a full scan of table is better. How efficient is the usage of the above by setting it to false. Rgds, Satya On Tue, 07 Jan 2003 Koivu, Lisa wrote : _full_table_scan=FALSE -Original Message- Sent: Tuesday, January 07, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: Satya V Prakash 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: FULL TABLE SCAN?
Title: RE: FULL TABLE SCAN? Is that backported to 8i and 7.3 ??? :) ;) Thanks. - Kirti -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 11:05 AMTo: Multiple recipients of list ORACLE-LSubject: RE: FULL TABLE SCAN? _full_table_scan=FALSE -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 07, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: FULL TABLE SCAN? Hi How to avoid FULL TABLE SCAN? Thx -seema _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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).
Full table scan error
Hi List,
I have the below query whose explain plan is showing that it is doing full
table scan on Historie table:
select F1.AMTLICHESKENNZEICHEN
AMTLICHESKENNZEICHEN,F1.OID,F1.VERKAUFSBEZEICHNUNG,B1.FAHRZEUGARTTEXT,B1.FAH
RZEUGHERSTELLERTEXT,B1.FAHRZEUGTYPTEXT,B1.FIN,B1.VERKAUFSBEZEICHNUNG,H1.AUFT
RAGSPOSITIONSNR,H1.DATUMSTR,H1.OID,H1.PRODUKT,H1.VORGANGSNUMMER,'Stamm'
SOURCE
from ZPAB.FAHRZEUG F1 , ZPAB.FZGBRIEF B1 , ZPAB.HISTORIE H1
where F1.FZGBRIEF = B1.OID AND F1.OID = H1.MYTECHOBJEKT(+) AND
((H1.produkt, TO_DATE(H1.DATUMSTR,'-MM-DD')) IN (select
ZPAB.HISTORIE.produkt, TO_DATE(MAX(ZPAB.historie.DATUMSTR),'-mm-dd')
from ZPAB.historie, ZPAB.FAHRZEUG
WHERE ZPAB.FAHRZEUG.OID = ZPAB.historie.MYTECHOBJEKT AND ZPAB.FAHRZEUG.OID =
F1.OID AND ZPAB.historie.PRODUKT
IN('HU','AU','SP','HUPlus','GGVS','P193','P21','UVVFahrzeuganbau','Ersatzpla
kette','SOL')
group by ZPAB.historie.produkt) OR H1.PRODUKT IN
('StandardGutachten','SchadenGutachten','BewertungZustandspruefung','MagBewe
rtungZustandspruefung','Transportschadenbericht','Reparaturpruefung','Rechnu
ngspruefungsbericht','Reparaturpruefungsbericht','FzgSchadengutachten','Hage
lschadenbericht','Massenschadenbericht','Kalkulationsbericht','Schadenberich
t','Bericht','DekraSiegel','NfzSchadenGutachten','HypoWBWGutachten','MagGuta
chten','MagBewertung','Bewertung') OR F1.OID NOT IN (SELECT
ZPAB.FAHRZEUG.OID
FROM ZPAB.FAHRZEUG,ZPAB.HISTORIE
WHERE ZPAB.FAHRZEUG.OID = ZPAB.historie.MYTECHOBJEKT)) AND
(F1.AMTLICHESKENNZEICHEN LIKE 'DD%')AND rownum =10
and for HISTORIE, it is showing TABLE ACCESS FULL.
I have created indexes on produkt and mytechobjekt columns of historie
table. Still I am getting full table scan errors. I am sending herewith
the snapshot of the explain plan as seen in spotlight.
Could you please advise me as to how do I eradicate the full table access
error on Historie table? Any help in this regard is very much appreciated.
Thanks and Regards,
Ranganath
Doc1.doc
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this message
by anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
Doc1.doc
Description: MS-Word document
Re: Full table scan error
You have a outer join in yr history table. Also you have a IN condition for
the history table for which the CBO might think a FTS might be cheaper..
Babu
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, January 03, 2003 7:19 AM
Hi List,
I have the below query whose explain plan is showing that it is doing full
table scan on Historie table:
select F1.AMTLICHESKENNZEICHEN
AMTLICHESKENNZEICHEN,F1.OID,F1.VERKAUFSBEZEICHNUNG,B1.FAHRZEUGARTTEXT,B1.FAH
RZEUGHERSTELLERTEXT,B1.FAHRZEUGTYPTEXT,B1.FIN,B1.VERKAUFSBEZEICHNUNG,H1.AUFT
RAGSPOSITIONSNR,H1.DATUMSTR,H1.OID,H1.PRODUKT,H1.VORGANGSNUMMER,'Stamm'
SOURCE
from ZPAB.FAHRZEUG F1 , ZPAB.FZGBRIEF B1 , ZPAB.HISTORIE H1
where F1.FZGBRIEF = B1.OID AND F1.OID = H1.MYTECHOBJEKT(+) AND
((H1.produkt, TO_DATE(H1.DATUMSTR,'-MM-DD')) IN (select
ZPAB.HISTORIE.produkt, TO_DATE(MAX(ZPAB.historie.DATUMSTR),'-mm-dd')
from ZPAB.historie, ZPAB.FAHRZEUG
WHERE ZPAB.FAHRZEUG.OID = ZPAB.historie.MYTECHOBJEKT AND ZPAB.FAHRZEUG.OID
=
F1.OID AND ZPAB.historie.PRODUKT
IN('HU','AU','SP','HUPlus','GGVS','P193','P21','UVVFahrzeuganbau','Ersatzpla
kette','SOL')
group by ZPAB.historie.produkt) OR H1.PRODUKT IN
('StandardGutachten','SchadenGutachten','BewertungZustandspruefung','MagBewe
rtungZustandspruefung','Transportschadenbericht','Reparaturpruefung','Rechnu
ngspruefungsbericht','Reparaturpruefungsbericht','FzgSchadengutachten','Hage
lschadenbericht','Massenschadenbericht','Kalkulationsbericht','Schadenberich
t','Bericht','DekraSiegel','NfzSchadenGutachten','HypoWBWGutachten','MagGuta
chten','MagBewertung','Bewertung') OR F1.OID NOT IN (SELECT
ZPAB.FAHRZEUG.OID
FROM ZPAB.FAHRZEUG,ZPAB.HISTORIE
WHERE ZPAB.FAHRZEUG.OID = ZPAB.historie.MYTECHOBJEKT)) AND
(F1.AMTLICHESKENNZEICHEN LIKE 'DD%')AND rownum =10
and for HISTORIE, it is showing TABLE ACCESS FULL.
I have created indexes on produkt and mytechobjekt columns of historie
table. Still I am getting full table scan errors. I am sending herewith
the snapshot of the explain plan as seen in spotlight.
Could you please advise me as to how do I eradicate the full table access
error on Historie table? Any help in this regard is very much
appreciated.
Thanks and Regards,
Ranganath
Doc1.doc
WARNING: The information in this message is confidential and may be
legally
privileged. It is intended solely for the addressee. Access to this
message
by anyone else is unauthorised. If you are not the intended recipient,
any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be
unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Babu Nagarajan
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: Full table scan error
2 things:
1) don't send attachments to the list, they rarely
arrive unscathed. Yours didn't.
2) why do you think the full table scan is an 'error'?
It could be that an FTS is the fastest access method
for this query.
3) ok, three things. Please make an attempt to format
longish queries so that they are somewhat readable.
Personally, I always format queries before working with
them, otherwise it is too difficult to determine what is
being done in the query.
Jared
On Friday 03 January 2003 04:19, Krishnaswamy, Ranganath wrote:
Hi List,
I have the below query whose explain plan is showing that it is doing full
table scan on Historie table:
select F1.AMTLICHESKENNZEICHEN
AMTLICHESKENNZEICHEN,F1.OID,F1.VERKAUFSBEZEICHNUNG,B1.FAHRZEUGARTTEXT,B1.FA
H
RZEUGHERSTELLERTEXT,B1.FAHRZEUGTYPTEXT,B1.FIN,B1.VERKAUFSBEZEICHNUNG,H1.AUF
T RAGSPOSITIONSNR,H1.DATUMSTR,H1.OID,H1.PRODUKT,H1.VORGANGSNUMMER,'Stamm'
SOURCE
from ZPAB.FAHRZEUG F1 , ZPAB.FZGBRIEF B1 , ZPAB.HISTORIE H1
where F1.FZGBRIEF = B1.OID AND F1.OID = H1.MYTECHOBJEKT(+) AND
((H1.produkt, TO_DATE(H1.DATUMSTR,'-MM-DD')) IN (select
ZPAB.HISTORIE.produkt, TO_DATE(MAX(ZPAB.historie.DATUMSTR),'-mm-dd')
from ZPAB.historie, ZPAB.FAHRZEUG
WHERE ZPAB.FAHRZEUG.OID = ZPAB.historie.MYTECHOBJEKT AND ZPAB.FAHRZEUG.OID
= F1.OID AND ZPAB.historie.PRODUKT
IN('HU','AU','SP','HUPlus','GGVS','P193','P21','UVVFahrzeuganbau','Ersatzpl
a kette','SOL')
group by ZPAB.historie.produkt) OR H1.PRODUKT IN
('StandardGutachten','SchadenGutachten','BewertungZustandspruefung','MagBew
e
rtungZustandspruefung','Transportschadenbericht','Reparaturpruefung','Rechn
u
ngspruefungsbericht','Reparaturpruefungsbericht','FzgSchadengutachten','Hag
e
lschadenbericht','Massenschadenbericht','Kalkulationsbericht','Schadenberic
h
t','Bericht','DekraSiegel','NfzSchadenGutachten','HypoWBWGutachten','MagGut
a chten','MagBewertung','Bewertung') OR F1.OID NOT IN (SELECT
ZPAB.FAHRZEUG.OID
FROM ZPAB.FAHRZEUG,ZPAB.HISTORIE
WHERE ZPAB.FAHRZEUG.OID = ZPAB.historie.MYTECHOBJEKT)) AND
(F1.AMTLICHESKENNZEICHEN LIKE 'DD%')AND rownum =10
and for HISTORIE, it is showing TABLE ACCESS FULL.
I have created indexes on produkt and mytechobjekt columns of historie
table. Still I am getting full table scan errors. I am sending herewith
the snapshot of the explain plan as seen in spotlight.
Could you please advise me as to how do I eradicate the full table access
error on Historie table? Any help in this regard is very much appreciated.
Thanks and Regards,
Ranganath
Doc1.doc
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.
Content-Type: application/msword; name=Doc1.doc
Content-Transfer-Encoding: 7bit
Content-Description:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
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: Full table scan error
Personally, I always format queries before working with them, otherwise it is too difficult to determine what is being done in the query. Jared Glad to see a fellow maniac :-). Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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: Full table scan error
Yea same here, column names, table names all on separate lines :) joe Personally, I always format queries before working with them, otherwise it is too difficult to determine what is being done in the query. Jared Glad to see a fellow maniac :-). Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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). Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa 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: Full table scan error
And people get so pissed when you take the time to do this while they are standing over your shoulder... I take a perverse pleasure in making them wait... While we are on the subject... I also get annoyed by mixed case... I hate the queries that look like... SELECT * from MY_table_NAME a, my_other_table B Where b.COLUMN_ONE = a.column_one One of my many things that annoy me... :-) Tim -Original Message- Sent: Friday, January 03, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Yea same here, column names, table names all on separate lines :) joe Personally, I always format queries before working with them, otherwise it is too difficult to determine what is being done in the query. Jared Glad to see a fellow maniac :-). Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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). Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa 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: Johnston, Tim 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).
SQueaL formatters (was: Full table scan error)
FWIW, TOAD includes some version of Formatter Plus, which does a fair job on simple queries. It doesn't handle subqueries, though. Perhaps that's only with the separately licensable product. I *think* it even comes with the freeware version of TOAD: http://www.toadsoft.com Any other SQL (pronounced: SQueaL) formatters that folks here like? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Subject: Re: Full table scan error 2 things: 1) don't send attachments to the list, they rarely arrive unscathed. Yours didn't. 2) why do you think the full table scan is an 'error'? It could be that an FTS is the fastest access method for this query. 3) ok, three things. Please make an attempt to format longish queries so that they are somewhat readable. Personally, I always format queries before working with them, otherwise it is too difficult to determine what is being done in the query. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Full table scan error
tr [A-Z] [a-z] lame.sql lower.sql This of course hoses any quoted arguments in the where clause, but those are easy to fix. Jared Johnston, Tim [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/03/2003 01:53 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Re: Full table scan error And people get so pissed when you take the time to do this while they are standing over your shoulder... I take a perverse pleasure in making them wait... While we are on the subject... I also get annoyed by mixed case... I hate the queries that look like... SELECT * from MY_table_NAME a, my_other_table B Where b.COLUMN_ONE = a.column_one One of my many things that annoy me... :-) Tim -Original Message- Sent: Friday, January 03, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Yea same here, column names, table names all on separate lines :) joe Personally, I always format queries before working with them, otherwise it is too difficult to determine what is being done in the query. Jared Glad to see a fellow maniac :-). Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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). Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa 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: Johnston, Tim 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: 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).
causing full table scan
Hi list , In one of my queries if I use in where clause it is causing full table scan . If i use "IN " ( ) it uses index . Actually if it is" " the it is doing hash join and if"IN" then nested loop . when i make opt_indx_cost_adj to 5 it starts using index again with condition . Is there a way i can cause it to use index ( yeah i can use hints . ) ? anything else .. -bp
RE: causing full table scan
bp, indexes are used when limiting conditions are equalities not inequalities (, !=, NOT IN) How about rewriting the query to replace the byan outer join. -Original Message-From: BigP [mailto:[EMAIL PROTECTED]]Sent: Tuesday, July 30, 2002 3:20 PMTo: Multiple recipients of list ORACLE-LSubject: causing full table scan Hi list , In one of my queries if I use in where clause it is causing full table scan . If i use "IN " ( ) it uses index . Actually if it is" " the it is doing hash join and if"IN" then nested loop . when i make opt_indx_cost_adj to 5 it starts using index again with condition . Is there a way i can cause it to use index ( yeah i can use hints . ) ? anything else .. -bp
RE: Does start with/connect by uses full table scan?
You want to make sure there is an index on the start with and connect by clauses. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Monday, September 24, 2001 2:36 PM To: Multiple recipients of list ORACLE-L List, Does Oracle go for full table scan or does it use indexes when we use start with/connect by clause? Thanks, Rao -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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: Christopher Spence 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: Does start with/connect by uses full table scan?
According to this page, you would use concatenated indexes: http://www.arsdigita.com/books/sql/trees.html Hmmm. g -Original Message- Sent: Monday, September 24, 2001 7:36 PM To: Multiple recipients of list ORACLE-L List, Does Oracle go for full table scan or does it use indexes when we use start with/connect by clause? Thanks, Rao -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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: Guy Hammond 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: Does start with/connect by uses full table scan?
Thanks a lot Guy. Very good link with lot of examples. Rao -Original Message- Sent: Tuesday, September 25, 2001 6:30 AM To: Multiple recipients of list ORACLE-L According to this page, you would use concatenated indexes: http://www.arsdigita.com/books/sql/trees.html Hmmm. g -Original Message- Sent: Monday, September 24, 2001 7:36 PM To: Multiple recipients of list ORACLE-L List, Does Oracle go for full table scan or does it use indexes when we use start with/connect by clause? Thanks, Rao -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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).
Does start with/connect by uses full table scan?
List, Does Oracle go for full table scan or does it use indexes when we use start with/connect by clause? Thanks, Rao -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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: Full Table Scan and TKPROF Output
Hi Ethan, If selectivity is that low, try using a bitmapped index. HTH, Remco -Oorspronkelijk bericht- Van: Post, Ethan [mailto:[EMAIL PROTECTED]] Verzonden: vrijdag 8 juni 2001 2:26 Aan: Multiple recipients of list ORACLE-L Onderwerp: Full Table Scan and TKPROF Output My theory...We are running J.D. Edwards OneWorld. OneWorld allows the CNC (code word for OneWorld admin) to configure a number of job queues that check a table (the F986110) for new jobs that need to be processed. Each of these processes and occasionally a few more update, delete and select from this table almost constantly. The SQL being executed against the table uses a WHERE clause on 5 columns which are indexed but the selectivity is really bad, only 5 distinct values out of 100+ thousand records, so it does a full table scan. A few months ago I cached the table. At the moment the table is 100 MB and only has 30 MB of data. I will reorg it the next time we get some down time. The trouble is that I experience a lot of buffer busy waits on these processes. Also when I ran SQLTRACE it showed an almost unbelievable number of buffers read in consistent mode, way! way! larger than the size of the table. The CPU associated with these processes runs around 10% each so we are at 50% CPU even when the system is dead. Luckily they seem to take a low priority and the % CPU drops when the job kicks off, this may be because the queue is waiting on the job. My guess why CPU is 10% is that the CPU is reading all the blocks in memory a bazillion times. I can't find anything about this on the J.D. Edwards Knowledge Garden. By the way CPU time is really high also. This is a huge performance problem for OneWorld. My proposed official duct tape solution is to make the table much smaller by moving the records into another table after they are more than N days old. At the moment we clean up after 90 days but I think there would be a terrific gain if we reduce it to 7 days or so, (some of this is for the benefit of folks on the JDELIST, sorry I'm gonna cross-post). Am I missing anything? Are there any other solutions to this dilemma? Thanks, Ethan Post -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Daemen, Remco 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).
Full Table Scan and TKPROF Output
My theory...We are running J.D. Edwards OneWorld. OneWorld allows the CNC (code word for OneWorld admin) to configure a number of job queues that check a table (the F986110) for new jobs that need to be processed. Each of these processes and occasionally a few more update, delete and select from this table almost constantly. The SQL being executed against the table uses a WHERE clause on 5 columns which are indexed but the selectivity is really bad, only 5 distinct values out of 100+ thousand records, so it does a full table scan. A few months ago I cached the table. At the moment the table is 100 MB and only has 30 MB of data. I will reorg it the next time we get some down time. The trouble is that I experience a lot of buffer busy waits on these processes. Also when I ran SQLTRACE it showed an almost unbelievable number of buffers read in consistent mode, way! way! larger than the size of the table. The CPU associated with these processes runs around 10% each so we are at 50% CPU even when the system is dead. Luckily they seem to take a low priority and the % CPU drops when the job kicks off, this may be because the queue is waiting on the job. My guess why CPU is 10% is that the CPU is reading all the blocks in memory a bazillion times. I can't find anything about this on the J.D. Edwards Knowledge Garden. By the way CPU time is really high also. This is a huge performance problem for OneWorld. My proposed official duct tape solution is to make the table much smaller by moving the records into another table after they are more than N days old. At the moment we clean up after 90 days but I think there would be a terrific gain if we reduce it to 7 days or so, (some of this is for the benefit of folks on the JDELIST, sorry I'm gonna cross-post). Am I missing anything? Are there any other solutions to this dilemma? Thanks, Ethan Post -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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).
