Oracle index introduction (graphic explanation)

For databases, indexing is a required option, but for various large databases, indexing can greatly improve the performance of the database, so that it becomes an indispensable part of the database.

Index category:
Logical Classification
Single column or concatenated
Unique or nonunique is unique and non-unique, that is, whether the key value (key) of a column or several columns is unique.
Function-based is based on some Function indexes. When you execute some functions, You need to compute them. You can save and index the computing results of some functions in advance to improve efficiency.
Doman indexes data other than the database and uses less data.

Physical Classification
B-Tree: normal or reverse key B-Tree indexes are also common indexes that we have traditionally understood. They can be classified into normal indexes and reverse indexes.
Bitmap: cited by Bitmap.

B-Tree Index

B-Tree indexes are also common indexes. B-tree (balance tree) is the balance tree, and the left and right branches are relatively balanced.

B-Tree index

Root is the Root node, branch is the branch node, and leaf to the bottom layer is called the leaf node. Each node represents a layer. When you look for a certain data, read the root node first, then read the supporting node, and finally find the leaf node. The leaf node stores the index entry, which corresponds to a record.

Index entry components:
The Indexentry header stores some control information.
Key column length the length of a key
Key column value the value of a key
ROWID pointer, specific to a data

Create an index:

Copy codeThe Code is as follows:
User Logon:
SQL> conn as1/as1
Connected.

Create a table:
SQL> create table dex (id int, sex char (1), name char (10 ));
Table created.

Insert 1000 data records into the table
SQL> begin
For I in 1000
Loop
Insert into dex values (I, 'M', 'chongshi ');
End loop;
Commit;
End;
/

PL/SQL procedure successfully completed.

View table records
SQL> select * from dex;
ID SE NAME
--------------------------------
.........
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
Rows selected.

Create an index:
SQL> create index dex_idx1 on dex (id );
Index created.
Note: Create an index for the first column (id) of the table.

View created tables and Indexes
SQL> select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX

An index is separated from a table and exists as a single individual. In addition to creating an index based on a single field, you can also create an index based on multiple columns. Oracle requires that you create an index for up to 32 columns.

Copy codeThe Code is as follows:
SQL> create index dex_index2 on dex (sex, name );
Index created.

SQL> select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
DEX_INDEX2 INDEX

Here you need to understand:

Write a book, and set the directory only after the Chapter Page is set. The same is true for database indexes. Only data is inserted before the index is created. Then we will insert and delete the database content, and the indexes will also need to change. However, oracle automatically modifies the index.

The figure above gives a clearer description of the index structure.

Records 0 to 50 data records at the same node, and records 0 to 10 for splitting on the branch node ....... between 42 and 50, the leaf node records the length and value of each data segment and points the pointer to specific data.
The leaf segments on the last layer are two-way links. They are ordered links to quickly lock a data range.

For example:

Copy codeThe Code is as follows:
SQL> select * from dex where id> 23 and id <32;

ID SE NAME
--------------------------------
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
M chongshi
Rows selected.

For example, if you search for a column, you can first find 23rd pieces of data and then 32nd pieces of data by indexing, so that you can quickly lock a search range, if each piece of data needs to be searched from the root node, the efficiency will be very low.

Bitmap Index

Bitmap indexes are mainly created for a large number of Columns with the same value. Take the country resident login table as an example. Assume that there are four fields: name, gender, age, and ID card number. The age and gender fields generate many identical values, gender only has two values: Age (1 to 120) and age (assuming the maximum age is 120 years. Therefore, no matter whether a table has hundreds of millions of records, there are only two values (male and female), which are differentiated by gender fields ). The bitmap index is an index created based on this feature of the field.

Bitmap Index

We can see that a leaf node (identified by different colors) represents a key, and start rowid and end rowid specify the search range of this type, A leaf node is marked with a unique bitmap value. Because a value type corresponds to a node, during row query, bitmap indexes directly perform bitwise operations (and or) based on different bitmap values ), to obtain the result set vector (the calculated result ).

Example:

Suppose there is A data table T, which has two data columns A and B. The values are as follows. We can see that columns A and B have the same data.

Create Bitmap indexes for two data columns A and B: idx_t_bita and idx_t_bitb. The storage logic structure of the two indexes is as follows:

The Idx_t_bita index structure corresponds to the leaf node:

The Idx_t_bitb index structure corresponds to the leaf node:

For the query "select * from t where B = 1 and (a = 'L' or a = 'M ')"
Analysis: Bitmap indexes are significantly different from B * indexes. The use of B * indexes usually starts from the root node and is compared to the nearest qualified leaf node through continuous branch nodes. Scan the result set rowid through continuous Scan on the leaf node.

Bitmap indexes work in different ways. Bitwise operations (and or) with different bitmap values can be used to obtain the result set vector (the calculated result ).
For instance SQL, you can split it into the following operations:

1. a = 'L' or a = 'M'

A = L: vector: 1010
A = M: vector: 0001

The result of the or operation is the two vectors or operation: the result is 1011.

2. Combine the vectors of B = 1

Intermediate result vector: 1011
B = 1: vector: 1001
And operation result, 1001. The first and fourth rows are the query results.

3. Get the result rowid.

At present, we know the starting rowid and ending rowid, as well as the results of the first and fourth actions. You can obtain the rowid of the result set through the trial calculation method.

Features of Bitmap indexes:
1. Saved storage space for Bitmap indexes
2. Quick creation of Bitmap indexes
3. Bitmap indexes allow null key values
4. efficient access to table records using Bitmap indexes

Create a bitmap index:

Copy codeThe Code is as follows:
View table records
SQL> select * from dex;
...................
ID SEX NAME
--------------------------------
M chongshi
M chongshi
G chongshi
G chongshi
G chongshi
M chongshi
G chongshi
G chongshi
G chongshi
M chongshi
Rows selected.

For the above table, sex (gender) has only two values, which are most suitable for creating bitmap
Create an index:
SQL> create bitmap index my_bit_idx on dex (sex );

Index created.

View the created Reference
SQL> select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
MY_BIT_IDX INDEX

Rules for creating Indexes

1. Weigh the relationship between the number of indexes and DML, that is, DML inserts and deletes data.
Here we need to weigh a problem. The purpose of index creation is to improve the query efficiency. However, too many indexes will affect the speed of data insertion and deletion, because the table data we modified, the index also needs to be modified. Here we need to weigh whether our operation is to query more or modify more.
2. Place the index and the corresponding table in different tablespaces.
When reading a table, the table and the index are both performed. If the table and the index are in the same tablespace, resource competition will occur. If the table and the index are in the same tablespace, they can be executed in parallel.
3. It is best to use blocks of the same size.
Oracle has five I/O reads by default. If you define six or ten I/O reads, you must read the I/O twice. Preferably an integer multiple of 5 to improve efficiency.
4. If a table is large, it takes a long time to create an index. Because index creation also produces a large amount of redo information, you can set no or no redo information during index creation. As long as the table data exists, the index fails and cannot be created again. Therefore, you do not need to generate redo information.

5. You should create an index based on the specific business SQL statements, especially the where condition and the order of where conditions. Try to put the filtering range at the end, because SQL Execution is performed from the back to the front. (Xiao Li cutting kitchen knife)

Common indexing operations

Change index:

Copy codeThe Code is as follows:
SQL> alter index employees_last _ name_idx storage (next 400 K maxextents 100 );

After an index is created, you can modify its parameters. View related documents for details

Adjust the index space:

Copy codeThe Code is as follows:
New Space
SQL> alter index orders_region_id_idx allocate extent (size 200 K datafile '/disk6/index01.dbf ');

Release Space
SQL> alter index oraers_id_idx deallocate unused;

When using an index, there may be insufficient space or a waste of space. In this case, you need to add or release space. The preceding two commands are used to complete the ADD and release operations. Oracle can automatically help with the addition of space. Manual addition can improve the performance if you know the database.

Re-create index:

Oracle automatically completes the process. When we perform frequent database operations, the index is also modified. When we delete a record in the database, the corresponding index does not only make a delete mark, but it still occupies space. The space of a block is released only when all the tags of a block are deleted. In this way, the index performance will decrease after a long time. At this time, you can create a new clean index to improve efficiency.

Copy codeThe Code is as follows:
SQL> alter index orders_region_id_idx rebuild tablespace index02;

You can use the preceding command to create an index again. The oracle re-indexing process is as follows:

1. After locking a table, no one else can perform any operation on the table.
2. Create a New (clean) Temporary index.
3. Delete the old index
4. Rename the new index as the old index name.
5. Unlock the table.

Cited by mobile:

In fact, we also use the above command to move the index to other tablespaces and specify different tablespaces when specifying the tablespace. When a new index is created in another location, the old index is moved.

Copy codeThe Code is as follows:
SQL> alter index orders_region_id_idx rebuild tablespace index03;

Online Index re-creation:

As described above, when creating an index, the table is locked and cannot be used. It takes a long time to re-create an index for a large table. In order to meet your needs for table operations, this online re-create index is generated.

Copy codeThe Code is as follows:
SQL> alter index orders_id_idx rebuild online; creation process:

1. Lock a table
2. Create temporary and empty indexes and IOT tables to store on-going DML. The key value stored in a common table, and the data in the table directly stored in the table referenced by IOT. on-gong DML is the addition, deletion, and modification operations performed by the user.
3. unlock a table
4. Create a new index from the old index.
5. the IOT table stores the on-going DML information. The content of the IOT table is merged with the newly created index.
6. Lock a table
7. Update the content of the IOT table to the new index again to remove the old index.
8. Rename the new index as the old index name.
9. unlock a table

Integrated index fragmentation:

For example, if many indexes have surplus space, you can use a single command to integrate the remaining space.

Copy codeThe Code is as follows:
SQL> alter index orders_id_idx coalesce;

Delete An index:

Copy codeThe Code is as follows:
SQL> drop index hr. departments_name_idx;

Analysis index

Check the effects of indexing. As described earlier, indexing takes a long time to produce a large amount of fragmentation, junk information, and waste of space. You can re-create an index to improve the performance.

You can use a command to complete the analysis index. The analysis results are stored in the index_stats table.

Copy codeThe Code is as follows:
View the table that stores the analysis data:
SQL> select count (*) from index_stats;

COUNT (*)
----------

Execute the analysis index command:
SQL> analyze index my_bit_idx validate structure;

Index analyzed.

Check that index_stats has a data record.
SQL> select count (*) from index_stats;

COUNT (*)
----------

Query the data:
SQL> select height, name, lf_rows, lf_blks, del_lf_rows from index_stats;

Height name LF_ROWS LF_BLKS DEL_LF_ROWS
-----------------------------------------------------------------------------------------------------
MY_BIT_IDX 1000 3 100

Analysis Data Analysis:

(HEIGHT) the reference HEIGHT is 2. (NAME) The index NAME is MY_BIT_IDX. (LF_ROWS) the reference table contains 1000 rows of data. (LF_BLKS) It occupies 3 blocks (DEL_LF_ROWS) delete 100 records.

This also verifies the problem mentioned above. The 100 data records deleted are only marked as deleted, because the total number of data records is still 1000, occupying 3 blocks, therefore, if each block contains more than 333 records and the deleted data contains more than 333 records, the total number of data records is reduced when a block is cleared.