Access path a re ways in which data is retrived from the database. The query optimizer chooses an access path based on the following factors:
- The available access paths for the statement
- The estimated cost of executing the statement, using each access path.
The optimizer first determines which access paths are available by examining the conditions in the statement's WHERE clause and its FROM clause. The optimizer then generates a set of possible execution plans using available access paths and estimates the cost of each plan, using the statistics for the index, columns, and tables accessible to the statement. Finally, the optimizer chooses the execution plan with the lowest estimated cost.
When choosing an access path, the query optimizer is influenced by the following factors:
You can instruct query optimizer to use a specific access path using hints.
If old statistics are not available in the data dictionary then query optimizer scan full table scan.
Full Table Scan
This type of scan reads all rows from a table up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. During a full table scan, all blocks in the table that are under the high water mark are scanned. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multi-block i/o to read the blocks from disk.
When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently.
FTS is not recommended for large tables unless you are reading >5-10% of it (or so) or you intend to run in parallel.
Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. This is because full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls.
When the Optimizer Uses Full Table Scans
The oracle optimizer uses a full table scan in any of following cases:
Lack of Index:
Indexes are not avalable or the query is unable to use any existing indexes. For example, if there is a function used on the indexed column in the query, the optimizer is unable to use the index.
Large Amount of Data :
If optimizer thinks that query will access most of the blocks in the table, then it uses a full table scan, even though indexes might be available.
Small Table :
If a table contains less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, which can be read in a single I/O call, then a full table scan might be cheaper than an index range scan, regardless of the indexes are present.
High Degree of Parallelism :
A high degree of parallelism for a table skews the optimizer toward full table scans over range scans. Examine the DEGREE column in ALL_TABLES for the table to determine the degree of parallelism.
Full Table Scan Hints :
The hint FULL(table alias) instruct optimizer to use a full table scan.
Parallel Query Execution :
When a full table scan is required, response time can be improved by using multiple parallel execution servers for scanning the table. Parallel queries are used generally in low-concurrency data warehousing environments
Small tables are automatically cached according to the given criteria:
|Small||Number of blocks < 20 or 2% of total cached blocks, whichever is larger||If STATISTICS_LEVEL is se to TYPICAL or higher, Oracle decides whether to cache a table depending on the table scan history. The table is cached only if a future table scan is likely to find the cached blocks. If STATISTICS_LEVEL is set to BASIC, the table is not cached.|
|Medium||Larger than a small table, but < 10% of total cached blocks||Oracle decides whether to cache a table based on its table scan and workload history. It caches the table only if a future table scan is likely to find the cached blocks.|
|Large||> 10% of total cached blocks||Not cached|
Automatic caching of small tables is disabled for tables that are created or altered with the CACHE attribute.
The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row.
Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan. Oracle then locates each selected row in the table based on its rowid.
When the Optimizer Uses Rowids : This is generally the second step after retrieving the rowid from an index. The table access might be required for any columns in the statement not present in the index.
Access by rowid does not need to follow every index scan. If the index contains all the columns needed for the statement, then table access by rowid might not occur.
In this method, a row is retrieved by traversing the index. Oracle searches the index for the indexed column values accessed by the statement. If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.
The index contains not only the indexed value, but also the rowids of rows in the table having that value. Therefore, if the statement accesses other columns in addition to the indexed columns, then Oracle can find the rows in the table by using either a table access by rowid or a cluster scan. An index scan can be one of follwing types.
Assessing I/O for Blocks, not Rows
Oracle does I/O by blocks. Therefore, the optimizer's decision to use full table scans is influenced by the percentage of blocks accessed, not rows. This is called the index clustering factor. If blocks contain single rows, then rows accessed and blocks accessed are the same.
However, most tables have multiple rows in each block. Consequently, the desired number of rows could be clustered together in a few blocks, or they could be spread out over a larger number of blocks.
Although the clustering factor is a property of the index, the clustering factor actually relates to the spread of similar indexed column values within data blocks in the table. A lower clustering factor indicates that the individual rows are concentrated within fewer blocks in the table. Conversely, a high clustering factor indicates that the individual rows are scattered more randomly across blocks in the table. Therefore, a high clustering factor means that it costs more to use a range scan to fetch rows by rowid, because more blocks in the table need to be visited to return the data. Example 13-3 shows how the clustering factor can affect cost.
Example 13-3 Effects of Clustering Factor on Cost Assume the following situation:
There is a table with 9 rows.
There is a non-unique index on col1 for table.
The c1 column currently stores the values A, B, and C.
The table only has three Oracle blocks.
Case 1: The index clustering factor is low for the rows as they are arranged in the following diagram.
Block 1 Block 2 Block 3
------- ------- --------
A A A B B B C C C
This is because the rows that have the same indexed column values for c1 are located within the same physical blocks in the table. The cost of using a range scan to return all of the rows that have the value A is low, because only one block in the table needs to be read.
Case 2: If the same rows in the table are rearranged so that the index values are scattered across the table blocks (rather than collocated), then the index clustering factor is higher.
Block 1 Block 2 Block 3
------- ------- --------
A B C A B C A B C
This is because all three blocks in the table must be read in order to retrieve all rows with the value A in col1.
Index Unique Scans
This scan returns a single rowid if a statement contains a UNIQUE or a PRIMARY KEY constraint.
When Optimizer Uses : This access path is used when all columns of a unique (B-tree) index or primary key constraint are specified with equality conditions.
Index Unique Scan Hints : INDEX(alias index_name)
There might be cases where the table is across a database link and being accessed from a local table, or where the table is small enough for the optimizer to prefer a full table scan.
Index Range Scans
This scan can return more than one row. Data is returned in the ascending order of index columns
When Optimizer Uses : Optimizer uses range scan when it finds range operations e.g.
* >, <, <>, >=, <=, BETWEEN
* AND combination
* LIKE'ASD%'OR LIKE'%ASD' does NOT result IN a range scan.
Range scans can use unique or non-unique indexes. Range scans avoid sorting when index columns constitute the ORDER BY/GROUP BY clause.
Index Range Scan Hints : INDEX(table_alias index_name)
Index Range Scans Descending
An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order.
When the Optimizer Uses : When an order by descending clause can be satisfied by an index.
Index Range Scan Descending Hints : INDEX_DESC(table_alias index_name)
Index Skip Scans
Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.
Consider, for example, a table employees (sex, employee_id, address) with a composite index on (sex, employee_id). Splitting this composite index would result in two logical subindexes, one for M and one for F.
For this example, suppose you have the following index data:
('F',98) ('F',100) ('F',102) ('F',104) ('M',101) ('M',103) ('M',105)
The index is split logically into the following two subindexes:
The first subindex has the keys with the value F.
The second subindex has the keys with the value M.
Figure 13-2 Index Skip Scan Illustration
The column sex is skipped in the following query:
SELECT * FROM employees WHERE employee_id = 101;
A complete scan of the index is not performed, but the subindex with the value F is searched first, followed by a search of the subindex with the value M.
In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.
For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.
An Index full scan will perform single block i/o's and so it may prove to be inefficient.
A full scan is also available when there is no predicate, if both the following conditions are met:
- All of the columns in the table referenced in the query are included in the index.
- At least one of the index columns is not null.
A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. It reads the blocks singly.
Fast Full Index Scans
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.
Fast Full Index Scan Hints : INDEX_FFS, same format and arguments as the regular INDEX hint
An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.
Index Join Hints : You can specify an index join with the INDEX_JOIN hint.
A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.
A cluster scan is used to retrieve, from a table stored in an indexed cluster, all rows that have the same cluster key value. In an indexed cluster, all rows with the same cluster key value are stored in the same data block. To perform a cluster scan, Oracle first obtains the rowid of one of the selected rows by scanning the cluster index. Oracle then locates the rows based on this rowid.
A hash scan is used to locate rows in a hash cluster, based on a hash value. In a hash cluster, all rows with the same hash value are stored in the same data block. To perform a hash scan, Oracle first obtains the hash value by applying a hash function to a cluster key value specified by the statement. Oracle then scans the data blocks containing rows with that hash value.
Sample Table Scans
A sample table scan retrieves a random sample of data from a simple table or a complex SELECT statement, such as a statement involving joins and views. This access path is used when a statement's FROM clause includes the SAMPLE clause or the SAMPLE BLOCK clause. To perform a sample table scan when sampling by rows with the SAMPLE clause, Oracle reads a specified percentage of rows in the table. To perform a sample table scan when sampling by blocks with the SAMPLE BLOCK clause, Oracle reads a specified percentage of table blocks.
Example 13-6 uses a sample table scan to access 1% of the employees table, sampling by blocks.
SELECT * FROM employees SAMPLE BLOCK (1);
The EXPLAIN PLAN output FOR this statement might look LIKE this:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 1 | 68 | 3 (34)|
| 1 | TABLE ACCESS SAMPLE | EMPLOYEES | 1 | 68 | 3 (34)|