An Index That Does Something

cry-face.sql

select FACULTY.FirstName, FACULTY.LastName, STUDENT.FirstName, STUDENT.LastName
  from FACULTY, STUDENT
where FACULTY.FacultyID = STUDENT.AcademicAdvisorID and FACULTY.LastName = 'Leto';

----

Plan hash value: 1096367573
 
----------------------------------------------------------------------------------------
| 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 |   NESTED LOOPS               |         |     5 |    74 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | STUDENT |     5 |   185 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | FAC_PK  |     1 |       |     0   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| FACULTY |     1 |    37 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("FACULTY"."FACULTYID"="STUDENT"."ACADEMICADVISORID")
   5 - filter("FACULTY"."LASTNAME"='Leto')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

----

create index last_name on FACULTY(LastName);

select FACULTY.FirstName, FACULTY.LastName, STUDENT.FirstName, STUDENT.LastName
  from FACULTY, STUDENT
where FACULTY.FacultyID = STUDENT.AcademicAdvisorID and FACULTY.LastName = 'Leto';

----

Plan hash value: 1605570460
 
---------------------------------------------------------------------------------------------------
| 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 |   NESTED LOOPS                        |           |     1 |    74 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| FACULTY   |     1 |    37 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | LAST_NAME |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                   | ADVISOR_I |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | STUDENT   |     1 |    37 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("FACULTY"."LASTNAME"='Leto')
   5 - access("FACULTY"."FACULTYID"="STUDENT"."ACADEMICADVISORID")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan