Oracle Index < seven >

Oracle Index

Managing indexes-Introduction to Principles

? Introduced

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?

Create an index

? Single-column index

A single-column index is an index that is based on individual columns, such as:

Create index index name on table name (column name);

? 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);

Principles of Use

? Principles of Use

1. Building indexes on large tables makes sense

2. Indexing on columns that are frequently referenced on a WHERE clause or join condition

3. No more than 4 levels of index

Can you show this effect to the students here?

How to build a big table?

Disadvantages of the Index

? Index disadvantage analysis

The index has some congenital weaknesses:

1. Indexing, the system will occupy about 1.2 times times the disk and memory space to save the index.

2. When updating data, the system must have additional time to update the index simultaneously 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 are not indexed when inserting, modifying, and deleting operations

Cost more system time.

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, to improve the efficiency of the query 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 a very important means of excellence

Standard.

Other indexes

? Introduced

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. Like what:

The b* Tree index is built on columns with very few duplicate values, whereas bitmap indexes are built on columns with a large number of duplicate values and relatively fixed values.

Display index Information

? Show all indexes on a table

You can have multiple indexes on the same table, and you can display the index information by querying the data dictionary view Dba_indexs and User_indexs. where Dba_indexs 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 ';

? Display index Columns

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

Oracle Index < seven >