Oracle index, index creation, modification, deletion

Index, index creation, modification, deletion
2007-10-05 13:29 Source: User reviews 0 Views 2986
Index
An index is an object in a relational database that is used to hold each record, and the main purpose is to speed up the reading speed and integrity checks of the data. Indexing is a highly technical and demanding task. Generally in the database design phase with the database structure to consider. The performance of the application system is directly related to the rational index. Here are the methods and the key points to build the index.
§3.5.1 Building an Index
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 table in which the index was created
column names in the columns base table, with an index of up to 16 columns, a long column, a long raw
Column cannot be indexed column
DESC, ASC default ASC is ascending sort
CLUSTER Specifies a cluster (Hash CLUSTER cannot be indexed)
Initrans, Maxtrans Specify the initial and maximum number of transaction entries
Tablespace Table Space Name
STORAGE storage parameters, same as STORAGE in CREATE table.
PCTFREE Percentage of free space for index blocks (cannot specify pctused)
Nosort not (CAN) sort (it is stored in ascending order, so the point is no longer sorted)

2. Purpose of Indexing:

The index is built 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 slowed by the time the table is stored in the index entry.
Note: A base table cannot build too many indexes;
Null value cannot be indexed
Only the unique index really increases the speed, the average index can only increase by about 30%.

Create index ename_in on EMP (ename,sal);

Example 1: The Commodity library table structure of the mall is as follows, we establish a unique index for the product Code of the table, which makes the query speed increase when receiving POS at the foreground.
Create table Good (good_id number (8) Not null,/* product 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 the primary key and the unique index
Is that the 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 Modifying an index
For older versions of Oracle, the primary task for modifying an index is to modify the storage parameters of an existing index to meet the need for growth or to re-establish the index. In the Oracle8i and later versions, you can merge unused space. These tasks are mainly done by the administrator.

The brief syntax structure is as follows, and more detailed syntax is shown in the electronic document "Oracle8i Reference" in the Alter index.

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

which
REBUILD is re-indexed based on the original index structure, actually deleting the original index before re-establishing it.

Tip: DBAs often use REBUILD to rebuild indexes to reduce hard disk fragmentation and improve application performance.

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

ALTER INDEX Emp_ix REBUILD REVERSE;

The new functionality of oracle8i can be used to 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 Deleting an index
The index can be deleted to free up hard disk space when it is not needed. The command is as follows:

DROP INDEX [schema.] IndexName

For example:

sql> DROP Index pk_dept;

Note: When the table structure is deleted, all of its associated indexes are also deleted.

§3.6 New Index Type
Oracle8i provides new indexes for creating new types for performance optimizations. These new indexes are described below:

§3.6.1 Function-based indexes
A function-based index is an index that stores a pre-computed function or expression value. These expressions can be an arithmetic expression, an SQL or a/PL function, a C call, and so on. It is worth noting that the general user to create the function index, must have the global QUERY rewrite and create any index permission. Otherwise, you cannot create a function index, see the following example:

Example 1: Index of the uppercase conversion function for the ename column of the EMP table idx:

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

This allows you to use the following query statements:

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

Example 2: Index The sum of the EMP's wages and bonuses:
1) View the table structure of the EMP:
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 a 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/[email protected]
Connected.
Sql> Grant GLOBAL QUERY REWRITE to Scott;

Grant succeeded.

Sql> Grant CREATE any INDEX to Scott;

Grant succeeded.

4) When connecting to the Scott account, create a function-based index:

Sql> Connect Scott/[email protected]
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 inverse key index keeps the insertion distribution on all the leaf keys indexed by the inverse key. Sometimes, a reverse key index can be used 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 rebuild Noreverse or rebuild reverse clauses after the ALTER INDEX command;
L The query using range Scan cannot use reverse key index;
L Bitmap index cannot be reversed;
L Indexing table cannot be reversed.

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

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

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

The CREATE index structure table is also implemented with the CREATE TABLE command plus the organization index keyword. However, after such a table is created, 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 build clusters;
L cannot contain a long type column;
L Do not support distribution and replication.
Tip: If you have an indexed organization table, information about the indexed organization table is recorded on the Iot_type and Iot_name columns in Dba_tables.

Example 1. Modify the Initrans parameter for the index segment of the index structure table Docindex:

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 the overflow data segment of the Index organization table Docindex:

ALTER TABLE docindex OVERFLOW Initrans 4;

============================================================================================================
Proper use of indexes can improve data retrieval speed and can create indexes on fields that frequently require queries

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 method January 04, 2008 Friday 13:20 View index number and category

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

View the fields indexed by the 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;

Oracle index, index creation, modification, deletion