Oracle index type

Index category
One Binary tree Index, or B-tree indexes, is the most widely used index. by default, all indexes we create are B-tree indexes. b-tree index based on binary tree principles

2. Binary tree clustering index (B-tree Cluster indexes) is mainly used for clustering

3. Hash Cluster indexes are mainly used for Hash clustering.

4. The Reverse index (Reverse Key indexes) also belongs to the B-tree index, which reverses the index value by byte.

5. bitmap indexes are used to manage indexes. Bitmap indexes are suitable for columns with few unique values, that is, Bitmap joins indexes with many duplicate values) used to connect two tables

6. Function-Based indexes: If a Function or expression is often used in the where clause of SQL statements, you can create a Function-Based index.

Create an index
Create index idx_emp1_ename on emp1 (ename );

Create a unique index
Create unique index idx_uq_emp1_empno on emp1 (empno) tablespace mypl;

Create a bitmap Index
Create bitmap index idx_bm_emp1_deptno on emp1 (deptno );

Create reverse Index
Create index idx_reverse_emp1_ename on emp1 (empno );

Create a function index
Create index idx_funs_emp1_ename on emp1 (upper (ename ));

Re-create index replace index tablespace
Alter index idx_reverse_emp1_ename rebuild;
Alter index idx_reverse_emp1_ename rebuild tablespace mypl;

Delete Index
Drop index idx_reverse_emp1_ename;

View users' Indexes
Select INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE from user_indexes;

View the tablespace of the Index
Select index_name, tablespace_name from dba_indexes;

View index statistics after analyzing Indexes
Analyze index idx_funs_emp1_ename validate structure;

Select height, (DEL_LF_ROWS_LEN/lf_rows_len) * 100, blocks, BTREE_SPACE, USED_SPACE from index_stats;

If (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 is greater than 20 or heighr is greater than 4, you need to consider re-indexing.

(Index_stats stores the index statistics. DEL_LF_ROWS indicates the number of rows to be deleted. LF_ROWS indicates the total number of rows, and height indicates the level of the binary tree from the root to the leaf block)