Home » SQL & PL/SQL » SQL & PL/SQL » Reading explain plans
icon7.gif  Reading explain plans [message #350940] Sat, 27 September 2008 22:29 Go to next message
jgreenleaf
Messages: 9
Registered: September 2008
Junior Member
Hey everybody,

I am studying sql optimization and as part of my studies I have tried to write a join, subquery, and correlated subquery that all return the same information in hopes of executing each one and trying to find out which one runs most efficiently. However I don't really know how to tell which one runs more efficiently and was hoping someone could explain which of these statements performs best and why. Any input is greatly appreciated!!

Here is the join query:

SELECT DISTINCT s.student_id
FROM student s JOIN enrollment e
ON (s.student_id = e.student_id)
JOIN grade g
ON (s.student_id = g.student_id)
JOIN zipcode z
ON (s.zip = z.zip)
WHERE g.numeric_grade = 94;

Explain plan:

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91 | 2366 | 12 (17)|
| 1 | HASH UNIQUE | | 91 | 2366 | 12 (17)|
| 2 | NESTED LOOPS | | 91 | 2366 | 11 (10)|
| 3 | NESTED LOOPS | | 67 | 1541 | 10 (10)|
|* 4 | HASH JOIN | | 67 | 1139 | 10 (10)|
|* 5 | TABLE ACCESS FULL| GRADE | 67 | 469 | 6 (0)|
| 6 | TABLE ACCESS FULL| STUDENT | 268 | 2680 | 3 (0)|
|* 7 | INDEX UNIQUE SCAN | ZIP_PK | 1 | 6 | 0 (0)|
|* 8 | INDEX RANGE SCAN | ENR_PK | 1 | 3 | 1 (0)|
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("S"."STUDENT_ID"="G"."STUDENT_ID")
5 - filter("G"."NUMERIC_GRADE"=94)
7 - access("S"."ZIP"="Z"."ZIP")
8 - access("E"."STUDENT_ID"="S"."STUDENT_ID")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
367 bytes sent via SQL*Net to client
334 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed


The subquery:

SELECT DISTINCT student_id
FROM student
WHERE student_id IN
(SELECT student_id
FROM enrollment
WHERE zip IN
(SELECT zip FROM zipcode
WHERE student_id IN
(SELECT student_id FROM grade
WHERE numeric_grade = 94)));

Explain plan:

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91 | 2366 | 12 (17)|
| 1 | HASH UNIQUE | | 91 | 2366 | 12 (17)|
| 2 | NESTED LOOPS | | 91 | 2366 | 11 (10)|
|* 3 | HASH JOIN | | 91 | 1820 | 11 (10)|
| 4 | NESTED LOOPS | | 91 | 910 | 7 (0)|
|* 5 | TABLE ACCESS FULL| GRADE | 67 | 469 | 6 (0)|
|* 6 | INDEX RANGE SCAN | ENR_PK | 1 | 3 | 1 (0)|
| 7 | TABLE ACCESS FULL | STUDENT | 268 | 2680 | 3 (0)|
|* 8 | INDEX UNIQUE SCAN | ZIP_PK | 1 | 6 | 0 (0)|
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("STUDENT_ID"="STUDENT_ID")
5 - filter("NUMERIC_GRADE"=94)
6 - access("STUDENT_ID"="STUDENT_ID")
8 - access("ZIP"="ZIP")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
367 bytes sent via SQL*Net to client
334 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed

The correlated subquery:

SELECT s.student_id
FROM student s JOIN zipcode z
ON s.zip = z.zip
WHERE EXISTS
(SELECT 'X'
FROM grade g JOIN enrollment e
ON g.student_id = e.student_id
WHERE g.student_id = s.student_id AND g.numeric_grade = 94);

Explain plan:

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91 | 2093 | 11 (10)|
|* 1 | HASH JOIN SEMI | | 91 | 2093 | 11 (10)|
| 2 | TABLE ACCESS FULL | STUDENT | 268 | 2680 | 3 (0)|
| 3 | VIEW | VW_SQ_1 | 91 | 1183 | 7 (0)|
| 4 | NESTED LOOPS | | 91 | 910 | 7 (0)|
|* 5 | TABLE ACCESS FULL| GRADE | 67 | 469 | 6 (0)|
|* 6 | INDEX RANGE SCAN | ENR_PK | 1 | 3 | 1 (0)|
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("STUDENT_ID"="S"."STUDENT_ID")
5 - filter("G"."NUMERIC_GRADE"=94)
6 - access("G"."STUDENT_ID"="E"."STUDENT_ID")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
367 bytes sent via SQL*Net to client
334 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

I'm sorry there is so much information here but if anyone can give me some insight I would really appreciate it! Thank you!

[Updated on: Sat, 27 September 2008 22:39]

Report message to a moderator

Re: Reading explain plans [message #350945 is a reply to message #350940] Sun, 28 September 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Indent the code (See SQL Formatter) and align the columns in explain plan.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Reading explain plans [message #350977 is a reply to message #350940] Sun, 28 September 2008 12:33 Go to previous messageGo to next message
jgreenleaf
Messages: 9
Registered: September 2008
Junior Member
My apologies Michel! I will read the guides. I would take the information on the first post down but I don't seem to be able to "edit" that post any longer?

[Updated on: Sun, 28 September 2008 12:34]

Report message to a moderator

Re: Reading explain plans [message #351041 is a reply to message #350977] Mon, 29 September 2008 01:43 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Explain Plan in simple terms tell you how oracle will execute the query.

Please have a look at this:-

Explain Plan

Regards,
Rajat
Re: Reading explain plans [message #351063 is a reply to message #351041] Mon, 29 September 2008 02:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This article might help too.

Ross Leishman
icon14.gif  Re: Reading explain plans [message #351183 is a reply to message #350940] Mon, 29 September 2008 13:05 Go to previous message
jgreenleaf
Messages: 9
Registered: September 2008
Junior Member
Thank you for the references, they were both very useful!
Previous Topic: How to achieve this ?
Next Topic: MIN fUNCTION WITH GROUP BY
Goto Forum:
  


Current Time: Sat Dec 10 15:01:03 CST 2016

Total time taken to generate the page: 0.16692 seconds