Oracle Management Index (III) Oracle indexing

Source: Internet
Author: User
Tags create index hash

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/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.