Oracle Management Index (III) Oracle indexing

1. Establish B-tree Index

(1) Related concepts

Root block (one): Index top-level block that contains information that points to the next level of node (branch block or leaf block).

Branch BLOCK: It contains information that points to the next level of nodes (branch blocks or leaf blocks).

Ye: It contains index entry data, index entry contains indexed column values or restricted ROWID

(2) Index creation

If you want to frequently refer to a column or a few columns in the WHERE clause, you should give these column values a b-* tree index

10:23:58 sql> CREATE index ind_ename on scott.emp (ename) Pctfree 30

10:24:32 2 tablespace indexes;

Index created.

(3) Use index

10:24:41 sql> set Autotrace on explain

10:26:54 sql> SELECT * from scott.emp where ename= ' Scott ';

EMPNO ename JOB hiredate SAL COMM DEPTNO

---------- ---------- --------- ------------------- ---------- ---------- ----------

7788 SCOTT ANALYST 1987-04-19 00:00:00 3000 20

Execution Plan

----------------------------------------------------------

Plan Hash value:48385638

-----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

-----------------------------------------------------------------------------------------

| 0 |           SELECT STATEMENT |     |    1 |     29 | 2 (0) | 00:00:01 |

|  1 | TABLE ACCESS by INDEX rowid|     EMP |    1 |     29 | 2 (0) | 00:00:01 |

|* 2 | INDEX RANGE SCAN |     Ind_ename |       1 |     | 1 (0) | 00:00:01 |

-----------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("ename" = ' SCOTT ')

2. Set up bitmap index

Bitmap indexing identifies index data as a bit value and is used primarily to perform data statistics, data aggregation, and so on in the DSS (Decision Support System) systems.

The b*-Tree index is built on columns with few duplicate values, and bitmap indexes are built on columns with a lot of duplicate values and relatively fixed values.

Using a bitmap index can save a lot of disk space and 1/20~1/10 the space used to index the b*-tree.

When you create a bitmap index, Oracle creates a bitmap based on each of the different values.

(1) Create a bitmap index

Sql> Create bitmap index test_sex_bitind on test (sex) tablespace indexes;

Index created.

(2) Analysis of index structure

Sql> Analyze index test_sex_bitind validate structure;

Index analyzed.

Sql> Select Index_name,index_type,tablespace_name,blevel,leaf_blocks,num_rows from user_indexes where index_name= ' Test_sex_bitind ';

Index_name index_type tablespace_name blevel leaf_blocks num_rows

--------------- --------------- ---------------- ------- ----------- ----------

Test_sex_bitind BITMAP INDEXES 0 1 2

--the index that is appropriate to establish bitmap on a column with high duplicate values

(3) using a bitmap index

Sql> Select/*+ Index (Test test_sex_bitind) * * name,sex from Test where sex= ' F ';

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan Hash value:2624764158

------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

------------------------------------------------------------------------------------------------

| 0 |                 SELECT STATEMENT | |    10000 |    97k| 85 (0) | 00:00:02 |

|  1 | TABLE ACCESS by INDEX ROWID | TEST |    10000 |    97k| 85 (0) | 00:00:02 |

|   2 |                 BITMAP conversion to rowids|       |       |            |          | |

|* 3 | BITMAP INDEX Single VALUE |       Test_sex_bitind |       |            |          | |

------------------------------------------------------------------------------------------------

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/