Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

EXPLAIN PLAN FOR in oracle

 
jaya kemmannu
Ranch Hand
Posts: 89
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34680
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic