Oracle 11g R2 Index

An index is an object provided by Oracle that provides a quick way to access the data, improving the retrieval performance of the database. The index allows the database program to not have a full table scan of the entire table, in which you can find the data you want, the directory of the book, you can quickly find the information required by him, without reading the whole book.

Oracle's database management system uses the following 3 methods of access when accessing data;
1. Full table Scan

2. By rowID

3. Using the Index

Classification of indexes:

1,b Tree Index structure

The top of the index is the root, which contains items that point to the next-level index. The next level is the branch block, which also points to the lower-level block in the index, and the lowest-level block is called the leaf node, which contains the index entry that points to the table data row. Leaf nodes are two-way connections that help you scan indexes by keywords that are worth ascending and descending.

Create a normal index
syntax for creating a normal index

Create [unique] index name on table name (column name) [tablespace table space name]

[Unique] is used to specify a unique index, which by default is a non-unique index

[Tablespace] Specify tablespace for index

Practice Environment

In the Employee EMP table, create a B-tree index in the Employee Name column, and the normal index created by Oracle if it does not say

The type of the Ming is the B-Tree index

View the index of the newly created EMP_NAME_IDX

Creating unique and non-unique indexes
Unique index: guarantees that the column that defines the index does not have any duplicate values, and the index key for a unique index can only point to one row in the table.

Non-unique index: A column that defines an index can have duplicate values

In the salary Level Salgrade table, create a unique index for the level number (grade) column

Reverse Key index
In contrast to the regular B-tree index, the reverse key index reverses the byte of the indexed column while preserving the order. Reverse key index by reversing the index key data values, so that the index changes are evenly distributed across the entire index tree, the main application of multiple instances can access the same database in the scene. Use the reverse key index to spread the index insert operation across multiple index block keys, and if the index keyword is in the same index block as it is located in the index tree with a B-number index, conflicts will occur when multiple instances are updated at the same time, causing bottlenecks on I/O access.

The syntax is as follows:

CREATE index name on table name (column name) REVERSE;
Bitmap index
Bitmap indexes apply to low-cardinality columns, where the value of the column is limited, such as the job column in the employee table, and even if there are millions of employee records, there is a limited number of jobs. The job column can be used as a bitmap index

Advantages of bitmap Indexing:

Queries based on bitmap indexed columns can reduce response time relative to B-Tree indexes

Bitmap index footprint is reduced compared to other indexing techniques

Bitmap indexes should not be used on tables that frequently occur insert,update,delete operations, because a single bitmap index entry points to many rows of data in a table, and when you modify an index entry, you need to lock all the data rows it points to, which can severely degrade the concurrency of the database. Bitmap indexing is suitable for use in data warehousing and decision support systems.

In the Employee table (EMP) table, create a bitmap index for the job column

In the Employee table (EMP), create an uppercase function index for the employee name (ename) column

Function-based indexing

View information about an indexed column: Index name, table name, index column

Maintain indexes
Rebuild Index:

Indexes need to be maintained, and if there are a large number of deletions and insertions in the indexed table, the indexes are large, because after the delete operation, the deleted index space cannot be automatically reused. For tables where large tables and DML operations are frequent, the maintenance of indexes is important. Oracle provides the rebuild instruction to rebuild the index so that the index space can reuse the space occupied by the deleted value, making the index more forward-playing.

You can also modify the table space of an index when rebuilding an index


Merge Index Fragmentation

Merging index fragments frees up some disk space, is an important way of index maintenance, and is how you maintain disk space

Delete Index

Drop INDEX statement to delete indexes

To delete an index_bit_job bitmap index in an employee table

If you are interested in this, please scan the QR code below for free for more details

Oracle 11g R2 Index