sqlplus - How can I see the SQL execution plan in Oracle?

Question

I'm learning about database indexes right now, and I'm trying to understand the efficiency of using them.

• I'd like to see whether a specific query uses an index.
• I want to actually see the difference between executing the query using an index and without using the index (so I want to see the execution plan for my query).

I am using sql+.

How do I see the execution plan and where can I found in it the information telling me whether my index was used or not?

Answer 1

Try using this code to first explain and then see the plan:

Explain the plan:

explain plan 
for 
select * from table_name where ...;

See the plan:

select * from table(dbms_xplan.display);

Edit: Removed the brackets