Get the tools you need to learn Java skills fast!
Video tutorials, eBooks, hands-on lab exercises, sample code.
Get started
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 Badass: Making Users Awesome this week in the Game Development 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: 86


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

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

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]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
I agree. Here's the link:
subject: EXPLAIN PLAN FOR in oracle