Introduction to access paths for Oracle 11g release optimizer

Article content
• Full table scan (Scans)
• Physical ID Scan (Rowid Scans)
• Indexing scan (index Scans)
• Clustered access (Cluster access)
• Hash access (hash access)
• Sample table Scan (sample table Scans)
• How the query optimizer chooses access paths
• Notes

Access paths (access paths) are ways to retrieve data from a database. In general, the Index Access path (index access paths) is used to retrieve a small subset of the table rows, and full-table scans are more efficient when accessing a large portion of the table (scans). Online transaction PROCESSING,OLTP application consisting of highly selective, short-term-running SQL statements, usually indexed access paths. On the other side, decision support systems (Decision support systems) tend to use partitioned tables to perform full table scans of related partitions.

Full table Scan

--------------------------------------------------------------------------------

All table scans read all rows of the table and filter out rows that do not meet the selection criteria. During a full table scan, all data blocks below the high watermark (water MARK,HWM) in the table are scanned. All data below the high water level indicates the amount of space used (after inserting the data), or the space in which the data has been formatted (after inserting the data and then deleting it). Checks whether each row satisfies the WHERE clause.

When Oracle performs a full table scan, the block is read sequentially. Because the blocks are contiguous, I/O calls are larger than a single block, speeding processing. Reads the range of the call size from 1 blocks to the number of blocks specified by the initialization parameter db_file_multiblock_read_count. Using multiple-block reads means that a full table scan is performed with high efficiency. Each block is read only once.

Why is a full table scan faster for accessing large amounts of data?
Full-table scans are more cost-effective than index-range scans when accessing a large part of a table's block. This is because a full table scan can use a larger I/O call, while a small but large I/O call is less expensive than a larger number of calls.

When does the optimizer use full table scans?
The optimizer uses a full table scan in the following situations:

• No index
If the query cannot use any existing indexes, a full table scan is used. For example, if you have a query that uses functions on indexed columns, the optimizer cannot use the index, but instead uses a full table scan.

If you need to use an index for case-insensitive retrieval, either do not allow case mixing in the search or create a function based index on the retrieved column, such as UPPER (last_name). Refer to "Using Performance based on function index".

• Large amount of data
If the optimizer considers that the query accesses most blocks in the table, a full table scan is used, even if there is an index available.

• Small Table
If a table high watermark contains smaller blocks than the Db_file_multiblock_read_count parameter, they can be read in a single I/O call, then the full table scan peso range scan is more cost-effective, regardless of the other parts of the table that can be accessed, or the current index.

• high degree of parallelism
The high degree of parallelism of a table causes the optimizer to prefer a full table scan rather than a range scan. Check the DEGREE column of the All_tables table to determine the degree of parallelism.

Full table Scan hints
Use full (table alias) to command the optimizer to use the whole table scan. Reference "indicates access path".

You can use cache and NoCache to indicate whether the retrieved blocks are placed in the buffer cache. The cache command optimizer, when performing a full table scan, places the retrieved blocks at the end of the most recent use of the buffer cache LRU list.

The small table 1 is automatically cached according to the criteria in the following table.

Table 1 Guidelines for caching tables

Table Size Criteria Cache
The number of small blocks is greater than 20, or the cached blocks reach a total of 2%, no matter how big. If Statistics_level is set to typical or higher, Oracle determines whether to rely on the table's scan record to cache a table. The table is cached only after the table scan may find the cached block. If Statistics_level is set to BASIC, the table is not cached.
Larger than above, and the cached block is less than the total number of 10% Oracle determines whether a table is cached based on the table's scan and load records. The table is cached only if the subsequent scan may look for a cached block.
Large cached blocks larger than 10% of the total are not cached.

Disables automatic caching of small tables for tables created or changed with the cache property.

Execution of parallel queries
When a full table scan is required, the response time is increased by scanning the table for multiple parallel execution services. Parallel queries are typically used for data warehouse environments with low concurrent data.

Physical ID (rowids) scan

--------------------------------------------------------------------------------

The physical ID of a row specifies the data file (datafile) and a block of data that contains the rows ' data blocks and the row positions of the blocks. Locating a row by specifying the physical ID of a row is the quickest way to retrieve a single row, because the precise positioning of the row is specified in the database. In order to access a table through a physical ID, Oracle first obtains the physical ID of the selected row, either from the statement's WHERE clause or through an index scan of one or more table indexes. Oracle then locates each selected row in the table based on their physical ID.

When the optimizer uses Rowids
Generally, this is the second step after retrieving the physical ID from an index. The table you are accessing may require any columns in the statement, and these columns are not in the index.

Access through physical IDs does not need to be scanned by each index. Access through physical IDs may not occur if the index contains all the columns required by the statement.

Index Scan

--------------------------------------------------------------------------------

In this method, the row is retrieved by traversing the index, using the value of the indexed column specified in the statement. An index scan retrieves data from an index based on one or more values in the index. To perform an index scan, Oracle retrieves the index for the value of the indexed column that was established. If the statement accesses only the indexed columns, Oracle reads the indexed column values directly from the index, rather than from the table.

The index contains not only the index value, but also the physical ID of the row that has the value in the table. Therefore, if the statement accesses other columns that do not have an index, Oracle finds rows in the table through physical IDs or clustered scans.

The following are the index scan types:

• Access to I/O through blocks, not rows
• Index unique Scan (indexed unique Scans)
• Index range Scan (index range Scans)
• Index access decrement scan (index Range Scans Descending)
• Index filter Scan (indexes skip Scans)
• Full table scan (fully Scans)
• Fast full table index Scan (fast all index Scans)
• Index connection (joins)
Bitmap Index (Bitmap Indexes)

Clustered access

--------------------------------------------------------------------------------

A clustered scan is used to retrieve all rows that have the same clustered key value from a table that is stored in the indexed clustered cluster. To perform a clustered scan, Oracle first obtains the physical ID of the selected row by scanning the clustered index. Then locate the row based on the physical ID.

Hash access

--------------------------------------------------------------------------------

Hash scans are used to locate rows in a Hachizu cluster based on a hash value. In a Hachizu 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 clustered key value. The data block in which the row containing the hash value is scanned.

Sample Table Scan

--------------------------------------------------------------------------------

Sampling (random) Table scans retrieve random sampled data from a sample table or a complex SELECT statement. This access path is used when the FROM statement contains a sample clause or a sample block clause. When sampling data with the sample clause, a sample table scan is performed, and Oracle reads a specified percentage of rows in the table. When sampling blocks with the sample block clause, Oracle reads a percentage of the specified table block.

How the query optimizer chooses access paths

--------------------------------------------------------------------------------

The query optimizer chooses an access path based on the following factors:

• Access paths available for statements
• Evaluate the cost of executing statements through the combination of each access path, or access path
To select an Access path, the optimizer first determines which access path is available by examining the condition of the WHERE clause and the FROM clause. The optimizer then uses the access path to produce a set of possible execution plans and evaluates the cost of each execution plan by indexing, columns access to the statistics of the table statement. Finally, the optimizer chooses the execution plan with minimal cost.

When you select an Access path, the query optimizer is affected by the following:

• Optimizer instructions
You can specify that the optimizer uses the specified access path, in addition to the FROM clause containing sample or sample block.

• Statistical information
For example, if a table has not been parsed since it was created, and its high watermark (HWM) block is less than the parameter db_file_multiblock_read_count, the optimizer considers the table to be smaller and uses a full table scan. Review the last_analyzed and BLOCKS columns in the All_tables table to check for statistics.

Note

--------------------------------------------------------------------------------

High water line (water mark,hwm)
Oracle is divided into 4 granularity-table spaces, segments, extents, and blocks-on the logical store.

• Block is the smallest storage unit (8K). Each I/O operation of Oracle is also grouped by block. That is, when Oracle reads data, it reads how many blocks, not how many rows.
The area is composed of a series of adjacent blocks, which is the basic unit of the Oracle allocation space. For example, when a table is created, Oracle allocates a space for the table. When the area is full, Oracle expands in extents, not blocks.
The segment is made up of a series of areas. In general, when an object is created (a table or index), Oracle assigns a segment to the object. In a sense, a segment is a certain kind of data. such as CREATE TABLE, Oracle creates the data segment for the table, when create INDEX, Oracle also allocates a segment, but this is the index segment.
• The table space contains segments, areas, and blocks. The tablespace data is physically stored in the data file in which it resides.
Oracle uses a high-water mark (high water mark,hwm) to define the blocks and unused blocks used in a segment.

For example, when you create a table, Oracle assigns a segment to the object. At least one of the extents is allocated, even if no records have been inserted. The first block of the first zone is called the segment header (SEGMENT heade). Segment Headers Store some information, such as HWM information. The HWM is located in the 2nd block. When the block does not fit the new data, Oracle stores the block above the HWM, while the HWM moves up. When you keep inserting data, the HWM will move up continuously. In this way, HWM represents the used block, and the HWM above represents an allocated but unused block