Oracle's approach to accessing data

This is a review of Oracle's approach to accessing table data, in an Oracle database, to access the data stored in the database,

To go through the following steps in turn:

Pending SQL----> Parsing----> Optimizer processing----> Generating execution plan----> actually executing----> returning execution results,

In this stage of the optimizer's processing, we decide how to access the data of the target table, that is, how the optimizer will access the specific

The data.

There are two ways to access a table in Oracle, one is to access the table directly, one to access the index first, and then the table (and, of course, it is possible

You can only access the index to get the data, so you don't need to return the table.

The following two methods of accessing the data in the table are respectively compiled.

1. Ways to Access tables

There are two ways to directly access data in a table: Full table scan and ROWID scan.

1.1 Full Table Scan

When Oracle accesses the data in the target table, it starts scanning from the first block (block) of the first area (EXTENT) occupied by the table.

To the table's high watermark (Hwm,high water Mark), and finally returns the data that satisfies the where condition.

Analysis: The full table scan will use multiple blocks of reading, when the target table data volume is not large, the efficiency is very high, but the problem is that the full table sweep

The execution time of the stroke will increase with the increment of data volume.

1.2 ROWID Scan

ROWID scanning refers to the fact that Oracle accesses data by locating and accessing it through the physical storage address where the data resides.

For a heap table in Oracle, the ROWID value of the corresponding row record can be obtained through Oracle's built-in ROWID pseudo-column. And then through

The relevant method in the Dbms_rowid package translates the ROWID pseudo-column into the actual physical storage address of the corresponding data row, as follows

Select Empno,ename,rowid,dbms_rowid.rowid_relative_fno (rowid) | | _'||

Dbms_rowid.rowid_block_number (ROWID) | | ' _'|| Dbms_rowid.rowid_row_number (ROWID)

from EMP;

2. Ways to access the index

This is the most commonly used B-tree index in the Oracle database, and the B-tree index resembles an inverted tree that contains two types of data blocks,

One is the index branch block, and one is the index leaf block.

The advantages of the B-tree index are three points

A. All index leaf blocks are at the same level, they are the same depth as the index root node, meaning that any access to the index leaf block

The index key value takes almost the same amount of time;

B. Oracle will ensure that all B-tree indexes are self-balanced, and that it is not possible to have different indexed leaf blocks that are not at the same level;

C. The efficiency of accessing table records through the B-tree index is not significantly reduced as the amount of data in the related tables increases, i.e. by

The time of index access data is controllable and basically stable, which is the biggest difference between index and full table scan.

The following is a common approach to accessing the B-Tree index within Oracle

2.1 Index Uniqueness Scan

The index unique scan is a scan for a unique index, but it only

Only available in the Where condition in the equivalent query class SQL, because the scan object is a unique index, so the scan results at most return a record

Recorded.

2.2 Index Range Scan

The index range scan is applied to all types of B-tree indexes, and when the scanned object is a unique index, the

The Where condition of the subscript SQL must be a range query (predicate condition is between, <, >, etc.); When the scanned object is a non-unique index

, there is no limit to the where condition of the target SQL (either an equivalent query or a range query).

2.3 Index Full scan

The index full scan, which applies to all types of B-tree indexes, refers to all the necessary branches to scan the target index

All index rows of the leaf blocks under the block. By default, Oracle only needs to navigate to the index when it is doing the full scan.

Index row of the first row of the leftmost leaf block, and then use the index to make a list of two-way pointers between the leaf blocks, from left to right

All index rows of all the leaf blocks of the index are scanned.

The precondition for an index full scan is that the property of at least one index key value column for the target index is not NULL.

By default, the index full scan scans all index rows of the leaf block that the target index is in sequentially from left to right, because the index is

Ordered, so the index full scan execution results are also ordered, and is indexed by the index key Value column to sort, thus,

Walk the index full scan in order to achieve the effect of sorting, while avoiding the index of the index key column of the real sort operation, this situation

You can view sorts (memory) in the execution plan for the full scan of the index when you are in SQL, and sorts (disk) is the zero acknowledgment.

Index full scan results in order, which determines that the index full scan cannot be executed in parallel, and is usually a single block read.

2.4 Index Fast Full scan

The index fast full scan (index fast fully scanned) is similar to the index full scan, with the following differences:

A. Applicable to CBO only

B. You can use multiple blocks of read or parallel execution

C. The results of implementation are not necessarily orderly

2.5 Index Skip Scan

Index skip scan is available for all types of composite B-tree indexes, including unique indexes and non-

Uniqueness index), jumping means, for example, the table DEMO1 has a field (gender varchar2 (1), ID number not NULL), and then

Then create a composite B-tree index for the table as follows

Create INDEX idx_xxx on demo1 (Gender,id);

Then insert multiple rows of records into the table in the following form

Begin

For I in 1..5000 loop

INSERT into demo1 values (' F ', i);

End Loop

Commit

End

/

Begin

For I in 5001..10000 loop

INSERT into demo1 values (' M ', i);

End Loop

Commit

End

/

Then, open the execution plan to execute a query

Set Autotrace traceonly

SELECT * from demo1 where id = 100;

You can see the index idx_xxx in the execution plan.

The upper where condition is id=100, that is, it only specifies the query criteria for the second column of the composite index, and there is no reference to the leading column

Specifies the query condition, which is the case of an index hop scan. This is actually all the distinct values that Oracle will have on the leading column

Do the traversal.

The index skip scan efficiency decreases with the increment of the distinct value of the leading column of the target index, so it is only applicable

The number of distinct values in the leading column of the target index is small, and subsequent non-leading columns are very selective and very good.

Table table Data method This piece to the finishing, the subsequent time is best to each point of the example to organize.

Oracle's approach to accessing data