Table Access Operations
TABLE ACCESS FULL
The Oracle server reads all blocks
up to the high-water mark (HWM).
TABLE ACCESS BY ROWID
The Oracle server gets blocks that are identified
by an index lookup, or by a specified ROWID.
Index Access Operations
INDEX UNIQUE SCAN
– Returns a single key value
– Only if index is unique, for equality searches
INDEX RANGE SCAN
– Lookups that can return more than one row
INDEX FULL SCAN
– Retrieves all values
INDEX FAST FULL SCAN
– Scans all index blocks; can be parallel
INDEX SKIP SCAN
– Splits a composite index logically into smaller subindexes
and scans only the subindex of interest to the query
Join Methods
SORT-MERGE JOIN
– Sorts tables on the join key and then merges them
together
– Sorts are expensive
NESTED LOOPS
– Retrieves a row from one table and finds the
corresponding rows in the other table
– Usually best for small numbers of rows
HASH JOIN
– Build hash table in memory for smaller row source
– Hash larger row source
– Probe in-memory hash table for matches
– Hash joins are only considered by the CBO