What are the different types of access path that can be followed by optimizer?
Wednesday, 23 December 2009 15:56 | 
Written by Administrator | 
1- 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. Optimizer can use full table scan in case of Lack of Index, Large Amount of Data, and Small Table
2- ROW ID scan
The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block. Oracle first obtains the rowids of the selected rows and then locates each selected row in the table based on its rowid. Optimizer uses when any columns in the statement not present in the index.
3- Index Scan
In this method, a row is retrieved by traversing the index. The index contains not only the indexed value, but also the rowids of rows.
|
Index Unique Scans |
This scan returns a single rowid if statement contains a UNIQUE or PRIMARY KEY constraint. |
|
Index Range Scans |
This scan can return more than one row. Data can be ascending or descending. Optimizer uses when it finds range operations (>, <, <>, >=, <=, between) |
|
Full Scans |
Optimizer chooses when the statistics indicate that it is going to be more efficient than a Full table scan. Optimizer chooses if statement is without where clause and all the select columns included in the index and At least one of the index columns is not null. (performs single block I/O) |
|
Fast Full Index Scans |
Alternative to a full table scan the difference is, it performs multi-block reads and can not be used against bitmap indexes |
| Index Joins | An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query |
| Bitmap Indexes | A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to rowid. |
4- Cluster Access A cluster scan is used to retrieve, from a table stored in an indexed cluster
5- Hash Access A hash scan is used to locate rows in a hash cluster, based on a hash value
6- Sample Table Scan This access path is used when a statement's FROM clause includes the SAMPLE clause or the SAMPLE BLOCK clause.
1 2 |
--scan to access 1% of the employees table SELECT * FROM employees SAMPLE BLOCK (1); |
Last Updated (Wednesday, 23 December 2009 18:37)