The Index We Were Looking For

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)