16. Oracle Index

First, management index-Principle Introduction
An index is a data object used to speed up data access. Proper use of indexes can significantly reduce I/O times and thus improve data access performance. There are many kinds of indexes we mainly introduce several commonly used:
Why does the query speed up when the index is added?

Second, create an index
1), single-column index
Single-column indexes are indexes based on individual columns
Syntax: CREATE INDEX index name on table name (column name);
eg, create index nameindex on custor (name);
2), composite index
A composite index is a two-column or multiple-column index. You can have multiple indexes on the same table, but the combination of required columns must be different, such as:
CREATE INDEX emp_idx1 on EMP (ename, job);
CREATE INDEX emp_idx1 on EMP (Job, ename);
The above two indexes are two different indexes.

Iii. Principles of Use
1), it makes sense to build indexes on large tables.
2), indexing on columns that are frequently referenced on the WHERE clause or join condition
3), the level of the index should not exceed 4 layers
Can you show this effect to the students here?
How to build a big table?

Iv. Disadvantages of the index
The index has some congenital weaknesses:
1), indexing, the system to occupy about 1.2 times times the hard disk and memory space to save the index.
2), when updating data, the system must have additional time to update the index at the same time to maintain data and index consistency.
The practice shows that inappropriate indexes not only help but degrade system performance. Because a large number of indexes spend more system time than no indexes when inserting, modifying, and deleting operations.
For example, it should be inappropriate to create an index in the following fields:
1. Fields that are seldom or never referenced;
2. Logical type fields, such as male or female (yes or no), etc.
To sum up, improve query efficiency is to consume a certain system resources at the expense of the index can not be blindly established, this is the test of a DBA is an important indicator of excellence

V. Other indexes
According to the data storage method, can be divided into b* tree, reverse index, bitmap index;
According to the number of index column classification, can be divided into single-column index, composite index;
Unique and non-unique indexes can be categorized by the uniqueness of indexed column values.
There are also function indexes, global indexes, partition indexes ...

For the index I would also like to say:
In different situations, we will build indexes on different columns and even create different kinds of indexes, remember that technology is dead and people are alive.
For example: The b* tree index is built on columns with few duplicate values, whereas bitmap indexes are built on columns with a large number of duplicates and relatively fixed values.

Vi. Display of index information
1), on the same table can have more than one index, by querying the data dictionary view Dba_indexs and User_indexs, you can display the index information. Where Dba_indexs is used to display all index information for the database, and User_indexs is used to display the current user's index information: Select Index_name, Index_type from User_indexes where Table_ name = ' table name ';
2), Display index column
By querying the data dictionary view user_ind_columns, you can display the information for the column that corresponds to the index
Select table_name, column_name from user_ind_columns where index_name = ' ind_ename ';
You can also view the index information by using the PL/SQL developer tool