Detail Oracle Index (note)

1. Oracle Index NULL problem

When a single-column index is established on an empty merit column, if the search condition is null in the interpretation plan you can see that Oracle does not use index queries for this column;

However, when a multi-column index is established, the query is made according to the index.

2, B-Tree index

：

　　

In the B-tree index, it will be indexed in order from top to bottom. If the selection of columns is not low, the index scan will be slow. The reason is to traverse a lot of leaves to get the different row numbers.

As the number of access data increases, the right-most leaf block is also increasing, which may cause the buffer to be busy waiting. The fast growth of this type of rightmost index is known as the right growth index. Some workarounds are discussed later.

3. Bitmap indexing

Bitmap indexes are not suitable for tables that require a large number of DML operations (DML refers to SQL statements other than select). A data warehouse table that is suitable for read-only operations where most data has fewer unique columns.

One thing to note about bitmap indexing is that. Update a column with a bitmap index, you must update the bitmap index.

4. Partition index

4.1. Local Index

Local indexes are established using the local keyword.

CREATE INDEX index_name on table_name (column_name) local;

When the partition index is used, the content of the matching partition is looked up directly, instead of querying each partition.

4.2. Global Index

Global indexes are created with global

4.3. Hash partition

The regression to the right-side growth index as mentioned in the B-number index can be partitioned in a hash-partition manner.

In the same way that a range is partitioned, a hash partition distributes all the data evenly across different partitions. Here's how:

　　　　

--range partition CREATE table table2partition by range (year) (partition p_2012 values less than), partition p_2013 values less th An (in), partition p_2014 values less than, partition P_max values less than (MaxValue)) Asselect * from table1;--Hash Partition drop sequence sf;create sequence SF cache 200;drop table Table3;create table table3partition by hash (SID) Partitions 32as Select Sf.nextval sid,t.* from table1 t;--can be found in the following code for select Dbms_rowid.rowid_object (ROWID) obj_id,count (*) from Table3group by Dbms_rowid.rowid_object (ROWID);    obj_id   COUNT (*)--------------------     86232       4717     86236       4571     86240       4696     86257       4633     86234       4547     86235       4580     86241       4717     86249     4589       86250 4612     86251       4623     86261       4742     86238       4578......create Unique index index_table3_sid on Table3 (SID) Local;select * from Table3_sid where Sid =10000;--view its interpretation plan and can get

　　

5. Compression index

A compressed index is a variant of the B-tree index and is more appropriate for columns with duplicate values in the boot column

CREATE INDEX index_name on table_name (COLUMN1,COLUMN2,COLUMN3) Compress N;

where n is the first few items to compress. For example, n=2 is the two indexes of compressed column1,column2.

The compressed index applies to indexes with fewer unique values for the boot column.

6. Function-based indexing

Create CREATE INDEX INDEX_NAME on table_name (function_name (column1));

The select is required to add function_name (column1) to use the index, only column1, or full-table scan.

7. Reverse Key index

is also a way to solve the right growth index problem, but because the index is inverted, you cannot use the scope operator

CREATE INDEX index_name on table_name (column_name) global reverse;

Not commonly used, because it can cause some other negative effects.

Detail Oracle Index (note)