Oracle Tree-like index (Image harvesting "Oracle only")

I. Tree-like index features

1. Low height

2. Store column values

3. Orderly structure

Let's look at the structure of the index first.

The above structure diagram illustrates that the index is composed of root (root block), Branch (STEM) and leaf (leaf block) Three parts, wherein the bottom of the leaf block mainly stores the key column value (index column specific value), And can be specifically located in the location of the data rowid (here rowID and query time with the rownum is not the same concept, interested in Baidu RowNum and rowid the difference)

Note: The index block and data block need to be differentiated, and the index block is also the disk space

Two. Oracle Index query

How does an index retrieve data quickly?

As an example of a book: SELECT * from test where id = 12;
If the test table has 10,050 data, and id=12 only returns one piece of data, the Test table ID field has an index that retrieves data such as:

Through the picture understanding, the SQL statement roughly as long as 3 Io, here is an example, the actual situation 1w multi-data volume, the index height should be only 2 layers, that is, 2 io or so.

First, the query locates at the root of the index, then locates the stem data according to the root block data distribution, then locates to the leaf block.

With the index, Oracle will only scan some of the index blocks, not all of them, so it will improve performance. Because it is a SELECT * statement, not only the ID of this field, so not only to scan the index block, but also according to the id=12 index in the ROWID, to go back to the table scan other fields, so add an IO.

Database io: Continuous read, random read, random write and continuous write

Three. Understanding of index height and structure

Index is not really the root of the leaf 3 layer, the index height is based on the amount of data to reflect.

1. Steps to establish an index

or the test table, with an ID column, and now an index on the ID,

1) First, the value of the ID column of the test table is taken out of the order in memory, each value corresponding to the ROWID is also taken out.

2) The values of the sequentially stored columns and the corresponding rowid are stored in the Oracle idle block, forming an index block, as follows:

Summary of steps:

1. to build the index first sort , so the index is actually orderly

2. column values are indexed in blocks , which is how the index blocks come in.

3. fill A piece of the piece, the amount of data increases in the index, that is, the leaf block horizontal expansion

4. Similar two pieces need human tube , the leaf block has 2 or more times, you need to have a boss to manage multiple leaf blocks, this old chunk is not a leaf block of the pointer, so the height of the index is not easy to become particularly large is the truth

Thus, the three main features of the index can also be obtained:

To verify the above theory, you can test it locally according to the example in the book.

It is possible to look at the query statements for tables with the same index height through the execution plan, which is not the same as the cost, because the number of IO times is the same, and the index is higher, the index to query the data, also several times the IO, the speed is also very fast (in the case, the query returns only one of the cases)

Why should you choose a high-selection field when indexing is established, such as a table with only a few states, should not be indexed?

According to the above theory, it can be known that if the 10w data based on the index query 5w,io the number of times equivalent to 5w*3 io,

And the full table scan is far less so, even if a scan is also up to 10w, not to mention full-table scanning, can sweep multiple data blocks at a time.

Oracle Tree Index in detail (graph harvesting "not Oracle only")