 Reading explain plans [message #350940] |
Sat, 27 September 2008 22:29  |
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
