Summary of various indexes in Oracle

Index plays an important role in Oracle SQL-TUNNING, index is divided into B-TREE index, bitmap Index two categories, but need to emphasize particularly is the index organization table, clustering these two most Special Indexes! Where the most used is the B-TREE index.

1. B-TREE Index
B-TREE index is the most commonly used, B-TREE as the name suggests is similar to the tree structure, I want to learn computer students should understand. The storage format of each leaf node is:
Key Value rowid
In Oracle databases, reading data based on rowid is the fastest. Oracle first reads the rowid from the memory and then physically reads the rowid.
A) Unique Indexes
A unique index includes not only the unique attribute, but also the index feature. Especially for primary key, we usually select unique index.
B) Implicit Indexes
Implicit index (I don't know if my translation is correct). When we run statements similar to the following:
Create Table Vogts (ID number, name varchar2 (20), address varchar2 (300 ));
Oracle automatically creates an implicit index in the background.
C) concatenated Indexes
Multiple indexes. It is to combine multiple fields to form an index. However, when making up an index, you must query it in order.
The test results are as follows:
Create tables and indexes:
SQL> Create Table Vogts (ID number, name varchar2 (20), address varchar2 (300 ));

The table has been created.
SQL> Create index idx_vogts on Vogts (ID, name );

The index has been created.
Insert data:
SQL> insert into Vogts values ('1', 'vogts', 'hangzhou ');

One row has been created.

SQL> commit;
The two query methods are as follows: (1) ID query; (2)
SQL> select ID from Vogts where id = 1 and name = 'vogts ';

ID
----------
1

Execution Plan
----------------------------------------------------------
Plan hash value: 3883664107

------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 9 | 1 (0) | 00:00:01 |
| * 1 | index range scan | idx_vogts | 1 | 9 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

1-access ("ID" = 1 and "name" = 'vogts ')

SQL> select ID from Vogts where name = 'vogts ';

ID
----------
1

Execution Plan
----------------------------------------------------------
Plan hash value: 2527835214

------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 9 | 1 (0) | 00:00:01 |
| * 1 | index skip scan | idx_vogts | 1 | 9 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

1-access ("name" = 'vogts ')
Filter ("name" = 'vogts ')

SQL> spool off;
The test results show that if the index I created is (a, B), at least a must be included in the query conditions before the index is taken. Otherwise, the full table scan is required.
D) null values
If the index column contains a null value, no index is taken. Null does not work in the index. You think the index itself stores the value and rowid, and this value is gone. What is rowid? Right?
If the query condition is a is null, full table scan is required! Therefore, use this condition with caution among developers.
E) reverse Index
Reverse index. For example, if a common index is stored in Vogts, the reverse index is stored in stgov. The advantage of the reverse index is that when the index storage is unreasonable, the use of reverse index is more effective. However, there should be no problem after index rebuild;
F) based Function Index
The function of the index function is to make the query condition continue to be indexed when the query condition is added with the function;

2. Bitmap Index
Bitmap indexes are mainly used to index category fields. If the key value exceeds 1000, the efficiency of this index will be significantly reduced!
Other instructions are similar to B-TREE indexes;

3. index the Organizational table
The index organization table (OIT organization index table) is an index. Normal indexes only store index classes, while the index organization table stores all the data of the table. If the table has additional data, other data is attached to the block;

4. Clustering
Cluster Index. My personal understanding is that multiple tables share one index. No actual tests.