- 78.1. Full Table Scan
- 78.1.1. Full Table Scan: Unconstrained Access
- 78.1.2. Full Table Scan: Using Where (without Index)
- 78.1.3. RowId Scan: Using Where (with Index)
- 78.1.4. Full Table Scan: Invalidating Index using Function applied to Row Column
- 78.1.5. RowId Scan: Using Index with Function applied to Row Column
- 78.1.6. Full Table Scan: Invalidating Index by using Leading Wildcards
- 78.2. Order By
- 78.3. Summary
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
Request N rows of data from a table with no defined order
Figure 78.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 <= ?
DB scans table as a part of getting data to resolve select clause
No added cost for where or order-by clauses
Figure 78.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.
Request N rows of data matching a condition on an un-indexed column
Figure 78.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 <= ?
Where constraint adds cost to unconstrained query
DB must scan the rows in the table for a match to query
Figure 78.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.
Request N rows of data matching a condition on an indexed column
Figure 78.10. JPA Query
"select m from Movie m
where m.rating = :rating",
params={rating=R}, limit=1000Figure 78.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 <= ?
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 78.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.
Request N rows of data matching a condition on an indexed column invalidated by a function
Figure 78.15. JPA Query
"select m from Movie m
where upper(m.rating) = :rating",
params={rating=R}, limit=1000Figure 78.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 <= ?
Applying function to column indexed by value invalidates use of index
DB reverts to full table scan to locate row
Figure 78.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)
Request N rows of data matching a functional condition on a function-indexed column
Figure 78.20. JPA Query
"select m from Movie m
where lower(m.rating) = :rating",
params={rating=r}, limit=1000Figure 78.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 <= ?
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 78.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.
Request N rows of data matching a like condition containing a leading wildcard
Figure 78.25. JPA Query
"select m from Movie m
where m.title like :title",
params={title=%eventeen: The Faces for Fall}, limit=1000Figure 78.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 <= ?
Row located using full table scan
Leading wildcard in like invalidates use of indexed column
Figure 78.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.
Order returned results by one or more columns
Request N rows of data ordered by (ASC) a column
Figure 78.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 <= ?
DB table is scanned as before when unconstrained by where clause
Output is sorted at a noticeable cost increase
Figure 78.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).
Request N rows of data ordered by (ASC) indexed column (ASC) from where
Figure 78.35. JPA Query
"select m from Movie m
where m.title like :title
order by title ASC",
params={title=A%}, limit=1000Figure 78.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 <= ?
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 78.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.
Request N rows of data ordered by (DESC) indexed column (ASC) from where
Figure 78.40. JPA Query
"select m from Movie m
where m.title like :title
order by title DESC",
params={title=A%}, limit=1000
Figure 78.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 <= ?
Normal (ASC) indexes can be efficiently traversed in DESC order
Figure 78.43. Relative Test Result
Table Access with Single Indexes.OrderBy Non-null Where Column DESC:warmups=2, rounds=11, ave=0.72 [+- 0.04]
Request N rows of data ordered by (DESC) indexed column (DESC) from where
Figure 78.45. JPA Query
"select m from Movie m
where m.title like :title
order by title DESC",
params={title=A%}, limit=1000
Figure 78.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 <= ?
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/]
Choose Ascending Indexes (over Descending) for Single Columns
Ascending indexes can be efficiently traversed in ascending and descending order.