Oracle Learning Article 12: Indexing

Index:

Query user_indexes can get more information about the indexes that the user has created.
Query user_ind_partitions can get detailed information about the partition index that the user has created.
Query User_ind_columns can get detailed information about the columns (the user's index is created from these columns).

Index type: Unique index, composite index, reverse key index, bitmap index, and function-based index.

Indexes are logically physically independent of the data in the associated table and can be created or dropped at any time without affecting the base table or other indexes.

1. Syntax for creating a normal index:
Create INDEX index_name on table_name (column_list) [tablespace tablespace_name];

which
Index_name refers to the name of the index being created.
TABLE_NAME represents the name of the table for which the index was created.
Column_list is the list of column names on which indexes are created, and indexes can be created based on multiple columns.
TABLESPACE_NAME Specifies the table space for the index.

Example 12: Demonstrates how to create an index on the ItemCode column of a itemfile table
Create INDEX Item_index on Itemfile (ItemCode);

The rebuild option of the 2.ALTER index statement can be used to rebuild an existing index. This option provides better performance than re-creating the index using the drop INDEX and CREATE INDEX statements.

Example 13: Rebuilding an index
ALTER INDEX Item_index REBUILD;

To delete an index syntax: Drop INDEX Item_index;

(1) Unique index: This index ensures that the values of any two rows of the table are different in the column that defines the index. Oracle automatically creates a unique index for the table's primary key columns.
You can use the Create unique index command to explicitly create a unique index.

Example 14: A unique index named Item_index is created on the ItemCode column of the Itemfile table.
Create UNIQUE INDEX item_index on Itemfile (ItemCode);

(2) composite index: A composite index is an index created on more than one column in a table. The order of the columns in the composite index is arbitrary and does not have to be adjacent columns in the table.
When you create a composite index, you should be aware of the order of the columns used in the definition. Typically, the most frequently accessed columns should be placed at the front of the list.

Example 15: A composite index named Comp_index is created on the Itemfile table, when the WHERE clause of the query for the table contains either of these two columns, or contains only
P_category column, the following example statement creates an index that will be used to retrieve the data, but if the itemrate column is used alone, the index cannot be used to retrieve the data.
Create INDEX Comp_index on Itemfile (p_category,itemrate);

(3) Reverse key index: usually built on columns where values are continuously growing, such as when the values in a column are generated by a sequence.
Example 16: A reverse key index named Rev_index is created on the Itemfile table. Note Use the Reverse keyword.
Create INDEX Rev_index on Itemfile (ItemCode) REVERSE;

Example 17: Use the keyword Noreverse to rebuild a reverse key index to a standard index.
ALTER INDEX rev_index REBUILD noreverse;
Note: You cannot rebuild a standard index to a reverse key index.

(4) Bitmap index: If a column has a value that repeats more than 100 times, you might consider creating a bitmap index on that column.
The example 18:itemcode is a low cardinality column in the Order_Detail table, because the goods encoding is duplicated in most orders, so it is appropriate to create a bitmap index on that column.
Create BITMAP INDEX bit_ind1 on Order_Detail (ItemCode);
Bitmap indexes should not be used on tables that frequently occur on insert,update,delete operations. Bitmap indexes are best suited for data warehousing and decision support systems.

(5) Index Organization table: an indexed organization table is similar to a normal table indexed on one or more columns, but it does not need to maintain two separate storage spaces for tables and indexes.
The database system maintains only one index that contains the corresponding encoded key values and the column values associated with them.
Example 19: Use the Organization index clause to create an indexed organization table.
Create Table Ind_org_tab
(
Vencode Number (4) Primary key, note: Primary key is required to create an indexed organization table. Partitioning is not allowed.
Venname VARCHAR2 (20)
)
Organization Index;
An indexed organization table is appropriate for accessing data through a primary key.

(6) Function-based index: If a column is already contained in an arithmetic expression or function in the WHERE clause, the index on that column is not used. You cannot include any in an expression
An aggregate function that creates a function-based index on a LOB column, a ref column, or an object type that contains LOB or ref.

Example 20:venname is a column of the Vendor_master table that stores the name of the vendor, assuming that all vendor names are stored in mixed-case form
(e.g. John Smith, Dave Jones, Tony Greig, etc.), and assume that we often need to query the table's data based on the vendor's name. Because
Names are stored in mixed-case form, so it may be difficult to give the correct capitalization of a name.
The following indexes can be created:
Create INDEX vn_ind on Vendor_master (UPPER (venname));

Example 21: Demonstrates how to retrieve data using the function-based index that you created earlier.
SELECT * from Vendor_master where UPPER (venname) = ' SMALL ';

To create an index that is based on a function or an expression, you must have query rewrite system permissions.

(7) Partitioning in an index: similar to partitioning a table, Oracle also allows partitioning of indexes. The traction partition can be stored in a different table space.
Local Partition Index: Oracle establishes a separate index for each partition of the table.

Example 22: Create a partitioned table first
Create Table Order_mast
(
OrderNo Number (4),
Venname VARCHAR2 (20)
)
Partition by range (OrderNo)
(
Partition Oe1 values less than (1000),
Partition Oe2 values less than (2000),
Partition Oe3 values less than (MaxValue)
);

Then create the local index:
Create INDEX Myind on Order_mast (OrderNo) LOCAL;

Global Partition index: Refers to an index created on a partitioned table or a non-partitioned table.

Example 23: Create a global index on the partition table created above
Create INDEX Glb_ind on Order_mast (OrderNo) GLOBAL
Partition by range (OrderNo)
(
Partition ip1 values less than (1500),
Partition IP2 values less than (MaxValue)
);

Create an index of 2 partitions on a table with 3 partitions. Note: You cannot establish a global index on a hash partition or sub-partition.

Global non-partitioned index: A global partition index is a global index created on a partitioned table that resembles an index on a non-partitioned table, and the structure of the index is not split.

Oracle Learning Article 12: Indexing