Oracle's Index instance tutorial-basics

Oracle's Index instance tutorial-basics

Index is an object in a relational database that holds the location of each record in a table, with the main purpose of speeding up the reading speed of the data and checking the integrity of the data. The establishment of indexes is a very high technical requirement.

It is generally necessary to consider how to design and create indexes during the database design phase.

1. Create an index

Syntax for creating indexes:

CREATE [UNIQUE] INDEX [schema.] Indexon [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 is used to indicate that the index created is a unique index.
• CLUSTER: This parameter is optional and is used to specify a cluster (Hash CLUSTER cannot create an index).
• Initrans, Maxtrans: For optional parameters, specify the initial and maximum number of transaction entries.
• Tablespace: The storage table space for the index.
• STORAGE: Storage parameters.
• PCTFREE: Percentage of index chunk idle.
• No sort: not sorted (sorted in ascending order when stored, so this point is no longer sorted).

Example: Create a Product table (Tb_product) that creates an index for the product_id column of the table to improve query efficiency when used with this column.

CREATE TABLE tb_product (  product_id number              , Product_Name            varchar2 (+), Product_type            varchar2 (20), Product_unit            Varchar2 (a), Product_unit_price number      (10,4));

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

Create unique index product_id_u1 on tb_product (product_id);

2. Modifying the index

The modification of the index is mainly done by the database administrator, which mainly involves modifying the storage parameters of the index, rebuilding the index, merging the useless index space, and so on.

Syntax for modifying indexes:

ALTER [UNIQUE] INDEX [user.] Indexinitrans nmaxtrans nrebuild [STORAGE <storage>]

Description

• Initrans N: Represents the number of entries for the initial transaction that is accessed concurrently within a block, and n is a decimal integer.
• Maxtrans N: Represents the maximum number of transaction entries that are accessed concurrently within a block, and n is a decimal integer.
• REBUILD: Represents a re-index based on the original index structure, that is, the index data is created after a full table scan of the table.
• STORAGE <storage>: Represents the storage data.

Example:

To modify an index parameter by using the ALTER INDEX statement:

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

Use the ALTER INDEX statement to modify the index to reverse the 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. Deleting an index

You can use the drop statement to delete an index.

DROP INDEX Schema.index;

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

---------------------------------------------------------------------------------------------------------

If you have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint Please indicate the source, otherwise investigate legal responsibility!
----------------------------------------------------------------------------------------------------------

Oracle's Index instance tutorial-basics