Oracle database Objects-Index learning notes

First, the concept of the index

Index is similar to look up a dictionary, through pinyin, strokes, only a few steps can find the word. Database, through the index, as long as a small amount of reading, you can find the required records.

Using an index requires finding a balance, and too many indexes can affect the speed of a DML statement because you want to write an index table. Too few indexes can improve query speed.

Second, b* Tree index


1, b* Tree index: What we call the "traditional" index. The index created by create index defaults to the b* index.

The index of b* is similar to a binary tree, but not a binary tree. "B" in the b* tree does not represent a two-fork (binary) and represents a balance (balanced).

One of the characteristics of the b* tree is that all leaf blocks should be on the same floor of the tree.

This layer is also called the height of the index, which means that all traversal from the root block of the index to the leaf block accesses the same number of blocks. That is, for an index that is shaped like "SELECT indexed_col from T WHERE indexed_col =: X", the same number of I/O will be performed to get the first row of the leaf block, regardless of what X value is used.

In other words, the index is highly balanced (height balanced). Most b* tree indexes have a height of 2 or 3, even if there are millions of rows of records in the index. This means that in general, a key is only required to perform 2 or 3 times I/O in the index.

2, Reverse key index

Reverse key index to mitigate problems in the b* tree index where the data is concentrated in certain leaf blocks, the right index problem.

In general, when using data (often in bulk inserts) are more concentrated in a contiguous range of data, it is very easy to index leaf block overheating when using a normal index, which will result in degraded system performance.

Using a reverse index, the distribution of index key values in the index leaf block can be dispersed, and the hotspot blocks are reduced to improve the system performance.

Statement:

CREATE table t (x int);

CREATE index idx_t on t (x) reverse;

Plus the "reverse" keyword, you create a reverse key index.

3, descending index

A descending index is used to extend the functionality of the B* Tree Index, which allows a column to be stored in descending order (from large to small) in the index.

CREATE index idx_t on t (x desc);

With the "desc" keyword, a descending index is created.

Third, bitmap index

Bitmap indexing is a binary 0, a way to build the index, when the or operation is very fast. When a bitmap index is created, Oracle scans the entire table and creates a bitmap for each value of the indexed column. (same as the same as the same chart)

When a table has a large amount of data and its key columns cardinality (a different value for the keyword), the query often uses the WHERE clause of OR, and so on, with less modification to the index keyword, it is advisable to use a bitmap index.

When a key changes in a bitmap index (update), it will cause bitmap changes, which will lock all rows of the same bitmap. Bitmap indexing is particularly inappropriate for OLTP systems because bitmap indexing, changing a record, locks all records of the same value, and this system is not suitable for bitmap indexing if the data in the system is frequently updated by multiple concurrent sessions.

Inserts, like normal tables, do not lock records with the same value, and add a transaction lock with a lower level.

Statement:

Create bitmap index idx_t on t (x);

If a row, the number of different items in a column divided by the number of rows, is a very small number, may be indexed with a bitmap.

Frequently read systems are suitable for bitmap indexing, and frequently written systems are not suitable for bitmap indexing.

Four, function index

If you use functions frequently on a column, you can use a function-based index. It is equivalent to index the result of the function calculation, when Oracle calculates the result of the function obtained in the condition, does not need to compute the function for each row, speeds up the query speed

Oracle index, index creation, modification, deletion

Index

An index is an object in a relational database that is used to store each record, with the main purpose of speeding up the reading speed and integrity of the data. Indexing is a highly technical task. Generally in the database design phase with the database structure to consider. The performance of the application system is directly related to the rationality of the index. The following are the methods and key points for indexing.
§3.5.1 Index Creation
1. CREATE Index Command syntax:

CREATE INDEX
CREATE [Unique] INDEX [user.] Index
On [user.] Table (column [ASC | DESC] [, column
[ASC | DESC]]
[CLUSTER [Scheam.] Cluster
[Initrans N]
[Maxtrans N]
[PCTFREE N]
[STORAGE STORAGE]
[Tablespace tablespace]
[NO SORT]
Advanced

which
Schema Oracle mode, default is current account
Index name
Table name of the base tables where the index is created
Column base table, one index has up to 16 columns, long, long raw
Column cannot build indexed columns
DESC, ASC default to ASC-ascending sort
CLUSTER Specifies a cluster (Hash CLUSTER cannot be indexed)
Initrans, Maxtrans specify initial and maximum transaction entry number
Tablespace Table Space Name
STORAGE the storage parameters with the STORAGE in the CREATE table.
PCTFREE the percentage of the free space of the indexed block of data (pctused not specified)
Nosort do not (CAN) sort (the store is in ascending order, so that it is no longer sorted)

2. The purpose of establishing an index:

The purpose of indexing is to:
l improve the query speed of the table;
L Check the values of the columns in the table.

However, when a table is insert,update,delete processed, some speed is reduced because the table's location is recorded in the index entry.
Note: A base table cannot build too many indexes;
Null value cannot be indexed
Only a single index really improves speed, and the average index can only improve by about 30%.

Create index ename_in on EMP (ename,sal);

Example 1: The Commodity library table structure of the shopping mall is as follows, we set up a unique index for the commodity Code of the table, so that the query speed is increased when the Pos collection is received in the foreground.
Create table Good (good_id number (8) Not null,/* commodity barcode * *
Good_desc VARCHAR2 (40),//* Product Description * *
Unit_cost number (10,2)/* Unit Price * *
Good_unit VARCHAR2 (6),/* unit *
Unit_pric number (10,2)/* Retail Price * *
)；

Note: The way to improve query speed is to establish a primary key on the table, the difference between primary key and unique index
Is that a unique index can be empty, and the primary key is non-null, such as:

Create table Good (good_id number (8) primary key,
Good_desc Varchar2 (40),
Unit_cost number (10,2),
Good_unit Char (6),
Unit_pric Number (10,2)
);

§3.5.2 Modify Index
For older versions of Oracle, the primary task of modifying an index is to modify the storage parameters of an existing index to accommodate the need for growth or to re-establish the index. Oracle8i and later versions can be used to merge unused space. This work is done primarily by the administrator.

The brief grammatical structure is as follows, the more detailed syntax diagram see Alter Index in the electronic document "Oracle8i Reference".

ALTER [UNIQUE] INDEX [user.] Index
[Initrans N]
[Maxtrans N]
REBUILD
[STORAGE N]

which
REBUILD is based on the original index structure to re-establish the index, actually delete the original index and then re-establish it.

Tip: DBAs often use REBUILD to rebuild the index to reduce hard disk fragmentation and improve the performance of the application system.

Cases:
ALTER INDEX PK_DETNO rebuild storage (initial 1m next 512k);

ALTER INDEX Emp_ix REBUILD REVERSE;

The new features of Oracle8i can merge the unused space of the index, which is done by the following command:

ALTER INDEX ... Coalesce;

For example:

ALTER INDEX Ename_idx Coalesce;

§3.5.3 Delete Index
You can remove the index when it is not needed to free up hard disk space. The order is as follows:

DROP INDEX [schema.] IndexName

For example:

sql> DROP Index pk_dept;

Note: When the table structure is deleted, all indexes associated with it are deleted.

§3.6 New Index Type
Oracle8i provides a new index for creating new types for performance optimization. The new indexes are described below:

§3.6.1 index based on function
A function based index is an index that stores a predefined function or expression value. These expressions can be arithmetic operational expressions, SQL or Pl/sql functions, C invocations, and so on. It is noteworthy that the general user must have the global QUERY rewrite and create any index permissions to create a function index. Otherwise, you cannot create a function index, see the following example:

Example 1: The index of the uppercase conversion function is established for the ename column of the EMP table idx:

CREATE INDEX idx on EMP (UPPER (ename));

This allows you to use the query statement:

SELECT * from EMP WHERE UPPER (ename) is like ' joh% ';

Example 2: Create an index for the sum of the wages and bonuses of the EMP:
1 View the EMP table structure:
Sql> desc EMP
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO not NULL number (4)
Ename VARCHAR2 (10)
JOB VARCHAR2 (9)
MGR Number (4)
HireDate DATE
SAL Number (7,2)
COMM Number (7,2)
DEPTNO Number (2)

2 prompt to create function index without authorization:

Sql> CREATE INDEX Sal_comm on EMP ((Sal+comm) *12, Sal,comm)
2 Tablespace users Storage (initial 64k next 64k pctincrease 0);
CREATE INDEX Sal_comm on EMP ((Sal+comm) *12, Sal,comm)
*
ERROR at line 1:
Ora-01031:insufficient Privileges

3 Connect to the DBA account and authorize:

Sql> Connect sys/sys@ora816
Connected.
Sql> Grant GLOBAL QUERY REWRITE to Scott;

Grant succeeded.

Sql> Grant CREATE any INDEX to Scott;

Grant succeeded.

4 in connection to the Scott account, create a function based index:

Sql> Connect scott/tiger@ora816
Connected.
Sql> CREATE INDEX Sal_comm on EMP ((Sal+comm) *12, Sal,comm)
2 Tablespace users Storage (initial 64k next 64k pctincrease 0);

Index created.

1 Use the function index in the query:

Sql> Select Ename,sal,comm from emp where (Sal+comm) *12 >5000;

Ename SAL COMM
---------------------- ---------------- ----------------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
TURNER 1500 0
Zhao Yuanjie 1234.5 54321

§3.6.2 Reverse Key index
The reverse key index retains the insertion distribution on all the leaf keys of the index by the reverse key. Sometimes, you can use a reverse key index to avoid an unbalanced index. For a reverse key index, you can do the following:
L make the index edge a reverse key index or a normal index by adding the rebuild noreverse or rebuild reverse clause after the ALTER INDEX command;
L A query with a range scan cannot use a reverse key index;
L Bitmap index cannot be reversed;
L index tables cannot be reversed.

Example 1: Create a reverse key index:
CREATE INDEX i on t (a,b,c) REVERSE;

Example 2: Turn an index into a reverse key index:
ALTER INDEX i REBUILD noreverse;

§3.6.3 Index Organization table
Unlike normal indexes, the Index organization table (index_organized table) stores data based on tables, and indexes and tables are stored together. Such an index structure table (index_organized Table-iot) is characterized by changes to the table data, such as inserting a new row, and deleting a row that causes an update to the index.
An index organizes a table like a normal table with one or more columns, but the index organization table stores row data on the leaf node of the B-tree index structure. By storing the data in the index structure, the index organizes the table to reduce the total storage, in addition, the Index organization table also improves the access performance.
Because rows in a table are stored with the B_ tree index, each row has no rowid, but is identified by a primary key. But Oracle "guesses" the location of these rows and assigns logical rowid to each row. In addition, you can create a second index for such a table.

Creating an index structure table is also implemented with the CREATE TABLE command plus the organization index keyword. However, after the creation of such a table, you must also establish a primary key for the table.

Example:
CREATE TABLE Iot_expample
(
Pk_col1 Number (4),
Pk_col2 VARCHAR2 (10),
Non_pk_col1 VARCHAR2 (40),
Non_pk_col2 date,
CONSTRAINT Pk_iot PRIMARY KEY
(Pk_col1, Pk_col2)
)
Organization INDEX
Tablespace INDEX
STORAGE (INITIAL 1M NEXT 512K pctincrease 0);

There are some limitations to index organization tables:
L cannot use a unique constraint;
L must have a primary key;
L cannot establish a cluster;
L cannot contain a long type column;
L Do not support distribution and replication.
Tip: If an index organization table is established, information about the indexed organization table is recorded on the Iot_type and Iot_name columns in Dba_tables.

Example 1. To modify the Initrans parameters of an index segment DOCINDEX the index structure table:

ALTER TABLE Docindex Initrans 4;

Example 2. The following statement adds an overflow data segment to the Index organization table Docindex:

ALTER TABLE docindex ADD OVERFLOW;

Example 3. The following statement modifies the Initrans parameter for an overflow segment docindex the index organization table:

ALTER TABLE docindex OVERFLOW Initrans 4;

============================================================================================================
Proper use of indexes can improve the speed of data retrieval, and you can create indexes for fields that often need to be queried

Oracle's index is divided into 5 types: Unique index, combined index, reverse key index, bitmap index, function based index

Standard syntax for creating indexes:

CREATE index index name on table name (column name)

tablespace table space name;

To create a unique index:

CREATE Unique index index name on table name (column name)

tablespace table space name;

To create a composite index:

CREATE index index name on table name (column name 1, column name 2)

tablespace table space name;

To create a reverse key index:

CREATE index index name on table name (column name) reverse

tablespace table space name;

View Articles
Oracle View index categories and view indexed fields referenced field methods January 04, 2008 Friday 13:20 View the number and category of indexes

SELECT * from user_indexes where table= ' table name ';

To view the fields indexed by an index

Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');

Ps:

View constraints on a table

Sql>select constraint_name, Constraint_type,search_condition, R_constraint_name
From user_constraints WHERE table_name = UPPER (' &table_name ');

Sql>select C.constraint_name,c.constraint_type,cc.column_name
From User_constraints c,user_cons_columns cc
where C.owner = Upper (' &table_owner ') and c.table_name = Upper (' &table_name ')
and C.owner = Cc.owner and c.constraint_name = Cc.constraint_name
Order BY Cc.position;

View the name of the view

Sql>select view_name from User_views;