41.oracle index, Analysis index, index defragmentation

Overview

Indexes are divided into B-tree indexes and bitmap indexes. We mainly study B-Tree index, B-tree index such as (image from the network):

Index is an optional structure related to the table, logically and physically independent of the table data, the index can optimize the query, can not optimize the Dml,oracle Automatic Maintenance index, the frequent DML operation will win a lot of index Wei Hua.

If the SQL statement accesses only the columns that are indexed, the database simply reads the data from the index and does not read the table, and if the statement also accesses columns that are not indexed, the database uses ROWID to look up rows in the table, typically to retrieve the table data, the database reads the index block first, and then reads the corresponding table.

The purpose of the index is to reduce IO

1. Large table, number of rows returned <5%
2. Columns that are frequently queried using the WHERE clause
3. Columns with high dispersion
4. Low cost of updating key values
5. Logical AND, or high efficiency
6. See which table the index is built in
   SELECT * from User_indexes;
   SELECT * from User_ind_columns;

Use of indexes

1. Unique index
   Create unique index EMPNO_IDX on EMP (EMPNO);
2. General Index
   CREATE INDEX EMPNO_IDX on EMP (EMPNO);
3. Combined index
   CREATE INDEX JOB_DEPTNO_IDX on EMP (JOB,DEPTNO);
4. Function Index: This function must be used when querying.
   CREATE INDEX FUN_IDX on EMP (lower (ename));

Index issues

View execution plan: set autotrace traceonly explain;

Index fragmentation problem: Because the base table does DML operations, causing the automatic change of the index table block, especially the delete operation of the base table, causes the index_entries of the index table to be tombstoned, and note that only if all index_entry in an index block are deleted, To delete this index block, the index to the base table Delete, insert operation will produce an index fragmentation problem.

There is no clear quantitative standard for index fragmentation in Oracle documentation, and Oracle recommends resolving fragmentation of tables and indexes through Segment Advisor (fragment Advisor), which is mentioned later in the course, and if you want to resolve them yourself, you can view the Index_stats view When one of the following three scenarios occurs, the accumulated fragments should be sorted out (experience)

1. Height >= 4 (Overview of the index tree in the figure is 3)
2. pct_used < 50%
3. Del_lf_rows/lf_row > 0.2

Real:

First build the table, build the index

Then insert 1 million data

Analysis Index: Analyse index T_IDX validate structure;

View analysis Results: select name,height,pct_used,del_lf_rows/lf_rows from Index_stats;

And then we'll break the index and look at the results again.

You can see that the last indicator has changed. The description produces some fragments. Then it needs to be organized:

It is visible that the last indicator is normal (less than 0.2).

41.oracle index, Analysis index, index defragmentation