Chapter 3. Table Access

Access paths - strategies used to access data within table
Indexes - good for when accessing small amounts of data out of much larger data set
Full table scans - good for small tables and unrestricted row access
Any row can be *functionally* accessed with either method
Use execution plans to help use the appropriate technique

3.1. Full Table Scan

Database engine derives all information from the table directly from the physical storage location for the table. Table storage is not arranged in any guaranteed order.

3.1.1. Full Table Scan: Unconstrained Access

Request N rows of data from a table with no defined order

Full Table Scan selected when:
- There is no where clause
- There is no suitable index
Full Table Scan optimal when:
- Using small tables
- Majority of the rows in a larger table must be read (e.g., no where clause)

Figure 3.1. JPA Query

"select m from Movie m", limit=1000

Figure 3.2. Generated SQL

select * from 
    ( 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
    rownum <= ?

Figure 3.3. Explain Plan

DB scans table as a part of getting data to resolve select clause
No added cost for where or order-by clauses

Figure 3.4. Relative Test Result

Table Access without Index.Unrestricted Scan:warmups=2, rounds=11, ave=0.73 [+- 0.03]

No need for an Index for Unconstrained Access to a Table

Since the DB's job in this case is to obtain data from all rows of the table in any order -- an index would not add value.

3.1.2. Full Table Scan: Using Where (without Index)

Request N rows of data matching a condition on an un-indexed column

Figure 3.5. JPA Query

"select m from Movie m
where m.rating = :rating", 
params={rating=R}, limit=1000

Figure 3.6. Generated SQL

select * from 
    ( 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_.RATING=? ) 
where
    rownum <= ?

Figure 3.7. Explain Plan

Where constraint adds cost to unconstrained query
DB must scan the rows in the table for a match to query

Figure 3.8. Relative Test Result

Table Access without Index.Value Access:warmups=2, rounds=11, ave=0.80 [+- 0.04]

Where Clauses without a Matching Index results in a Full Table Scan

Full table scans are the most inefficient way to obtain certain rows from a large DB table.

3.1.3. RowId Scan: Using Where (with Index)

Request N rows of data matching a condition on an indexed column

Uses an exact location for a row -- normally obtained from an index
Fastest way to locate an individual row in a table
Note: rows can be moved during an update -- requiring multiple locations to be accessed when using former rowId

Figure 3.9. DB Index(es)

create index movie_rating_idx on jpatune_movie(rating)

Figure 3.10. JPA Query

"select m from Movie m 
where m.rating = :rating", 
params={rating=R}, limit=1000

Figure 3.11. Generated SQL

select * from 
    ( 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_.RATING=? ) 
where
    rownum <= ?

Figure 3.12. Explain Plan

Index on where clause search term avoids scanning table -- index scanned instead
DB row is accessed to satisfy select clause
DB row is located by rowId from index

Figure 3.13. Relative Test Result

Table Access with Single Indexes.Value Access:warmups=2, rounds=11, ave=0.75 [+- 0.03]

Match Indexes for Where Clauses Selecting Small Row Subset from Table

An index will speed access to rows within a table when selecting a small subset of a larger table.

3.1.4. Full Table Scan: Invalidating Index using Function applied to Row Column

Request N rows of data matching a condition on an indexed column invalidated by a function

Figure 3.14. DB Index(es)

create index movie_rating_idx on jpatune_movie(rating)

Figure 3.15. JPA Query

"select m from Movie m 
where upper(m.rating) = :rating", 
params={rating=R}, limit=1000

Figure 3.16. Generated SQL

select * from 
    ( 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
        upper(movie0_.RATING)=? ) 
where
    rownum <= ?

Figure 3.17. Explain Plan

Applying function to column indexed by value invalidates use of index
DB reverts to full table scan to locate row

Figure 3.18. Relative Test Result

Table Access with Single Indexes.Unindexed Function Access:warmups=2, rounds=11, ave=0.79 [+- 0.04]

Avoid using Functions on DB Rows within Where Clause

Calling a function on a DB column within the where clause will invalidate the use of an index on that column -- unless it is a function-based index (matching what is being used in the where)

3.1.5. RowId Scan: Using Index with Function applied to Row Column

Request N rows of data matching a functional condition on a function-indexed column

Figure 3.19. DB Index(es)

create index movie_rating_lower_idx on jpatune_movie(lower(rating))

Figure 3.20. JPA Query

"select m from Movie m 
where lower(m.rating) = :rating", 
params={rating=r}, limit=1000

Figure 3.21. Generated SQL

select * from 
    ( 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
        lower(movie0_.RATING)=? ) 
where
    rownum <= ?

Figure 3.22. Explain Plan

Where clause satisfied by scanning function index
DB row accessed to satisfy select clause
DB row accessed by RowId
COST is same order of magnitude (but )as non-function index

Figure 3.23. Relative Test Result

Table Access with Single Indexes.Indexed Function Access:warmups=2, rounds=11, ave=0.76 [+- 0.04]

Define Function Index where Calling Functions on DB rows cannot be Avoided

The DB will store the pre-calculated value in the index for use during the query.

3.1.6. Full Table Scan: Invalidating Index by using Leading Wildcards

Request N rows of data matching a like condition containing a leading wildcard

Figure 3.24. DB Index(es)

create index movie_title_idx on jpatune_movie(title)

Figure 3.25. JPA Query

"select m from Movie m 
where m.title like :title", 
params={title=%eventeen: The Faces for Fall}, limit=1000

Figure 3.26. Generated SQL

select * from
    ( 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 like ? ) 
where
    rownum <= ?

Figure 3.27. Explain Plan

Row located using full table scan
Leading wildcard in like invalidates use of indexed column

Figure 3.28. Relative Test Result

Table Access with Single Indexes.Ending Wildcard:warmups=2, rounds=11, ave=0.22 [+- 0.04]
Table Access with Single Indexes.Exact Equals:warmups=2, rounds=11, ave=0.20 [+- 0.02]
Table Access with Single Indexes.Exact Like:warmups=2, rounds=11, ave=0.22 [+- 0.05]
Table Access with Single Indexes.Leading Wildcard:warmups=2, rounds=11, ave=0.66 [+- 0.05]

Avoid Leading Wildcards when using Like

Leading wildcards invalidate the use of column indexes. Indexes are still used with non-leading wildcards.

3.2. Order By

Order returned results by one or more columns

3.2.1. Order By using Sort

Request N rows of data ordered by (ASC) a column

Figure 3.29. DB Index(es)

create index movie_title_idx on jpatune_movie(title)

Figure 3.30. JPA Query

"select m from Movie m 
order by title ASC", 
limit=1000

Figure 3.31. Generated SQL

select * from 
    ( 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_ 
    order by
        movie0_.TITLE ASC ) 
where
    rownum <= ?

Figure 3.32. Explain Plan

DB table is scanned as before when unconstrained by where clause
Output is sorted at a noticeable cost increase

Figure 3.33. Relative Test Result

Table Access with Single Indexes.OrderBy Non-Null Column:warmups=2, rounds=11, ave=0.76 [+- 0.03]

Sorting adds Cost

Look to reduce the number of rows required to be sorted or to use an index (see next topic).

3.2.2. Order By using Index

Request N rows of data ordered by (ASC) indexed column (ASC) from where

Figure 3.34. DB Index(es)

create index movie_title_idx on jpatune_movie(title)

Figure 3.35. JPA Query

"select m from Movie m 
where m.title like :title 
order by title ASC", 
params={title=A%}, limit=1000

Figure 3.36. Generated SQL

select * from 
    ( 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 like ? 
    order by
        movie0_.TITLE ASC ) 
where
    rownum <= ?

Figure 3.37. Explain Plan

Range scan of index is used to satisfy where clause
Order of index used to satisfy order by clause
Cost significantly less that sort

Figure 3.38. Relative Test Result

Table Access with Single Indexes.OrderBy Non-null Where Column ASC:warmups=2, rounds=11, ave=0.69 [+- 0.02]

Index-based Sorts are more Efficient

It is more efficient to sort results by the indexed values in the where clause because the index is already maintained in sort order.

3.2.3. Order By using Index DESC

Request N rows of data ordered by (DESC) indexed column (ASC) from where

Figure 3.39. DB Index(es)

create index movie_title_idx on jpatune_movie(title)

Figure 3.40. JPA Query

"select m from Movie m 
where m.title like :title 
order by title DESC", 
params={title=A%}, limit=1000

Figure 3.41. Generated SQL

select * from 
    ( 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 like ? 
    order by
        movie0_.TITLE DESC ) 
where
    rownum <= ?

Figure 3.42. Explain Plan

Normal (ASC) indexes can be efficiently traversed in DESC order

Figure 3.43. Relative Test Result

Table Access with Single Indexes.OrderBy Non-null Where Column DESC:warmups=2, rounds=11, ave=0.72 [+- 0.04]

3.2.4. Order By using Reverse Index DESC

Request N rows of data ordered by (DESC) indexed column (DESC) from where

Figure 3.44. DB Index(es)

create index movie_title_ridx on jpatune_movie(title desc)

Figure 3.45. JPA Query

"select m from Movie m 
where m.title like :title 
order by title DESC", 
params={title=A%}, limit=1000

Figure 3.46. Generated SQL

select * from 
    ( 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 like ? 
    order by
        movie0_.TITLE DESC ) 
where
    rownum <= ?

Figure 3.47. Explain Plan

DESC index implementedas a function index

Function indexes are not handled as efficiently as normal indexes ["...cardinality estimates are not as accurate and the SYS_OP_DESCEND and SYS_OP_UNDESCEND functions are used as access/filter conditions as they’re the functions implemented in the function-based index" http://richardfoote.wordpress.com/category/descending-indexes/]

Figure 3.48. Relative Test Result

Table Access with Single Indexes DESC.OrderBy Non-null Where Column DESC:warmups=2, rounds=11, ave=0.99 [+- 0.03]

Choose Ascending Indexes (over Descending) for Single Columns

Ascending indexes can be efficiently traversed in ascending and descending order.

3.3. Summary

Full table scans efficient when required to access most rows
Indexes access more efficient when selecting small subset of table
Indexes can be invalidated - resulting in full table scans
Indexes can be used to bypass expensive sort operations. Either:
1. Column must be non-nullable
2. Where clause excludes null values