Oracle Management Index (II) Oracle Management index Basic concepts

1, the concept of the index

An index is a database object that is used to speed up data access. Reasonable use of indexes can greatly reduce I/o times.

2, the function of the index: The record is sorted, speed up the query speed of the table.

3, the classification of the index:

(1) Single-column Index and compound index

A single-column index is an index that is based on a single column, and a composite index based on two or more columns.

Sql>create INDEX emp_idx1 on EMP (ename, job);

Sql>create INDEX emp_idx2 on EMP (Job, ename);

You can set up different indexes on the same column, and the column order is inconsistent.

(2) Unique index and non-uniqueness Index

A unique index is an index that cannot be duplicated by an indexed column value, or an index that can be duplicated when the index column value is not a unique index.

The index allows NULL, whether it is a unique index or a non unique index;

When a primary key constraint or a unique constraint is defined, Oracle automatically establishes a unique index on the corresponding constraint column.

(3) B-tree Index and bitmap index

(a) B-tree index (default)

A, on a large table

B, based on a column with fewer duplicate values, the number of rows returned is less than 4% of all records when a select query is made

C, if this column is often used to make a WHERE clause and sort, it can also be used to create an index

D, generally used for OLTP

(b) Bitmap index

A, based on a column with a very high repeat value

b, when doing DML operation, the cost is higher

C, generally used in OLAP or DSS

Note: The B-tree index cannot use the statement connected by or, bitmap index can use the

4, establish the index: default set up B-tree index

5. Set up Index Table space

It is recommended that you create a special index table space to store indexes for easy management.

Sql> Conn/as SYSDBA

Connected.

sql> Create tablespace indexes

2 datafile '/u01/app/oracle/oradata/anny/index01.dbf ' size 100m

3 Autoextend on next 10m maxsize 500m

4 extent management local uniform size 128k;

Tablespace created.

Sql> select File_id,file_name,tablespace_name from Dba_data_files;

file_id file_name Tablespace_name

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

5/U01/APP/ORACLE/ORADATA/ANNY/TEXT01.DBF TEXT

4/U01/APP/ORACLE/ORADATA/ANNY/USER01.DBF USERS

3/U01/APP/ORACLE/ORADATA/ANNY/SYSAUX01.DBF Sysaux

2/U01/APP/ORACLE/ORADATA/ANNY/LX02.DBF LX02

1/U01/APP/ORACLE/ORADATA/ANNY/SYSTEM01.DBF SYSTEM

6/U01/APP/ORACLE/ORADATA/ANNY/LX01.DBF LX01

7/U01/APP/ORACLE/ORADATA/ANNY/UNDOTBS01.DBF Undotbs

8/U01/APP/ORACLE/ORADATA/ANNY/LX03.DBF LX03

9/U01/APP/ORACLE/ORADATA/ANNY/LX04.DBF LX04

10/U01/APP/ORACLE/ORADATA/ANNY/INDEX01.DBF INDEXES

Ten rows selected.

6. Index-related views

Dba_indexes

Dba_ind_columns

V$object_usage

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