An execution plan defines how Oracle finds or writes the data For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan. SQL statement EXPLAIN PLAN to determines this. The general syntax of EXPLAIN PLAN is:
explain plan for sql-statement; If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:
explain plan into table_name for sql-statement;
If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.
The Plan Table
The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.
Option tells more about how an operation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others.
Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.
explain plan into table_name for sql-statement;
If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.
The Plan Table
The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.
Option tells more about how an operation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others.
Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.
FULL - means that the entire table is accessed.
sql*plus automatically explains the plan for you if autotrace is enabled.
INDEX (RANGE SCAN) - basically means, that the index was used, but that it can return more than one row
INDEX (UNIQUE SCAN) - means, that this index is used, and it sort of guarantees that this index returnes exactly one rowid
NESTED LOOPS - For each relevant row in the first table (driving table), find all matching rows in the other table (probed table).
SORT (AGGREGATE) - Whenever a result set must be sorted, the operation is sort. If this sort is used to return a single row (for example max or min) the options is AGGREGATE
