Shulou(Shulou.com)06/01 Report--

Index is an object provided by oracle, which provides a fast way to access data and improves the retrieval performance of the database. The index enables the database program to find the data it needs without scanning the whole table, just like the catalogue of the book, it can quickly find the information it needs without having to read the whole book.

Oracle's database management system uses the following three access methods when accessing data

1. Full table scan

two。 Through ROWID

3. Use index

Classification of the index:

1Btree index structure

The top of the index is the root, which contains items that point to the next index. The next level is the branch block, which in turn points to the block at the next level in the index, and the lowest level block is called the leaf node, which contains index items that point to the table data row. The leaf node is a two-way join, which helps to scan the index in ascending and descending order by keyword.

Create a normal index

Syntax for creating a normal index

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

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

[tablespace] specify a tablespace for the index

Practice environment

In the employee EMP table, create a B-tree index in the employee name column. If the normal index created by oracle does not say

Ming type is B-tree index.

View the index EMP_NAME_IDX that you just created

Create unique and non-unique indexes

Unique index: ensure that there are no duplicate values in the column that defines the index, and the index keyword of the unique index can only point to a row in the table.

Non-unique index: columns that define 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 a regular B-tree index, the reverse key index reverses the bytes of the index column while maintaining the order. By reversing the data value of the index key, the reverse key index makes the modification of the index evenly distributed to the whole index tree, and mainly applies multiple instances to access the same database at the same time. Using the reverse key index, the index insertion operation is scattered among multiple index block keys. If the B-number index is used, because the index keywords are in the same index block because of the similar position in the index tree, conflicts will occur when multiple instances are updated at the same time, which leads to the bottleneck of IGAMO access.

The syntax is as follows:

CREATE index Index name on Table name (column name) REVERSE

Bitmap index

Bitmap indexes are suitable for low-cardinality columns, that is, the column has a limited number of values, such as the job column in the employee table, and even millions of employee records are limited. JOB column can be used as a bitmap index

Advantages of bitmap indexing:

Compared with B-tree index, query based on bitmap index column can reduce response time.

Compared with other indexing techniques, bitmap indexes take up less space.

Bitmap indexes should not be used on tables where INSERT,update,delete operations occur frequently, because a single bitmap index item points to many data rows of the table, and when the index item is modified, all the data rows it points to need to be locked, which will seriously reduce the concurrent processing power of the database. Bitmap index is suitable for data warehouse and decision support system.

In the emp table, create a bitmap index for the type of work (job) column

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

Function-based index

View information about index columns: index name, table name, index column

Maintain index

Re-index:

The index needs to be maintained, and if there are a large number of deletions and inserts in the indexed table, it will make the index very large, because after the delete operation, the deleted index space can not be automatically reused. For large tables and tables with frequent DML operations, index maintenance is very important. ORACLE provides REBUILD instructions to rebuild the index, so that the index space can reuse the space occupied by deleted values, making the index more progressive.

When you rebuild the index, you can also modify the tablespace of the index

Merge index fragments

Merging index fragments can free up some disk space, which is an important way of index maintenance and a way to maintain disk space.

Delete index

The DROP INDEX statement deletes the index

Delete the INDEX_BIT_JOB bitmap index in the employee table

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.