Oracle's Explain Plan

You can use Oracle's explain plan to tune your queries by viewing how Oracle is using indexes or dividing the query. You must first build your plan table (in your schema) by issuing the following command:

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.

Now you can 'ask' Oracle how it is processing your SQL statements:

SQL> select * from dual;

D
-
X

SQL> explain plan for select * from dual;

Explained.

SQL> delete plan_table;
SQL> explain plan for select /*+ rule */ * from test_for_ep where a = 5;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql;

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |       |      |        |       |       |
|  TABLE ACCESS FULL        |TEST_FOR_ |       |      |        |       |       |
--------------------------------------------------------------------------------

SQL> 

This means that the above query produces a full table access. In the next articles I will go in more detail about Oracle's explain plan.