Shulou(Shulou.com)06/01 Report--

This is a review of oracle's method of accessing table data. In an oracle database, if you want to access the data stored in the database,

To go through the following steps in turn:

SQL to be executed-> parse-> Optimizer processing-> generate execution plan-> actual execution-> return the execution result

Among them, at this stage of the processing of the optimizer, to determine the way to access the target table data, that is, how the optimizer should access the specific

The data.

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

You can get the data only by accessing the index, so you don't need to go back to the table.

Let's sort out the above two methods to access the data in the table.

1. The method of accessing the table

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 EXTENT occupied by the table

Scan all the way to the high water level line (HWM,High Water Mark) of the table, and finally return the data that meets the where condition.

Analysis: multi-block reads are used in full table scanning, and when the amount of data in the target table is small, the efficiency is very high, but the problem is that full table scanning

The execution time is unstable and uncontrollable, and the execution time will increase with the increase of the amount of data.

1.2 ROWID scan

Rowid scanning means that when oracle accesses data, it locates and accesses data through the physical storage address where the data is located.

For the heap table in oracle, the value of the rowid where the corresponding row record is located can be obtained through the rowid pseudo column built into oracle. Then pass through

The relevant methods in the DBMS_ROWID package translate 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

two。 The method of accessing the index

Here we are talking about the most commonly used B-tree index in oracle databases. The B-tree index is similar to an inverted tree and contains two types of data blocks.

One is the index branch block, the other is the index leaf block.

There are three advantages of B-tree index.

a. All index leaf blocks are on the same layer, and they are at the same depth from the index root node, meaning to access any of the index leaf blocks

Index key values take almost the same time

B. oracle will ensure that all B-tree indexes are self-balanced, so it is impossible for different index leaf blocks to be in the same layer.

c. The efficiency of accessing records in a table through a B-tree index does not decrease significantly with the increase in the amount of data in the relevant table, that is, through the

The time of accessing data by index is controllable and basically stable, which is the biggest difference between index walking and full table scanning.

The following is an introduction to the common methods of accessing B-tree indexes in oracle

2.1 Index uniqueness scan

Index uniqueness scan (INDEX UNIQUE SCAN) is a scan for unique index (UNIQUE INDEX), but it only

It is only applicable to the equivalent query class SQL in the where condition. Because the scan object is a unique index, the scan result returns at most one entry.

Record.

2.2 Index range scan

Index range scan (INDEX RANGE SCAN) applies to all types of B-tree indexes, when the scan object is a unique index, the

The where condition of the label SQL must be a range query (predicate condition is BETWEEN, etc.); when the scan object is a non-unique index

There are no restrictions on the where conditions of the target SQL (either an equivalent query or a range query).

2.3 Index full scan

Index full scan (INDEX FULL SCAN) applies to all types of B-tree indexes, which means to scan all necessary branches of the target index

All index rows of leaf blocks under the block. By default, when oracle does a full scan of the index, it only needs to navigate to the location in the

The first index row of the leftmost leaf block of the index, and then use the two-way pointer chain table between the leaf blocks of the index, in turn from left to right.

Order scans all index rows of all leaf blocks of the index.

The prerequisite for a full index scan is that the property of at least one index key value column of the target index is NOT NULL.

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

Ordered, so the execution results of the full scan of the index are also ordered, and are sorted according to the index key value column of the index, thus it can be seen

The full scan of the index can achieve the effect of sorting while avoiding the real sorting operation on the column of the index key value of the index.

You can check whether sorts (memory) and sorts (disk) is 0 in the execution plan of the full index scan during SQL.

The order of the results of the index full scan determines that the index full scan cannot be performed in parallel and is usually read in a single block.

2.4 Fast full scan of index

Index fast full scan (INDEX FAST FULL SCAN) is similar to index full scan, with the following differences:

a. Only applicable to CBO

b. You can use multi-block reads or execute them in parallel

c. The execution result is not necessarily orderly.

2.5 Index skip scan

Index skip scan (INDEX SKIP SCAN) applies to all types of composite B-tree indexes (including unique and non-unique indexes)

Unique index), jump means, for example, table DEMO1 has fields (gender varchar2 (1), id number not null), but

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 ('Feminine I)

End loop

Commit

End

/

Begin

For i in 5001..10000 loop

Insert into demo1 values ('Mauremeni)

End loop

Commit

End

/

Then, open the execution plan and execute a query

Set autotrace traceonly

Select * from demo1 where id = 100

You can see that index idx_xxx is used in the execution plan.

The above where condition is id=100, that is, it only specifies the query condition for the second column of the composite index, not the leading column.

Specify the query criteria, which is the case of index jump scans. In fact, this is all the distinct values that oracle will set to the leading column.

Do traversing.

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

When the number of distinct values of the leading column of the target index is small, and the selectivity of subsequent non-leading columns is very good.

The method of the table data has been sorted out at this point, and it is best to give an example of each point in the future.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.