This week's giveaway is in the Spring forum.
We're giving away four copies of REST with Spring (video course) and have Eugen Paraschiv on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes EXPLAIN PLAN FOR in oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "EXPLAIN PLAN FOR in oracle" Watch "EXPLAIN PLAN FOR in oracle" New topic


jaya kemmannu
Ranch Hand

Joined: Sep 23, 2011
Posts: 87


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 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):


2 - access("x_column"=60)

14 rows selected.

Martin Vajsar

Joined: Aug 22, 2010
Posts: 3728

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

Joined: May 26, 2003
Posts: 32818

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Don't get me started about those stupid light bulbs.
subject: EXPLAIN PLAN FOR in oracle
jQuery in Action, 3rd edition