【執行計劃】資料訪問方式,連線方式及方法

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