Chapter 79. Indexes

Can be unique or non-unique
Can be simple or composite
Can be normal (ascending) or descending
Can be reverse key (for monotonically incrementing column values) to balance B*-tree
Can be function-based (to address normalization uses)
Can be used to implement sort for "order by"
Can be used to implement the entire table (Index-organized Table(IOT))
Can be traversed in different ways
- Unique Scan - used with "unique" or "primary key" indexes to return a single value
- Range Scan - used with "unique" and "non-unique" indexes to return multiple matching rows
- Full Scan - used with composite indexes where leading where column is not part of index (i.e., can use col2 of composite)
- Fast Full Scan - used when all columns of query (where and select) are contained within composite index -- table is skipped
- ...
Can be coalesced or rebuilt with
```
ALTER INDEX (index-name) (COALESCE | REBUILD)
```
- Coalesce - repairs index in place. Good for small repairs
- Rebuild - totally rebuilds index. Good for large repairs

79.1. Index Range Scan

Request N rows matching a specific criteria using a non-unique index

Figure 79.1. DB Index(es)

alter table jpatune_movie add utitle varchar2(256)
update jpatune_movie set utitle=concat(concat(concat(title,'('),id),')')
create index movie_utitle_idx on jpatune_movie(utitle)

* "utitle" was added as the concatenation of title(id) to create a unique column.

** we are not yet taking advantage of the uniqueness of the column

Figure 79.2. JPA Query

"select m from Movie m 
where m.title = :title", 
params={title=Tremors(m836199)}

Figure 79.3. Generated SQL

select
    movie0_.ID as ID1_2_,
    movie0_.DIRECTOR_ID as DIRECTOR7_2_,
    movie0_.MINUTES as MINUTES2_2_,
    movie0_.PLOT as PLOT3_2_,
    movie0_.RATING as RATING4_2_,
    movie0_.RELEASE_DATE as RELEASE5_2_,
    movie0_.utitle as utitle6_2_ 
from
    JPATUNE_MOVIE movie0_ 
where
    movie0_.utitle=?

Figure 79.4. Explain Plan

Matching row located using index
Since index is non-unique multiple entries must be scanned

Figure 79.5. Relative Test Result

Non-Unique Index.Values By Index:warmups=2, rounds=11, ave=0.17 [+- 0.01]

79.2. Unique Index Scan

Request N rows matching a specific criteria using a unique index

Figure 79.6. DB Index(es)

create unique index movie_utitle_udx on jpatune_movie(utitle)

* we are now taking advantage of the unique column values.

Figure 79.7. JPA Query

"select m from Movie m 
where m.title = :title", 
params={title=Tremors(m836199)}

Figure 79.8. Generated SQL

select
    movie0_.ID as ID1_2_,
    movie0_.DIRECTOR_ID as DIRECTOR7_2_,
    movie0_.MINUTES as MINUTES2_2_,
    movie0_.PLOT as PLOT3_2_,
    movie0_.RATING as RATING4_2_,
    movie0_.RELEASE_DATE as RELEASE5_2_,
    movie0_.utitle as utitle6_2_ 
from
    JPATUNE_MOVIE movie0_ 
where
    movie0_.utitle=?

Figure 79.9. Explain Plan

Index used to locate table rowId
Unique index scan used since index contains unique values

Figure 79.10. Relative Test Result

Nullable Unique Index.Values By Index:warmups=2, rounds=10, ave=0.18 [+- 0.01]

* the measurements in this test do not prove the following assertion which was based on the execution plan.

Unique Indexes can be more Efficient than Non-Unique Indexes

DB knows there can be only a single match and stops index scan after first match found

79.3. Composite Index

Indexes with multiple columns to match the where clause and optionally the select and join clauses as well.

79.3.1. Query Parts

Selecting N columns for M rows matching two (2) criteria terms

Figure 79.11. JPA Query

"select m from Movie m 
where m.title = :title and m.releaseDate = :releaseDate", 
params={title=Apollo 13, releaseDate=1995-07-01}

Figure 79.12. Generated SQL

select
    movie0_.ID as ID1_2_,
    movie0_.DIRECTOR_ID as DIRECTOR7_2_,
    movie0_.MINUTES as MINUTES2_2_,
    movie0_.PLOT as PLOT3_2_,
    movie0_.RATING as RATING4_2_,
    movie0_.RELEASE_DATE as RELEASE5_2_,
    movie0_.TITLE as TITLE6_2_ 
from
    JPATUNE_MOVIE movie0_ 
where
    movie0_.TITLE=? 
    and movie0_.RELEASE_DATE=?

79.3.2. First Term Indexed

First term of a two term where clause indexed

Figure 79.13. DB Index(es)

create index movie_title_idx on jpatune_movie(title)

Figure 79.14. Explain Plan

Candidate rows located using a range scan of the index on first condition
Final rows filtered from candidate rows using second condition

Figure 79.15. Relative Test Result

Dual Indexes.Get By Term1 and Term2:warmups=2, rounds=11, ave=0.21 [+- 0.01]

Most Selective Column Should be Indexed

DB will re-order the query to try to take advantage of an index that will result in the least number of rows moving forward to next step.

79.3.3. First and Second Term Indexed (using Composite Index)

All terms in the where clause part of same composite index

Figure 79.16. DB Index(es)

create index movie_title_rdate_idx on jpatune_movie(title, release_date)

Figure 79.17. Explain Plan

Rows located using range scan of the composite index, applying the two conditions
Select clause satisfied using table rows accessed by rowId

Figure 79.18. Relative Test Result

Compound Index.Get By Term1 and Term2:warmups=2, rounds=11, ave=0.25 [+- 0.02]

Composite Indexes have Minimal Performance Improvement

Adding all terms from the where clause may add some efficiency over a single term index -- but not a significant upgrade from a single index when the contents of the row must be returned anyway.

79.4. Index Fast Full Scan (with Composite Index)

Incorporate terms from select clause into composite index to bypass table access.

79.4.1. Query Parts

Selecting N columns from M rows matching Q criteria terms

Figure 79.19. JPA Query

"select m.rating from Movie m 
where m.title like :title", 
params={title=A%}, limit=2000

Figure 79.20. Generated SQL

select * from
    ( select
        movie0_.RATING as col_0_0_ 
    from
        JPATUNE_MOVIE movie0_ 
    where
        movie0_.TITLE like ? ) 
where
    rownum <= ?

79.4.2. Option: Use Range Scan and RowId Access

Figure 79.21. DB Index(es)

create index movie_title_idx on jpatune_movie(title)

Figure 79.22. Explain Plan

Where clause satisfied using an index range scan
Select clause satisfied using table access by rowId from index

Figure 79.23. Relative Test Result

Where Column Index.Query for Values:warmups=2, rounds=11, ave=1.25 [+- 0.06]

79.4.3. Option: Use Range Scan Alone with Composite Index

Figure 79.24. DB Index(es)

create index movie_title_rating_idx on jpatune_movie(title, rating)

* this index contains columns from both the where and select clauses

Figure 79.25. Explain Plan

Where clause satisfied using an index range scan
Select clause satisfied using same index since composite index also contains all necessary columns

Figure 79.26. Relative Test Result

Where, Select Index.Query for Values:warmups=2, rounds=11, ave=1.26 [+- 0.06]

Add Columns from Select to Composite Index to Bypass Table Access

DB will bypass rowId access to row if index already contains all columns necessary to satisfy the select clause.

79.4.4. Option: Fast Full Scan

DB can make use of secondary terms within composite index.

Figure 79.27. DB Index(es)

create index movie_rating_title_idx on jpatune_movie(rating, title)

* this index is the mirror image of previous example and contains columns from both the select and where clauses

Figure 79.28. Explain Plan

Index did not start with terms from where clause, but usable
Where clause satisfied using a fast full scan of composite index
Select clause satisfied using same index since composite index also contains all necessary columns

Figure 79.29. Relative Test Result

Select, Where Index.Query for Values:warmups=2, rounds=11, ave=1.35 [+- 0.04]

DB Can make use of Secondary Columns of Composite Index

Consider leveraging secondary columns of an existing composite index (over adding another index) to satisfy low-priority queries.

79.5. Summary

Indexes speed access to specific rows
Indexes should match where clause of high priority queries
Composite indexes can be formed with multiple terms from the where and select clause
Secondary columns of composite indexes can be used (over creating additional index) in lower priority queries
Unique indexes can be searched faster than non-unique indexes -- take advantage of unique column values