Bruce, here is what my oracle god said to do what you wanted via oracle..mike Yes, it can be done. You have to run what is called an Explain Plan. It shows how a query accesses a table.
NOTE 1: BEFORE YOU GET TOO FAR INTO DOING IT MANUALLY --- USE TOAD to run my Explain Plans. It is fast and easy. Just enter the query in question in the top frame and highlight the Query to be run and click on the Ambulance. – It reports the results in the bottom frame. Note 2: Whether using TOAD or doing it Manually, you need a PLAN TABLE created first (Step 1 below under important) – See the Web page on how to create a plan table. OR DO IT MANULLY as outline below (and in the Web Page listed here) http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/ex_plan.htm This link is for 9i but you do the same thing for 10g Important: 1. You have to define a table to hold the results. There is a Stored Procedure that will do that (see web page). You only have t o create the table one time and it will be used for all explain plans after that. 2. You then have to bracket the query with the special explain plan statements and execute the explain plan. The results will be placed in the explain plan table. 3.You then have to write a query to READ the results of the explain plan or use the predefined one in the library. Enter the following to display the EXPLAIN PLAN output: @?/RDBMS/ADMIN/UTLXPLS Oracle displays something similar to the following: Plan Table ------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart | Pstop| ------------------------------------------------------------------------------- | SELECT STATEMENT | | 105 | 8K| 1 | | | | PARTITION RANGE ALL | | | | | 1 | 5 | | TABLE ACCESS FULL |EMP_RANGE | 105 | 8K| 1 | 1 | 5 | ------------------------------------------------------------------------------- 6 rows selected. OR Write your own like: · SELECT cardinality "Rows",· lpad(' ',level-1)||operation||' '||· options||' '||object_name "Plan"· FROM PLAN_TABLE Oracle displays something similar to the following:· Rows Plan· ------- ----------------------------------------· 16957 SELECT STATEMENT· 16957 TABLE ACCESS FULL EMPLOYEES Or another example of an output show Plan---------------------------------------------SELECT STATEMENT TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F INDEX RANGE SCAN PER_PEOPLE_F_N54 Showing how it used an index (if it did) > Date: Fri, 26 Jun 2009 12:12:15 -0400 > From: [email protected] > Subject: Testing for Table Scan > To: [email protected] > > 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"
