This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell 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


JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "EXPLAIN PLAN FOR in oracle" Watch "EXPLAIN PLAN FOR in oracle" New topic
Author

EXPLAIN PLAN FOR in oracle

jaya kemmannu
Ranch Hand

Joined: Sep 23, 2011
Posts: 85

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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: May 26, 2003
Posts: 30580
    
154

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.


[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
 
GeeCON Prague 2014
 
subject: EXPLAIN PLAN FOR in oracle