Some learning records about Oracle database indexing

1, the characteristics of the index
A, a large table with read-only me or less insert, update, or delete operations can improve query speed.
b, you can index one or more columns of a table.
C. There is no limit to the number of indexed builds.
D, the index requires disk storage and requires Oracle automatic maintenance.
E, the index is transparent to the user, and whether the index is used is determined by Oracle.

2. The basic method of RDBMS accessing data
A, full table scan
b, through the ROWID
c, using the index

3. Increasing the index will bring down the performance of the INSERT statement.

4. Scan type of index
A, index unique scan (index unique scan)
B, index range scanning (index ranges scan)
C, index full scan
D, index fast Scan (index fast full scan)

5, limit the use of the index situation
1), using the Not equals operator (<>,! =), using the Not equal condition in where will invalidate the index.
2), using is null or is not NULL
3), using functions, some common functions such as trunc substr to_date To_char instr and so on.
4), comparing data types that do not match.

6. View the Execution plan example
Sql>explain plan for SELECT * from EMP;
Sql>select * FROM table (dbms_xplan.display);


7. Building an index example
1), CREATE INDEX Table space
sql> Create tablespace index_tbs datafile '/u1/index/index_tbs1.dbf '
Size 100M autoextend on;
2), build the index
Sql> CREATE INDEX Emp_ename_sal_idx
On EMP (ename,sal)
Tablespace Index_tbs;
3), build index full statement
sql>create [Unique | bitmap] index [schema.] Index_name
on [schema.] table_name
(column_name [desc | asc],column_name[desc | asc])
[Reverse] ==> creating a reverse Index
[Tablespace Tablespace_name]
[Pctfree N] ==> pre-reserved spatial proportions in index blocks
[Initrans N] ==> the number of transactions allocated in each index block
[Maxtrans N] ==> The maximum number of transactions per index block
[Instorage State] ==> How section extent is allocated in the index
[Logging | nologging] ==> to record | Do not record index related actions
[Nosort] ==> do not sort by key values when creating an index

8. View User Index
Sql>select * from User_indexes;
Sql>select * from User_idx_columns;


9, the use of monitoring index
1), open index usage monitoring
Sql>alter index pk_emp monitoring usage;
2), turn off index usage monitoring
Sql>alter index pk_emp nomonitoring usage;
3. View Index Usage
Sql>select * from V$object_usage;

10. Example of rebuilding an index and migrating table spaces
Sql>alter Index Pk_emp
Rebuild
Tablespace INDEX_TBS1;
Rebuilding a site index does not affect the user's use of the index, but it cannot use the Del operation and DML operations.

11. Maintenance Index
1), manually increase the index disk space
Sql>alter Index Pk_emp
Allocate extent;
2), merge index fragmentation
Sql>alter index pk_emp coalesce;

12. Delete Index
Sql>drop Index Pk_emp

This article is from the "Owenzou" blog, make sure to keep this source http://owenzou.blog.51cto.com/1392373/1664243

Some learning records about Oracle database indexing