Oracle index (notes)

Oracle index (notes)

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