Oracle Database Index

Oracle Database Index

In a relational database, an index is a database structure that is related to a table, which enables the SQL statements that correspond to tables to execute faster. The index acts as a catalog of books, and you can quickly find what you want based on the page numbers in the catalog.

Indexes are a must for a database, but for today's large databases, indexes can greatly improve the performance of the database so that it becomes an integral part of the database.

Index classification:

Logical classification

Single column or concatenated for one or more columns

Unique or nonunique is the only and non-unique cited, that is, the key value (key) for a column or columns is unique.

Function-based based on certain function indexes, when some functions need to be evaluated, the results of some functions can be saved and indexed in advance to improve efficiency.

Doman data outside the index database, using relatively few

Physical classification

The B-tree:normal or reverse key B-tree index is also an index that we have traditionally understood, and can be divided into normal and reverse indexes.

Bitmap: The bitmap is cited, the following will be said

B-tree Index

B-tree index is also one of the most commonly understood indexes in our tradition. The B-tree (balance tree) is a balanced one, with a relative balance of about two branches.

B-tree Index

Root is the root node, branch is the branch node, and the leaf to the bottom layer is called the leaf node. Each node represents a layer, and when a data is looked up, the root node is read, the node is read, and the leaf node is finally found. The leaf nodes hold index entry (index entry), and each index entry corresponds to a record.

Part of Index entry:

Indexentry Entry Header stores some control information.

key Column length of a key

key Column value of a key

ROWID Pointer, pointing to a particular data

To create an index:

 User Login:sql> conn as1/as1connected. Create a table:sql> creates a tables Dex (ID int,sex char (1), name char (10));  Table created. Insert 1000 data into the table sql> begin 2 For I in 1..1000 3 loop 4 insert into DEX values (I, ' M ', ' Chongshi ');  5 end Loop;  6 commit;  7 End;        8/pl/sql procedure successfully completed. View table Records sql> select * from Dex; ID SE NAME--------------------------------
 ..... 
 991 m Chongshi 992 m Chongshi 993 m Chongshi 994 m Chongshi 995 m Chongshi 9 Chongshi m 997 m Chongshi 998 m Chongshi 999 m chongshi m chongshi1000 rows selected. Gen Build index:sql> CREATE index dex_idx1 on Dex (ID), index created. Note: Index the first column (ID) of the table. View created tables and indexes sql> select Object_name,object_type from User_objects;object_name object_type----------------                      ----------------------------------------------------------------DEX tabledex_idx1 INDEX 

The index is separated from the table and exists as a single individual, in addition to the ability to create an index from a single field, or to create an index from multiple columns. Oracle requires up to 32 columns to be created.

Sql> CREATE INDEX Dex_index2 on Dex (sex,name); index created. Sql>  Select Object_name,object_type from User_objects;object_name                           object_ TYPE--------------------------------------------------------------------------------DEX                                       tabledex_idx1                                 Indexdex_index2                               INDEX

Here's what you need to understand:

Write a book, only the chapter page is fixed and then set the directory, database index is the same, only the first insert good data, and then build the index. Then we insert and delete the contents of the database, and the index needs to change. However, the modification of the index is done automatically by Oracle.

The above chart can describe the structure of the index more clearly.

With the node record 0 to 50 data locations, branch nodes to split records 0 to 10 .... 42 to 50, the leaf node records the length and value of each data, and the pointer points to the specific data.

The last leaf section is a two-way link that is chained together in order to quickly lock a data range.

Such as:

Sql> SELECT * from Dex where id>23 and id<32;        ID SE NAME--------------------------------        chongshi m  Chongshi  27 m Chongshi        M Chongshi m Chongshi m Chongshi (m)        chongshi m  Chongshi8 Rows selected.

As the example found above, the index of the way to find the 23rd data, and then find the 32nd data, so that you can quickly lock the scope of a lookup, if each piece of data to start from the root node to find, then the efficiency is very low.

Bitmap index

Bitmap indexes are primarily created for columns of the same value. Take the national resident log in a list, suppose there are four fields: name, gender, age, and Social security number, age and gender two fields will produce many of the same values, gender only two values for men and women, age, 1 to 120 (assuming the maximum age of 120 years) value. So no matter whether a table has hundreds of millions of records, but according to the gender field to differentiate, there are only two values (male, female). Then the bitmap index is an index based on this attribute of the field.

Bitmap Index

From, as we can see, a leaf node (identified by a different color) represents a key, start rowid and end rowid Specify this type of retrieval range, and a leaf node marks a unique bitmap value. Because a numeric type corresponds to a node, when the row is queried, the bitmap index obtains the result set vector (the computed result) by the bitwise operation (and OR) of the values directly from the different bitmaps.

For example:

Suppose there is a data table T, there are two columns A and B, the values are as follows, and we see that the same data exists in columns A and B.

Create a bitmap index on two data columns A, B, respectively: Idx_t_bita and IDX_T_BITB. The storage logical structure for the two indexes is as follows:

Idx_t_bita index structure, corresponding to the leaf node:

IDX_T_BITB index structure, corresponding to the leaf node:

To the query "select * from T where b=1 and (a= ' L ' or a= ' M ')"

Analysis : The Use of bitmap indexing, and the b* index is very different. The use of the b* index is usually started from the root node, and is compared to the nearest eligible leaf node through the Continuous branch node. "Scan" The result set ROWID through the continuous scan operation on the leaf node.

Bitmap indexing works in a very different way. A direct bitwise operation (with OR) of different bitmap values to obtain the result set vector (computed result).

For instance SQL, you can split it into the following actions:

1, a= ' L ' or a= ' M '

A=l: vector: 1010

A=m: vector: 0001

The result of an OR operation is two vectors or operations: The result is 1011.

2, the combination of b=1 vector

Intermediate result vector: 1011

B=1: vector: 1001

The result of the and operation, 1001. The first and fourth lines are the results of the query.

3. Get the result rowID

We now know the starting rowid and terminating rowID, as well as the first and fourth behavioral operation results. The result set rowID can be obtained by the method of trial calculation.

Features of bitmap indexing :

Storage space savings for 1.Bitmap indexes

2.Bitmap index creation speed is fast

3.Bitmap index allows null key value

4.Bitmap index efficient access to table records

To create a bitmap index:

View table Records sql> SELECT * from Dex, ...        ..... ..... ID SEX NAME--------------------------------       991 m  chongshi       992 m  chongshi       993 G  Chongshi       994 g  chongshi       995 g  Chongshi       996 M  chongshi       997 g  Chongshi       998 g  Chongshi       999 G  chongshi      M  chongshi1000 rows Selected. For the above table, there are only two values for sex (gender), which is best used to create an index created by a bitmap:sql> create bitmap index my_bit_idx on dex (sex); index Created. View created cited sql>  select Object_name,object_type from User_objects;object_name                           object_ TYPE--------------------------------------------------------------------------------my_bit_idx                               INDEX

Some rules for creating indexes

1. The relationship between the number of indexes and DML is weighed, and DML is the insertion and deletion of data operations.

There is a need to weigh a problem, the purpose of indexing is to improve the efficiency of the query, but the establishment of too many indexes, will affect the speed of inserting and deleting data, because we modify the table data, the index will also be modified. Here we need to weigh whether our operation is to query more or modify more.

2, put the index and the corresponding table in a different table space.

When a table is read, the table is in parallel with the index. If the table and index and in a table space will generate resource competition, placed in two tables this empty can be executed in parallel.

3, the best use of the same size is a block.

Oracle defaults to five blocks, read I/O, and if you define 6 blocks or 10 blocks you need to read two I/O. It is better to have an integer multiplier of 5 to improve efficiency.

4. If a table is large, indexing takes a long time because indexing also generates a large amount of redo information, so you can set up redo information when you create an index without producing or producing less. As long as the table data exists, the index fails to build again, so it is not necessary to generate redo information.

5, when the index should be built according to the specific business SQL to create, especially where conditions, as well as the order of where conditions, as far as possible to filter a wide range of placed in the back, because the SQL execution is from the back forward. (Xiao Li flying chopper)

Indexing common operations

To change the index :

sql> ALTER index Employees_last _NAME_IDX storage (next 400K maxextents 100);

After the index is created, it does not feel reasonable, and its parameters can be modified. Read more about the documentation

To adjust the space of an index:

New added Space sql> ALTER index ORDERS_REGION_ID_IDX allocate extent (size 200K datafile '/disk6/index01.dbf '); free space sql> Alter index ORAERS_ID_IDX DEALLOCATE unused;

There may be insufficient space or wasted space in the process of using the index, which requires new or free space. The above two commands complete the new and release operations. New Oracle on space can help automatically, and if you understand the database, manual additions can improve performance.

To re-create the index :

The citation is done automatically by Oracle, and when we operate on the database frequently, the index is also modified, and when we delete a record in the database, the corresponding index does not just make a delete tag, but it still occupies space. The entire block of space is freed unless all the tags in a block are completely deleted. With this time, the performance of the index will decrease. This time you can re-establish a clean index to improve efficiency.

sql> ALTER index ORDERS_REGION_ID_IDX rebuild tablespace index02;

With the above command, you can reproduce the process of building an index that Oracle re-establishes:

1, lock the table, the lock table after the other people can not do anything to the table.

2. Create a new (clean) temporary index.

3, delete the old index

4. Rename the new index to the name of the old index

5. Unlock the table.

Mobile cited by :

In fact, we also use the command above to move the index to other table spaces, specifying a different tablespace when specifying a table space. The new index is created in the other position, the old to kill, the equivalent of moving.

sql> ALTER index ORDERS_REGION_ID_IDX rebuild tablespace index03;

To re-create an index online :

As described above, when creating an index, the table is locked and cannot be used. For a large table, it takes a long time to re-create the index, and in order to meet the needs of the user for table operations, the resulting online re-creation of the index.

sql> ALTER index ORDERS_ID_IDX  rebuild  online;

To create a process:

1, Lock the table

2. Create temporary and empty indexes and IoT tables used to present on-going DML. The key value stored by the ordinary table, the data in the table directly stored by the IoT, On-gong DML is also the user to do some additions and deletions of the operation.

3. Unlock the table

4. Create a new index from the old index.

5. The IoT table holds on-going DML information, and the contents of the IoT tables are merged with the newly created indexes.

6, Lock the table

7. Update the contents of the IoT table to the new index again, and kill the old index.

8. Rename the new index to the name of the old index

9. Unlock the table

Consolidate index Fragmentation :

For example, there is space left in many indexes, and the remaining space can be combined by a single command.

sql> ALTER index ORDERS_ID_IDX  COALESCE;

To delete an index :

Sql> Drop  index  hr.departments_name_idx;

Analysis Index

　　

Check the results cited, the previous introduction, the long time of the cable reference will produce a lot of debris, garbage information and waste of the remaining space. You can improve the performance cited by re-creating indexes.

The analysis index can be completed by a single command, and the results of the analysis are stored in the Index_stats table.

View the table that holds the analysis data:sql> select COUNT (*) from Index_stats;  COUNT (*)----------         0 Perform the parse index command:sql> analyze index MY_BIT_IDX validate Structure;index analyzed. View Index_stats again There is already a data sql> select COUNT (*) from Index_stats;  COUNT (*)----------         1 data query:sql> select Height,name,lf_rows,lf_blks,del_lf_rows from Index_stats;    HEIGHT   NAME              lf_rows   lf_blks   del_lf_rows------------------------------------------------------- ----------------------------------------------         2   my_bit_idx          3            100  

Analysis Data Analysis :

(height) The cited height is 2, the (name) index is named My_bit_idx, (lf_rows) the cited table has 1000 rows of data, (Lf_blks) occupies 3 blocks, (del_lf_rows) deletes 100 records.

Here also verify that the above mentioned a problem, delete the 100 data is only marked for deletion, because the total number of data bar is still 1000, occupy 3 blocks, then each block is greater than 333 records, only the deleted data is more than 333 records, when a block is emptied, the total number of data bars will be reduced.

Oracle Database Index