Skip to content

Instantly share code, notes, and snippets.

@cmiles74

cmiles74/smiley-face.sql

Created November 30, 2017 21:19
Show Gist options
  • Save cmiles74/9dd346c9339887abd2e32ce82e040d40 to your computer and use it in GitHub Desktop.
Save cmiles74/9dd346c9339887abd2e32ce82e040d40 to your computer and use it in GitHub Desktop.
Download ZIP
The Index We Were Looking For
Raw
smiley-face.sql
select
STUDENT.FirstName || ' ' || STUDENT.LastName AS student,
FACULTY.FirstName || ' ' || FACULTY.LastName AS faculty
FROM STUDENT
JOIN FACULTY ON STUDENT.AcademicAdvisorID = FACULTY.FacultyID
WHERE FACULTY.FacultyID = 437143;
----
Plan hash value: 1633013143
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 74 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 74 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| FACULTY | 1 | 37 | 0 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | FAC_PK | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | STUDENT | 1 | 37 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FACULTY"."FACULTYID"=437143)
4 - filter("STUDENT"."ACADEMICADVISORID"=437143)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
----
CREATE INDEX advisor_i ON STUDENT(AcademicAdvisorID);
select
STUDENT.FirstName || ' ' || STUDENT.LastName AS student,
FACULTY.FirstName || ' ' || FACULTY.LastName AS faculty
FROM STUDENT
JOIN FACULTY ON STUDENT.AcademicAdvisorID = FACULTY.FacultyID
WHERE FACULTY.FacultyID = 437143;
----
Plan hash value: 4077473445
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 74 | 1 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 74 | 1 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | FACULTY | 1 | 37 | 0 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | FAC_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| STUDENT | 1 | 37 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | ADVISOR_I | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FACULTY"."FACULTYID"=437143)
5 - access("STUDENT"."ACADEMICADVISORID"=437143)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment