Indexed Select Statement

How can we find the select statement is indexed or not?

Questions by bmsrao

Editorial / Best Answer

promisinganuj  

  • Member Since Sep-2009 | Sep 13th, 2009

1. using AUTOTRACE
SQL> SET AUTOTRACE ON TRACEONLY
SELECT *
    FROM emp
 WHERE emp_name = 'ABC';

2. Using EXPLAIN PLAN
SQL> EXPLAIN PLAN for
SELECT *
    FROM emp
 WHERE emp_name = 'ABC';

The advantage of using EXPLAIN PLAN over the AUTOTRACE is that former does not require the query to be actually run. The TRACEONLY option in AUTOTRACE just supress the query ouput but the query still runs before the explain plan is displayed.

Showing Answers 1 - 4 of 4 Answers

shaukat.vadsaria

  • Sep 3rd, 2008
 

It is based on the time consumed to fetch the result set of the select statment. If the select statment is taking time to retrieve the data it mean that it is using the full table scan and no indexes or wrong indexes has been created in table

  Was this answer useful?  Yes

promisinganuj

  • Sep 13th, 2009
 

1. using AUTOTRACE
SQL> SET AUTOTRACE ON TRACEONLY
SELECT *
    FROM emp
 WHERE emp_name = 'ABC';

2. Using EXPLAIN PLAN
SQL> EXPLAIN PLAN for
SELECT *
    FROM emp
 WHERE emp_name = 'ABC';

The advantage of using EXPLAIN PLAN over the AUTOTRACE is that former does not require the query to be actually run. The TRACEONLY option in AUTOTRACE just supress the query ouput but the query still runs before the explain plan is displayed.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions