Research on Oracle Indexing technology

Oracle Index Type

B-Tree Index

Specific type Index

Determining index Columns

Index of primary key and unique key value columns

FOREIGN Key Index

Other appropriate index columns

B-Tree index B-Tree index algorithm

B-Tree refers to B-tree (Balanced tree), the existence of B-tree is a multi-forked tree designed for storage devices. In B-Tree

1. Each node contains a maximum of M child nodes
2. Each non-leaf node contains at least ⌈m/2⌉ child nodes except the root node
3. The root node contains at least 2 child nodes or it is a leaf node
4. Non-leaf nodes with K child nodes contain k-1 key values
5. All leaf nodes appear on the same layer

where M is called the order of the B-tree, the nodes of a 3-order B-tree are arranged as

"Pointer" key value "pointer" key value "pointer"

When traversing to the node, the key value is searched in sequence, depending on the size of the pointer to the child node before the key value, or the pointer after the node point.

B+-tree is the evolution of B-tree

The difference is that each index key value of the B + tree corresponds to a child node, and then the leaf node remains aligned (Balanced)

B-Tree Index sub-type Index organization table

Index Organization table IoT stores all the contents of a table row in a B-tree index structure

Create an indexed organization table

CREATE TABLE ... ORGANIZATION

An indexed organization table is a special kind of table that stores indexes and table data together. The data in the normal table is stored in the database in an unordered (Heap) manner. The Index organization table is sorted by primary key, and the table's data is stored in a two-tree (b-tree) manner. The Index organization table does not require the column ID, but rather accesses the table's data directly through the primary key.

Advantages of index Organization tables

1. Fast random Access: The index and table are stored together, and the table is the index itself, and Oracle only needs an index to update the table
2. Fast Range Scanning: (range) All data is sorted by primary key, so the scanning speed is very easy to approach Logn
3. Less storage requirements: indexes and tables are stored together to reduce the storage space of the index

Disadvantages:

As with very fast bitmap indexing, the disadvantage of an indexed organization table is that updating the contents of a table results in large-scale index rebuilds, so that the indexed organization table is not in a table for frequent data changes.

Unique index

Similar to a unique key-value constraint, a unique index guarantees that all non-null key values in the inserted table are different.

Creating a unique index is typically used in conjunction with a primary key and a unique constraint

CREATE UNIQUE INDEX

Reverse Key index

The reverse key index can deal with the large number of sequential Insert index IO, can evenly distribute the index data to avoid a large number of similar key values together.

Create a reverse Index

CREATE INDEX cust_ridx1 on Cust (cust_id) REVERSE

You cannot specify a reverse statement on a bitmap index or an indexed organization table

Reverse key index cannot be a descending type

The idea of Reverse key index is opposite to that of traditional index, and the traditional index idea will increase the index density in the intensive data region to achieve the purpose of improving the search efficiency of high frequency access zone. However, reverse key indexing in the opposite direction, in order to prevent local overheating (when the data size is too large to the likelihood that the high-frequency hotspot data is crammed into a certain disk of the channel caused by the local data overheating) there is a large number of sequential data insertion causes the partition Table index tree is extremely unbalanced, the indexed value is reversed operation.

Absolute forward-ordered data in successive inserts index is, if the key value scheme of the index is reversed, then the absolute forward ordered data is degraded to random data (not absolute reverse data), and we maintain the quoted balance. Otherwise, the extreme imbalance of the index tree in a large number of sequential insertions results in a significant increase in I/O times on one side of the branch, resulting in the local overheating problem described earlier.

Key Compression Index

Reduce the combined index that the leading column often repeats, using compress N to create a compressed index

CREATE INDEX cust_cidx_1 on Cust (last_name, first_name) COMPRESS 2

Cannot create a compressed index on a bitmap index

Descending index

The default index in the B-tree algorithm is the ascending index, which creates the descending index, which can be reversed by the DESC keyword.

For example, descending indexing is fast in large-value-intensive data.

CREATE INDEX cust_didx1

On CUST (cust_id DESC);

Specific index types

A particular index type is an index type that is specific to a particular case.

Bitmap index

Bitmap indexing is commonly used in a data warehouse environment for columns with a relatively low number of different values.

Databases that frequently use operations such as insert/update/delete should not use bitmap indexes because bitmap indexes lock multiple rows during DML operations

Create a bitmap index using the keyword bitmap

CREATE BITMAP Index F_SALES_FK1

On F_sales (d_date_id);

Bitmap indexing algorithm

The bitmap index enumerates all the values of a column after a bitwise (1/0) method column, marking all columns with values of that enumerator as 1

If there is a column like state in the test table, 10 rows of data are as follows:

10 20 30 20 10 30 10 30 20 30

Then three bitmaps will be created, as follows:

BLOCK1 key=10 1 0 0 0 1 0 1 0 0 0
BLOCK2 key=20 1 0 0 0 1 0 1 0 0 0
BLOCK3 key=30 1 0 0 0 1 0 1 0 0 0

Bitmap index statistics are fast, and a consistent bitwise operation on rows and columns is very fast. However, the disadvantage is that entities should not change frequently, because all columns have a one by one correspondence to all enumeration values, and the variable range of frequently-changed entity sets is very large for the bitmap index.

Function-based indexing

Indexes created based on defined SQL functions or expressions

CreateIndex cust_fidx1

Oncust (Upper (last_name));

Partition index

A partitioned index is logically an index, but implemented in different segments to ensure that a large database has good performance. (Partitioned index is Oracle's paid service)

The global partitioned index does not map to the segments of the underlying table, you can create a global partition index for a regular table or partition table, a B-tree implementation, and a global partition index using global partition

CreateIndex f_sales_gidx1 on F_sales (Sales_amt)

Globalpartition by RANGE (Sales_amt) P

PARTITION pg1 VALUES less THAN (25),

PARTITION pg1 VALUES less THAN (50),

PARTITION pg1 VALUES less THAN (maxvalue));

The local partition index must be built on a partitioned table, following the same partitioning policy as the underlying table.

Partitioned Tables and indexes

The partition table was just beginning to remind me of the MBR partition table and GPT partition table of the disk. Support for multiple partition tables from 10 in Oracle database. Partitioned tables are similar to disk partitions, where physical partitions are added to the data, and partitioned tables are physically unrelated. The benefit of this is that even if the physical storage of a partitioned table is corrupted, it does not affect other data.

A partitioned index is an index that is created independently on all of the zones, and it is automatically maintained, and does not affect the use of other partitioned indexes on the index when a partition is drop or truncate, that is, the index is not invalidated and is relatively easy to maintain, but has a slight impact on query performance.

When you create a partition for a partitioned table, the primary key must contain the partitioning column, and additional constraints cannot be created, either because of the isolated isolation of the physical space.

Local indexes and global indexes

Global index, is the index across the partition table, in doing cross-partition query advantage fast;

Local index, partition table of a partition index, faster than the global, and maintenance more convenient;

The concept of global and local indexes is related to partitioned tables. Global indexes and local indexes have different mechanisms, and the global index can be either partitioned or non-partitioned, but the global index must be a prefix index.

method indexes for optimizing indexes are related to constraints

Indexes are related to constraints that are equivalent to conditional judgments that are based on the constraints of the entity set during the index. Because all indexes are based on its corresponding entity collection, the constraint of the entity collection is the constraint of the index, which is an effective way to make an early judgment on the invalid search in the index stage.

Create a local area index

Local indexes save a lot of disk IO and storage overhead, because only local indexes on entity collections significantly reduce the amount of index storage; On the other hand, local indexes are targeted high-density indexes on the physical areas of high-frequency access, allowing the speed of lookups to push Logn as far as possible.

The underlying table must be a partitioned table

The base table is built as a partitioned table on the one hand, it is in the purpose of protecting data, isolating the irrelevant physical design, on the other hand, maintaining the separation method of all basic form data independence mapping to the physical design, it is advantageous to establish the correspondence relation between the data operation logic Planning and the efficiency analysis.

Experience and summary

Through this semester of database learning and Oracle database Indexing technology research, I have a more comprehensive understanding of database technology.

The first contact with the database is the Web site, often choose the more popular and configure a simple MySQL database for data manipulation. The functionality used in general also has only the underlying database table operations, and is entirely used as a performance-enhancing Excel. Through this semester's database I have a complete understanding of the Oracle database including the entire database system, including the definition of abstract concepts and basic physical definitions have a clear understanding of the database data relations and data manipulation ideas.

In particular, the expansion of database indexing technology, let me to the large-scale data management and system assumptions of the main points of understanding, and applied to the curriculum learned the basic database model and some disk-related knowledge. The basic balance tree algorithm and construction method are consolidated, and the ideas of many advanced indexing techniques are also recognized.

Indexing technology can be understood as a pre-prepared for querying data. In general, the faster the index technology for data entities the higher the static requirements, and these indexes are relatively high level of abstraction, need a relatively solid data base, otherwise frequently updated entities are often accompanied by large-scale table reconstruction operations.

Reference documents

Oracle Indexing Technology (US) Darl Kuhn

Figure Source: http://blog.csdn.net/v_JULY_v/article/details/6530142/

Definition of B-tree: Https://en.wikipedia.org/wiki/B-tree

Bitmap index: https://www.cnblogs.com/LBSer/p/3322630.html

Partition index:

http://ctwen.iteye.com/blog/2250320

http://blog.itpub.net/35489/viewspace-616711/

Index Organization table: http://blog.csdn.net/ljean/article/details/51360398

Reverse Key index: http://blog.csdn.net/zq9017197/article/details/7321604

Research on Oracle Indexing technology