Log-In to post
Range delimiting (Index Manager) predicates are used to bracket an index scan or provide start and stop keys
for the index search. Index Sargable predicates are not used to bracket a search, but are evaluated from the
index. Columns involved in the predicates are part of the index and evaluated by Index Manager.
Data sargable predicates cannot be evaluated by index manager and are evaluated by Data Management Services.
They typically require data access. DMS will retrieve the columns to evaluate the predicate as well as any
others to satisfy columns in the select list that could not be obtained via an index.
Residual predicates require I/O beyond just accessing the base table such as quantified subqueries - ANY, ALL,
IN, SOME, Long VARCHAR, LOBS, etc.
Residual predicates are evaluated by RDS and are the most costly of the 4 categories of predicates.
When creating indexes, consider using PCTFREE in the index specification to avoid index page splits. The
default is 10%. If more than 10% is specified, only 10% free will be provided for NON-LEAF pages.