[Oracle] index and Index Table management

(i) The concept of an index

An index is a database object related to a table or cluster, which provides a fast access path for querying data, reduces disk I/O, and improves retrieval efficiency.

The index is composed of index values and rowid two parts that record the corresponding physical address, and is arranged in an orderly manner by index values, ROWID can quickly locate the records that match the criteria of the database table. It can be understood that the index is the directory of a book, the index value is the title of the directory, ROWID is the page number of the table of contents.

(ii) Update strategy for indexes

As standard data is inserted, deleted, and modified, the information in the index table is automatically updated to the exact process:

When inserting data into a table, the system inserts an index entry corresponding to the table in the leaf node of the index;

L DELETE the data in the table, the system automatically deletes the relevant index entries, but the space is not recycled, nor is it assigned to the new index entries, only the entire leaf node is deleted, the space will be recycled;

When you modify the data in the Index table, the system deletes the entries for the index leaf node and then reassign the index entries

From the update strategy, it is not possible to create as many indexes as possible. Indexes can reduce the efficiency of inserting, deleting and updating data while improving query efficiency. Oracle recommends that indexes and tables be dispersed in different tablespaces, preferably on separate disks, to improve efficiency.

(iii) Index structure

The database has 2 kinds of index structures: The Balanced tree index structure (b_tree) and the bitmap index structure (BITMAP).

(1) Balanced tree index structure

The whole index structure consists of the root node, the branch node and the leaf node, in which the branch nodes can have multiple layers. The information of the root node points to the next Level branch node, the lowest point node points to the leaf node, and the leaf node holds the index entry information. The index entry information consists of 4 parts: The index is based on the column information (index entry header), the length of the index column (key column length), the index value (key column value), and the index corresponding to the ROWID. The B_tree index occupies a lot of space, and is suitable for applications with a wide range of index values (large cardinality) and low repetition rate.

Figure. Balanced tree Index structure diagram

(2) Bitmap index structure

The bitmap structure is also organized by the balance tree, but in the leaf node each index value corresponds to a bitmap instead of a rowid, a bitmap can contain one or more rowid, and the bits to ROWID mapping is implemented by the mapping function in the index. Bitmap indexes are suitable for columns with a small range of values and a high repetition rate for the index data.

Figure. Bitmap index Structure

(iv) Create an index

The index can be created using the CREATE INDEX:

CREATE [UNIQUE][BITMAP] INDEX [schema.]index_name on [schema.]TABLE_NAME (index_expr[ASC | DESC][,...])[PCTFREE integer] [pctused integer] [Initrans integer][STORAGE (storage_clause)][LOGGING][nologging][ONLINE][tablespace tablespace_name][nocompress | [COMPRESS integer] ][sort| Nosort][REVERSE][Noparallel | [PARALLEL integer]]

Explain:

Unique: Create a unique index, default non-uniqueness

BITMAP: Create bitmap index, default B_tree index

Used to specify the order of index values, ASC is ascending, desc is descending

PCTFREE | pctused | Initrans setting the use of data blocks

STORAGE: Sets how the index is stored and, if not specified, inherits the storage parameter settings of the Tablespace

LOGGING | Nologging: Indicates whether the index creation process is written to the redo log file, which is written by default

ONLINE: Allows execution of DML (statements requiring commits, such as insert/update/delete, etc.) statements while creating an index or rebuilding an index, but does not allow execution of DDL (statements that do not require commits, such as Create/alter/drop)

Tablespace: Indicates the table space where the index is stored

cmpress | Nocompress: Whether duplicate data in the index is compressed, not compressed by default

SORT | Nosort: By default, when you create an index, the data in the table is sorted first, and if our data is already sequenced, we can choose Nosort to speed up the creation of the index;

REVERSE: Creating a reverse-order index

Noparallel | PARALLEL: Indicates whether the index is allowed to be created in parallel, the default value is Noparallel

"Not to be continued ... 】

[Oracle] index and Index Table management