Just one caveat, though. If you're not familiar with execution plans, you need to be careful how you interpret some table scans. In some cases, the index will get the database close to the record (e.g., the correct block in the database), and then the DB will do a scan from there. That is not the same as a full table scan and not a problem. Make sure you pay close attention to whether it is using any indexes, otherwise you may think it's doing a full scan when it's really not.
Disclaimer: I haven't worked with execution plans from Oracle, but I'm guessing that the concepts are similar. Lyle -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Axton Sent: Friday, June 26, 2009 10:32 AM To: [email protected] Subject: Re: Testing for Table Scan SQL*Plus SQL> set autotrace on then run the query This will give you two sets of information: - Execution plan - Statistics The execution plan will tell you what indexes were used, what table/index ranges were scanned, etc. Axton Grams On Fri, Jun 26, 2009 at 11:12 AM, bruce sisk<[email protected]> wrote: > Hello, > > How can I test a query to see if it is using an index or performing a table > scan?? > > We are using Oracle 10g as our database. > > I am trying to optimize a query. I have looked at and applied the various > rules defined in Remedy documentation for optimizing, but would like to > test\validate that the indexes are in fact being used. > > Bruce Sisk > > ________________________________________ > PeoplePC Online > A better way to Internet > http://www.peoplepc.com > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
