Introduction to Oracle indexes (10-minute reading test)

What can I do if I continue my experiment for 10 minutes today? I chose to introduce Oracle indexes. The results are as follows:

Search for information from Google:1: 15 seconds.

Read the overview section:3: 15 seconds.

Oracle indexes mainly include btree and bitmap indexes. By default, most btree indexes are used. This index is usually the unique index seen and clustered index. btree is used in OLTP to speed up the query. Bitmap indexes are quite attractive in Oracle. They are mainly used in OLAP (online data analysis), that is, data warehouse, to speed up queries, saves storage space. Generally, indexes consume a large amount of storage space. Bitmap uses compression technology to reduce disk space. Btree uses a high base (that is, a column with a large data similarity), and a bitmap is used in a low base series. The basic principle of Bitmap indexes is to use bitmaps instead of column values in indexes. Usually there is a very low set potential (cardinality) between the keys of a fact table and a dimension table. Bitmap indexes are used to make the storage more effective. Compared with B * tree indexes, it only requires less storage space, so that more records can be read each time. In addition, compared with B * tree indexes, bitmap indexes convert Bitmap indexes into bitwise arithmetic operations for connections and aggregation, this greatly reduces the running time and greatly improves the performance.
How can I reasonably use Bitmap indexes in Oracle? Consider the following.
* If you want to use Bitmap indexes, the initialization parameter star_transformation_enabled should be set to true.
* The optimization mode should be CBO. For data warehouse environments, you should always consider using CBO (COST-BASEDOPTIMIZER ).
* Bitmap indexes should be created on the foreign key columns of each fact table. (This is just a general rule .)
In addition, the objective determination of cardinality in a data table is also a problem. The set of 10 thousand data records containing only three values is low, so it is not low to include 0.1 billion records in 30 thousand records? In this case, we recommend that you perform a few data simulation tests. Generally, in the data warehouse environment, the bitmap index performance is better than the B * tree index. Note that Bitmap indexes are not designed for the OLTP database and should not be used in large quantities in the OLTP database, especially for tables with update operations.

B * tree index-1 minute 30 seconds

B * tree indexes are the most common index structures. This type of indexes is created by default. B * tree indexes provide the best performance when retrieving high-base data columns (high-base data columns refer to the columns with many different values. When the proportion of the number of retrieved rows to the total number of rows is smaller than that of the hour, the B-Tree Index provides a more effective method than the full table search. However, when the check range is more than 10% of the table, the performance of data retrieval cannot be improved. B-tree indexes are based on binary trees and are composed of branch blocks and leaf blocks. In the tree structure, the bottom block is called a leaf block, which contains the value of each indexed column and the rowid corresponding to the row. On the leaf node, It is a branch block used for navigation. It contains the index column (keyword) range and the address of another index block, as shown in 26-1.

Suppose we want to find the row with the index value of 80, starting from the top entry of the index tree, locate the row with a value greater than or equal to 50, and then find the first 2nd branch blocks on the left, locate 75-100, locate the leaf block, find the rowid corresponding to 80, and then read the data block according to the rowid to obtain the data. If the query condition is set to a range, for example, where column> 20 and column <80, the first leaf block containing 20 is located, and other leaf blocks are located horizontally, until 80 blocks are found, you do not need to relocate them from the entry every time.

Reverse index-2 points

A reverse index is a branch of B * tree indexes. It is designed to be applied in certain environments. Oracle launched it to reduce the contention for leaf blocks in the parallel server (Oracle Parallel Server) environment. When a column in the B * tree index is generated by an incremental serial number, the index information is basically distributed in the same leaf block. When you modify or access similar columns, index blocks are prone to Contention. The index code in the reverse index will be distributed to each index block, reducing competition. The reverse index reverses the bytes of each column in the index code. We can see what it has done through the dump () function. For example, there are three consecutive numbers: 1, 2, and 3. Use the dump () function to view their representation in Oracle.

SQL> select 'number', dump (1, 16) from dual
2 Union all select 'number', dump (2, 16) from dual
3 Union all select 'number', dump (3, 16) from dual;

'Numbe dump (1, 16)
-----------------------
Number typ = 2 Len = 2: C1, 2 (1)
Number typ = 2 Len = 2: C1, 3 (2)
Number typ = 2 Len = 2: C1, 4 (3)

Then compare the reverse:

SQL> select 'number', dump (reverse (1), 16) from dual
2 Union all select 'number', dump (reverse (2), 16) from dual
3 Union all select 'number', dump (reverse (3), 16) from dual;

'Numbe dump (reverse (1), 1
-----------------------
Number typ = 2 Len = 2: 2, C1 (1)
Number typ = 2 Len = 2: 3, C1 (2)
Number typ = 2 Len = 2: 4, C1 (3)

We found that the entire structure of the index code was reversed. In this way, the 3 index codes, 1 and 2, will not appear in the same leaf block, thus reducing competition. However, another disadvantage of reverse indexing is that it cannot be used in all places where regular indexes are used. It cannot be used in range search. For example, where column> value. Because the index code in the leaf block of the index does not have a category, you cannot perform area scan by searching adjacent leaf blocks.

Descending index-2 points.

Descending index is a new index in 8i and another derivative of B * tree. Its change is that the storage mode of columns in the index changes from ascending to descending, in some cases, descending order indexing will take effect. For example, we can query a table and sort it:

SQL> select * from test where a between 1 and 100 order by a DESC, B ASC;

Row 100 has been selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 2 card = 100 bytes = 400)
1 0 sort (order by) (cost = 2 card = 100 bytes = 400)
2 1 index (range scan) of 'ind _ BT '(NON-UNIQUE) (cost = 2 card = 100 bytes = 400)

Here, the optimizer first selects an index range scan and then another sorting step. If a descending index is used, the sorting process is canceled.

SQL> Create index test. ind_desc on test. testrev (a desc, B ASC );

The index has been created.

SQL> analyze index test. ind_desc compute statistics;

Index analyzed

Let's take a look at the execution path:

SQL> select * from test where a between 1 and 100 order by a DESC, B ASC;

Row 100 has been selected.

Execution Plan (SQL Execution Plan, which will be explained later ).
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 2 card = 100 bytes = 400)

1 0 index (range scan) of 'ind _ DESC '(NON-UNIQUE) (cost = 2 card = 100 bytes = 400)

We can see that the sorting process disappears because Oracle has sorted all data in descending order when creating a descending index.

Note that the compatible parameter in init. ora should be set to 8.1.0 or later. Otherwise, the DESC keyword will be ignored during creation.

 

------

There are Bitmap indexes and so on. There is no time,
The overall feeling is good. If you can stick to it, it is no problem to add 2-3 knowledge points every day.
Of course, not all materials can be split into small time slices for reading,
Continue the test!