SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 222

Once the change recommended by the optimizer has been validated, the profile can be saved without making
any direct changes to the application code. So in spite of the query using a NOPARALLEL hint, the tuning optimizer will
ignore this and use the execution plan saved as a query profile for future execution:

EXECUTE DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
TASK_NAME => 'RACPTbook_workshop_chapter7',
TASK_OWNER=> 'RAPDWH',
REPLACE=> TRUE,
PROFILE_TYPE => DBMS_SQLTUNE.PX_PROFILE);

Table 7-2 lists out the common type of access paths used by SQL statements and explains why such access paths
are used by Oracle to get to the data sets.

Table 7-2. Access Paths 3

Access Path Explanation

Full table scan Reads all rows from table and filters out those that do not meet the where clause

predicates. Used when there are no indexes, DOP set, and so forth.

Table access by rowid Rowid specifies the data file and data block containing the row and the location of the

row in that block. Used if rowid is supplied by the index or in WHERE clause.

Index unique scan Only one row will be returned. Used when the statement contains a unique or a primary

key constraint that guarantees that only a single row is accessed.

Accesses adjacent index entries returns rowid values. Used with equality on nonunique
indexes or range predicate on unique indexes.

Index range scan

Index skip scan Skips the leading edge of the index and uses the rest. Advantageous if there are few

distinct values in the leading column and many distinct values in the nonleading
column.

Full index scan Processes all leaf blocks of an index but only enough branch blocks to find the 1st leaf

block. Used when all necessary columns are in an index and order by clause matches
index structure or if sort merge join is done.

Fast full index scan Scans all blocks in an index; used to replace a full table scan when all necessary columns

are in the index.

Index joins Hash joins of several indexes that together contain all the table columns that are

referenced in the query.

Bitmap indexes Uses a bitmap for key values and a mapping function that converts each bit position to

a rowid. Can efficiently merge indexes that correspond to several conditions in a WHERE
clause.

3 Source: Oracle Corporation.