Optimizer (CBO) Access Paths
Typical Reasons for a Full Table Scan
Oracle Optimizer Access Paths
An access path is a technique used by a query to retrieve rows from a row source.
The means by which the database retrieves data from a database. For example, a query using an index and a query using a full table scan use different access paths.
The access path determines the number of units of work required to get data from a base table. To determine the overall plan cost, the optimizer assigns a cost to each access path:
•Table scan or fast full index scan
During a table scan or fast full index scan, the database reads multiple blocks from disk in a single I/O. The cost of the scan depends on the number of blocks to be scanned and the multiblock read count value.
•Index scan
The cost of an index scan depends on the levels in the B-tree, the number of index leaf blocks to be scanned, and the number of rows to be fetched using the rowid in the index keys. The cost of fetching rows using rowids depends on the index clustering factor.
Introduction to Access Paths
A row source is a set of rows returned by a step in an execution plan. A row source can be a table, view, or result of a join or grouping operation.
A unary operation such as an access path, which is a technique used by a query to retrieve rows from a row source, accepts a single row source as input. For example, a full table scan is the retrieval of rows of a single row source. In contrast, a join is binary and receives inputs from exactly two row sources
The database uses different access paths for different relational data structures. The following table summarizes common access paths for the major data structures.
The optimizer considers different possible execution plans, and then assigns each plan a cost.
The optimizer chooses the plan with the lowest cost.
In general, index access paths are more efficient for statements that retrieve a small subset of table rows, whereas full table scans are more efficient when accessing a large portion of a table.
Table Access Paths
A table is the basic unit of data organization in an Oracle database.
Relational tables are the most common table type. Relational tables have with the following organizational characteristics:
•A heap-organized table does not store rows in any particular order.
•An index-organized table orders rows according to the primary key values.
•An external table is a read-only table whose metadata is stored in the database but whose data is stored outside the database.
About Heap-Organized Table Access
By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order.
As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.
Row Storage in Data Blocks and Segments: A Primer
The database stores rows in data blocks. In tables, the database can write a row anywhere in the bottom part of the block. Oracle Database uses the block overhead, which contains the row directory and table directory, to manage the block itself.
An extent is made up of logically contiguous data blocks. The blocks may not be physically contiguous on disk.
A segment is a set of extents that contains all the data for a logical storage structure within a tablespace.
For example, Oracle Database allocates one or more extents to form the data segment for a table. The database also allocates one or more extents to form the index segment for a table.
By default, the database uses automatic segment space management (ASSM) for permanent, locally managed tablespaces.
When a session first inserts data into a table, the database formats a bitmap block.
The bitmap tracks the blocks in the segment. The database uses the bitmap to find free blocks and then formats each block before writing to it. ASSM spread out inserts among blocks to avoid concurrency issues.
The High Water Mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used. Below the HWM, a block may be formatted and written to, formatted and empty, or unformatted.
The low high Water Mark (low HWM) marks the point below which all blocks are known to be formatted because they either contain data or formerly contained data.
During a full table scan, the database reads all blocks up to the low HWM, which are known to be formatted, and then reads the segment bitmap to determine which blocks between the HWM and low HWM are formatted and safe to read.
The database knows not to read past the HWM because these blocks are unformatted.
Importance of Rowids for Row Access
Every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. A rowid is a 10-byte physical address of a row.
The rowid points to a specific file, block, and row number. For example, in the rowid AAAPecAAFAAAABSAAA, the final AAA represents the row number.
The row number is an index into a row directory entry. The row directory entry contains a pointer to the location of the row on the block.
The database can sometimes move a row in the bottom part of the block.
>>> For example, if row movement is enabled, then the row can move because of partition key updates, Flashback Table operations, shrink table operations, and so on.
If the database moves a row within a block, then the database updates the row directory entry to modify the pointer. The rowid stays constant.
Oracle Database uses rowids internally for the construction of indexes. For example, each key in a B-tree index is associated with a rowid that points to the address of the associated row.
Physical rowids provide the fastest possible access to a table row, enabling the database to retrieve a row in as little as a single I/O.
In a direct path read, the database reads buffers from disk directly into the PGA, bypassing the SGA entirely.
The figure shows the difference between scattered and sequential reads, which store buffers in the SGA, and direct path reads.
Situations in which Oracle Database may perform direct path reads include:
•Execution of a CREATE TABLE AS SELECT statement
•Execution of an ALTER REBUILD or ALTER MOVE statement
•Reads from a temporary tablespace
•Parallel queries
•Reads from a LOB segment
What is ROWID access?
ROWID is the fastest way to access a specific row in Oracle because it points directly to the physical location of the row (datafile, block, row slot).
Accessing by ROWID is essentially a table access by ROWID — usually coming from another step in the plan (like index access or explicit ROWID condition).
When does Oracle CBO choose ROWID instead of Index?
Direct ROWID specified in WHERE clause
If the query has WHERE ROWID = '...', Oracle bypasses indexes and goes straight to the data block.
Example:
SELECT * FROM employees WHERE ROWID = 'AAAFSbAAEAAAAFnAAA';
Index access leads to ROWID lookup
Normal Index Range Scan returns ROWIDs first → then Oracle must do a Table Access by ROWID to fetch actual columns not present in the index.
But if all columns are in the index (Index-Only / Covering Index), Oracle skips the ROWID table lookup.
CBO estimates Index is not selective enough
If statistics show that using the index would still require fetching a large percentage of rows, Oracle may instead do full table scan or ROWID access directly if the ROWID is already known (e.g., from nested loop join result).
Single-row access from ROWID
In certain joins (like nested loops), Oracle may already have ROWIDs from the driving table, so it chooses table access by ROWID to get the row instead of re-scanning an index.
Clustering factor of index is poor
If the clustering factor is high (meaning table rows are not physically ordered like the index), Oracle may prefer ROWID or full table scan over using that index.
Row movement scenarios (partitioned tables, updates)
Sometimes Oracle resolves row locations using ROWIDs after partition pruning or updates.
Typical Execution Plan Examples
Using Index Range Scan + ROWID:
EXPLAIN PLAN FOR
SELECT first_name FROM employees WHERE employee_id = 101;
-- Plan:
INDEX RANGE SCAN (EMP_ID_IDX)
TABLE ACCESS BY ROWID (EMPLOYEES)
Using only ROWID:
SELECT * FROM employees WHERE ROWID = 'AAAFSbAAEAAAAFnAAA';
-- Plan:
TABLE ACCESS BY ROWID (EMPLOYEES)
In summary:
Oracle CBO decides to use ROWID access when:
The query explicitly filters by ROWID, or
Another operation (like index or join) has already produced ROWIDs, and direct row access is cheaper than re-scanning an index, or
Statistics show that index access is not selective/efficient and ROWID lookup is faster.
Full Table Scans
A full table scan reads all rows from a table, and then filters out those rows that do not meet the selection criteria.
A scan of table data in which the database sequentially reads all rows from a table and filters out those that do not meet the selection criteria. All data blocks under the high water mark are scanned.
le Scan
In general, the optimizer chooses a full table scan when it cannot use a different access path, or another usable access path is higher cost.
The following table shows typical reasons for choosing a full table scan.
How a Full Table Scan Works
In a full table scan, the database sequentially reads every formatted block under the High Water Mark.
The database reads each block only once.
The following graphic depicts a scan of a table segment, showing how the scan skips unformatted blocks below the High Water Mark.
Because the blocks are adjacent, the database can speed up the scan by making I/O calls larger than a single block, known as a multiblock read.
The size of a read call ranges from one block to the number of blocks specified by the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.
For example, setting this parameter to 4 instructs the database to read up to 4 blocks in a single call.
The algorithms for caching blocks during full table scans are complex.
For example, the database caches blocks differently depending on whether tables are small or large.
Table Access by Rowid
A rowid is an internal representation of the storage location of data.
The rowid of a row specifies the data file 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 because it specifies the exact location of the row in the database.
Note:
Rowids can change between versions. Accessing data based on position is not recommended because rows can move.
When the Optimizer Chooses Table Access by Rowid
In most cases, the database accesses a table by rowid after a scan of one or more indexes.
However, table access by rowid need not follow every index scan. If the index contains all needed columns, then access by rowid might not occur.
How Table Access by Rowid Works
To access a table by rowid, the database performs multiple steps.
The database does the following:
1.Obtains the rowids of the selected rows, either from the statement WHERE clause or through an index scan of one or more indexes
Table access may be needed for columns in the statement not present in the index.
2.Locates each selected row in the table based on its rowed
Optimizer (CBO) Access Paths
Typical Reasons for a Full Table Scan
Alireza Kamrani – Database Box Group
Table Access by Rowid: Example
This example demonstrates rowid access of the hr.employees table.
Assume that you run the following query:
SQL> SELECT * FROM employees WHERE employee_id > 190;
Step 2 of the following plan shows a range scan of the emp_emp_id_pk index on the hr.employees table.
The database uses the rowids obtained from the index to find the corresponding rows from the employees table, and then retrieve them.
The BATCHED access shown in Step 1 means that the database retrieves a few rowids from the index, and then attempts to access rows in block order
to improve the clustering and reduce the number of times that the database must access a block.
------------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows| Bytes| Cost(%CPU)|Time|
-------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 2(100)| |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED |EMPLOYEES | 16| 1104 | 2 (0) |00:00:01|
|*2| INDEX RANGE SCAN |EMP_EMP_ID_PK | 16| | 1 (0) |00:00:01|
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------------
2 - access("EMPLOYEE_ID">190)
No comments:
Post a Comment