An explanation of the indexes in Oracle

First, the concept of ROWID

Stores the location of row in the data file: 64-bit encoded data, A-Z, A-Z, 0-9, +, and/,

How row is stored in the data block

SELECT ROWID, last_name from hr.employees WHERE department_id = 20;

For example: Oooooofffbbbbbbrrr

Oooooo:data object number, corresponding to dba_objects.data_object_id

fff:file#, corresponding v$datafile.file#

bbbbbb:block#

rrr:row#

Dbms_rowid Bag

SELECT dbms_rowid.rowid_block_number (' aaagfqaabaaaiweaaa ') from dual;

Specific to a specific physical file

Second, the concept of the index

1. Directory Structure of similar books

2. Oracle's Index object, an optional object associated with the table, to increase the speed of SQL query statements

3. The index directly points to the location of the row containing the queried value, reducing disk I/O

4. The indexed tables are independent of each other's physical structure

5, Oracle automatically use and maintain the index, INSERT, delete, update the table, automatically update the index

6. Syntax: CREATE index index on table (column[, column] ...);

7, B-tree structure (non-bitmap):

[i] understand how the index works:

Table: EMP

Objective: To inquire about Frank's salary salary

Build index: CREATE INDEX EMP_NAME_IDX on EMP (name);

[Test] test the role of the index:

1. Run the/rdbms/admin/utlxplan script

2. Create a test table

CREATE TABLE T as select * from Dba_objects;

INSERT INTO t select * from T;

CREATE TABLE Indextable

As select RowNum Id,owner,object_name,subobject_name,

object_id,data_object_id,object_type,created

From T;

3. Set Autotrace Trace explain

4. Set timing on

5. Analysis of the table, you can get cost

6. Query object_name= ' dba_indexes '

7. Indexing on the object_name column

8. Re-search

[Thinking] The cost of the index:

Insert, update

Third, unique index

1, when to create: when the values of any two rows in a column are not the same

2, when establishing primary key (primary key) or UNIQUE constraint (unique constraint), the unique index will be automatically established

3. Syntax: CREATE UNIQUE index index on table (column);

4. Demo

Iv. Combined Index

1. When to create: when two or more columns often appear together in the Where condition, the combined index is created on those columns at the same time

2. The order of the columns in the combined index is arbitrary and does not need to be adjacent. However, it is recommended that the most frequently accessed columns be placed at the front of the list

3. Demo (combined column, separate column)

Five, bitmap index

1. When to create:

There are very many duplicate values in the column. For example, a column holds "gender" information.

Many or operators are included in the Where condition.

Fewer update operations, because it is necessary to follow all the new bitmap

2. Structure: Bitmap indexes use bitmaps as key values, and each data row bitmap in the table contains True (1), FALSE (0), or null values.

3. Advantages: Bitmaps are stored in a compressed format, so the disk space consumed is much smaller than the standard index

4. Syntax: CREATE BITMAP index Index on table (column[, column] ...);

5. Disguise:

CREATE TABLE bitmaptable as SELECT * from Indextable where owner in (' SYS ', ' public ');

Analyze, find, index, look up

Vi. Function-based indexing

1. When to create: when a function or an expression is included in a WHERE condition statement

2, Function includes: arithmetic expression, PL/SQL function, package function, the function of a user, and a custom function.

3. Syntax: CREATE index index on table (FUNCTION (column));

4. Demo

The table must be parsed and query_rewrite_enabled=true

Or use the Hint/*+ INDEX (ic_index) */

Seven, reverse key index

Purpose: For example, the index value is an auto-growing column:

Multiple users make modifications to the index rows that are concentrated on a few blocks, which can easily cause contention for resources, such as waiting on blocks of data. The reverse index is established at this time.

Performance issues:

Grammar:

Rebuild to Standard index: not the reverse.

Eight, key compression index

For example, table LANDSCP data is as follows:

Site Feature Job

Britten Park, Rose Bed 1, prune

Britten Park, Rose Bed 1, mulch

Britten Park, Rose Bed 1,spray

Britten Park, shrub Bed 1, mulch

Britten Park, shrub Bed 1, Weed

Britten Park, shrub Bed 1, Hoe

......

When querying, the above 3 columns are both present in the Where condition, so a combined index based on the above 3 columns is established. However, there are many duplicate values, so consider the compression feature.

Create Index Zip_idx

On LANDSCP (site, feature, job)

Compress 2;

Divides the index entry into two parts: prefix (prefix) and suffix (postfix). The first two items are placed in the prefix section.

Prefix 0:britten Park, Rose Bed 1

Prefix 1:britten Park, shrub Bed 1

The actual structure is:

0 Prune

0 Mulch

0 Spray

1 Mulch

1 Weed

1 Hoe

Feature: Consider using compression when the prefix portion of a composite index is non-selective. Reduce I/O and increase performance.

Ix. Index Organization table (IOT)

The data in the table is stored in the index by the structure of the index , increasing the query speed.

Sacrifice the performance of insert updates in exchange for query performance. Typically used in data warehouses, providing a large number of queries, with minimal insert modification work.

You must specify a primary key. When data is inserted, the B-tree index is sorted according to the primary key column and written to disk.

X. Partition index

Cluster:

A cluster is a group of tables this share the same data blocks because they share common columns and is often used Togeth Er.

Reprint: http://www.oschina.net/question/30362_4057Sort By default display of latest comments 2 Comments (last reply: 1 months ago)

An explanation of the indexes in Oracle