Oracle Index instance description-Basics

Oracle Index instance description-Basics

An Oracle Index is an object in a relational database used to store each record location in a table. It aims to speed up Data Reading and integrity checks. Indexing is a highly technical task.

Generally, we need to consider how to design and create indexes in the database design stage.

1. Create an index

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 that the created index is a UNIQUE index.
• CLUSTER: this parameter is an optional parameter used to specify a cluster (the Hash CLUSTER cannot create an index ).
• INITRANS and MAXTRANS: an optional parameter that specifies the initial and maximum number of transaction entries.
• TABLESPACE: The storage TABLESPACE of the index.
• STORAGE: STORAGE parameters.
• PCTFREE: Percentage of idle index data blocks.
• No sort: not sorted (the storage is sorted in ascending order, so it is not sorted here ).

Example: create a product table (tb_product) and create an index for the product_id column of the Table to improve the query efficiency when using this column.

Create table tb_product
(
Product_id number
, Product_name varchar2 (100)
, Product_type varchar2 (20)
, Product_unit varchar2 (50)
, Product_unit_price number (10, 4)
);

The following code creates a unique index on the product_id column:

Create unique index product_id_u1 on tb_product (product_id );

2. Modify the index

The modification of indexes is mainly done by the database administrator. Modifying indexes involves modifying the storage parameters of indexes, rebuilding indexes, and merging useless index spaces.

Modify the index Syntax:

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

Note:

• INITRANS n: the number of entries to the initial transaction simultaneously accessed in a block. n is a decimal integer.
• MAXTRANS n: the maximum number of transaction entries simultaneously accessed in a block. n is a decimal integer.
• REBUILD: re-create an index based on the original index structure, that is, re-scan the entire table to create index data.
• STORAGE: stores data.

Example:

Use the alter index statement to modify the INDEX parameters:

Alter index product_id_u1 rebuild storage (INITIAL 1 m next 512 K );

Use the alter index statement to change the INDEX to a reverse INDEX:

Alter index product_id_u1 rebuild reverse;

Use the alter index statement to merge the INDEX space:

Alter index product_id_u1 COALESCE;

3. delete an index

You can use the DROP statement to delete an index.

Drop index schema. index;

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Sharing pool for Oracle Performance Optimization