//
you're reading...
Oracle Database Performance Tuning

Oracle Database Index – Basic

Index is a relational database table for storing a record position of each object, the main purpose is to speed up the data read speed and data integrity checks. Indexing is a very demanding technical work.

General database design phase must take into account how to design and create the index.

Create an index

The syntax for creating an index:

CREATE [UNIQUE] INDEX [schema.] Index
ON [schema.] Table (column [ASC | DESC], column [ASC | DESC] ...)
[CLUSTER schema.cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]

Keyword Description:

  • UNIQUE: This parameter indicates the index created for the unique index.
  • CLUSTER: This parameter is optional, is used to specify a clustered (Hash cluster can not create an index).
  • INITRANS, MAXTRANS: optional parameter specifying the initial and maximum number of entries affairs.
  • TABLESPACE: index storage table space.
  • STORAGE: storage parameters.
  • PCTFREE: index data block idle percentage.
  • NO SORT: no sorting (when storing it in ascending order, so here that no sorting).

Example: create a student information table (tb_std), create an index for the std_id column of the table, so that when the column is used to improve query efficiency.

create table tb_std 
(  
	  std_id              number  
	, std_name            varchar2(50)  
	, std_type            varchar2(30)  
	, std_class           varchar2(100)  
	, std_contact         number(10,4)  
);  

The following code is used to create a unique index on the std_id column:

create unique index product_id_u1 on tb_std(std_id);

Modify Index

Modify the index is mainly done by the database administrator, modify index mainly related to the modified index storage parameters, rebuild the index, the index of useless space merge.

Modify the index syntax:

ALTER [UNIQUE] INDEX [user.] index  
INITRANS n  
MAXTRANS n  
REBUILD   
[STORAGE ]  

Explanation:

  • INITRANS n: indicates the number of entries initial transaction accessed simultaneously within a block, n is the decimal integer.
  • MAXTRANS n: indicates the entrance of the maximum number of transactions simultaneously access a block, n is the decimal integer.
  • REBUILD: indicates re-index index based on the original structure, which is again on the table full table scan data to create the index later.
  • STORAGE : indicates the stored data.

Use ALTER INDEX statement to modify the index parameters:

ALTER INDEX std_id_indx1 REBUILD STORAGE  ( INITIAL 1M NEXT 512K );

Use ALTER INDEX statement to modify the index to reverse the index:

ALTER INDEX std_id_indx1 REBUILD REVERSE;  

Use ALTER INDEX statement consolidated index space:

ALTER INDEX std_id_indx1 COALESCE;  

Delete Index

You can use the DROP statement to remove the index.

DROP INDEX schema.index;

Note: If the table structure is deleted, the index associated with the table are also deleted

Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: