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

ITPub博客

【执行计划】数据访问方式,连接方式及方法

最新文章