EXPLAIN PLAN FOR in oracle

[jaya kemmannu]

Hi,

Can anyone explain use of the below query :

query : EXPLAIN PLAN FOR delete from x_table where x_column='60'; and its output is

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4285998136

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | DELETE | x_table | | | | |
|* 2 | INDEX UNIQUE SCAN| PK_x_table| 1 | 17 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

2 - access("x_column"=60)

14 rows selected.


Regards,
Joy

[Martin Vajsar]

Oracle's EXPLAIN PLAN can be used to display the query plan - steps that the database will use to execute the statement. Even though your statement is not a complicated one, you can see that the database will use an index (PK_x_table) when accessing the row you want to delete.

EXPLAIN PLAN is usually used when 'tuning' the SQL statements - making changes that would result into the statement being executed more efficiently. This is actually a vast and somewhat complicated area of expertise, but if you want to get better understanding of it, you might start with this documentation, for example.

[Jeanne Boyarsky]

As Martin noted, you can see that your query uses and index - which is good. If it were using a table scan, the explain plan would be telling you that you should look into making your query more efficient.