Oracle index (notes)

Oracle index null. When a single column index is created in an empty value column, if the search condition is isnull, it can be seen in the interpretation plan that oracle does not use this column.

Oracle index null. When a single column index is created in an empty value column, if the search condition is null, it can be seen in the interpretation plan that oracle does not use this column.

1. Oracle index null

When a single column index is created on an empty value column, if the search condition is null, it can be seen in the interpretation plan that oracle does not use index query for this column;

However, when multiple columns of indexes are created, the indexes are used for queries.

2. B-tree indexes

:

　　

In the B-tree index, the index is performed from top to bottom. If the column selection is not low, index scanning will be slow. The reason is that we need to traverse many leaves and fetch different row numbers.

As incoming and outgoing data increases, the rightmost leaf blocks keep increasing, which may lead to busy waiting in the buffer zone. The rapid growth of the rightmost index of this type is called the growth index on the right. Some solutions will be discussed later.

3. Bitmap Index

Bitmap indexes are not suitable for tables that require a large number of DML operations (DML refers to SQL statements other than select ). It is suitable for Data Warehouse tables that perform read-only operations on a few unique columns for most data.

The bitmap index must be noted. To update a column with a bitmap index, you must update the bitmap index.

4. Partition Index

4.1. Partial Index

LOCAL indexes are created using the LOCAL keyword.

Create index index_name on table_name (column_name) local;

When the partition index is used, the content of the matched partition is directly searched, instead of each partition.

4.2 global index

GLOBAL index is created using GLOBAL

4.3 hash partitions

Return to the growth index on the right mentioned in the B-number index, You can partition by hash partitioning.

Different from range partitioning, hash partitioning distributes all data evenly in different partitions. The specific method is as follows:

-- Range Partition

Create table table2

Partition by range (year)

(Partition p_2012 values less than (2013 ),

Partition p_2013 values less than (2014 ),

Partition p_2014 values less than (2015 ),

Partition p_max values less than (maxvalue)

)

As

Select * from table1;

-- Hash Partition

Drop sequence sf;

Create sequence sf cache 200;

Drop table table3;

Create table table3

Partition by hash (sid)

Partitions 32

As

Select sf. nextval sid, t. * from table1 t;

-- The following code can be found:

Select dbms_rowid.rowid_object (rowid) obj_id, count (*) from table3

Group 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 the explain Plan.

　

5. Compress Indexes

The compressed index is a variant of the B-tree index. It is more suitable for columns with duplicate values in the Guide column.

Create index index_name on table_name (column1, column2, column3) compress N;

N indicates the first few items to be compressed. For example, N = 2 is to compress the column1 and column2 indexes.

The compressed index applies to indexes with a small number of unique values in the bootstrap column.

6. function-based indexes

Create index index_name on table_name (function_name (column1 ));

In select, you must add function_name (column1) to use the index. If you only use column1, you can still scan the entire table.

7. Reverse Key Index

It is also a method to solve the Index Growth Problem on the right side, but the range operator is not available because the index is reversed.

Create index index_name on table_name (column_name) global reverse;

Not commonly used, because it may cause some negative effects.

Oracle Index instance description-Basics

Oracle | PL/SQL Unique index (Unique Constraint) Usage

Example of Oracle full-text index performance advantages

Restore non-critical Oracle files, redo, temporary files, index files, and password files

Oracle index tablespace data file loss and Reconstruction

Oracle implements function-based indexing

Oracle index Suppression

Oracle index reconstruction script