How to create an optimal index using Oracle Indexing Technology

 

How to create the best index?

1. Create an index explicitly

Create index index_name on table_name (field_name)

Tablespace tablespace_name

Pctfree 5

Initrans 2

Maxtrans 255

Storage

(

Minextents 1

Maxextents 16382

Pctincrease 0

);

 

2. Create a function-based index

It is commonly used in functions such as UPPER, LOWER, and TO_CHAR (date). For example:

Create index idx_func on emp (UPPER (ename) tablespace tablespace_name;

 

3. Create a bitmap Index

When you create an index for a column with a small base and a relatively stable base, you must first consider the bitmap index. For example:

Create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

 

4. explicitly create a unique index

You can use the create unique index statement to create a unique index. For example:

Create unique index dept_unique_idx on dept (dept_no) tablespace idx_1;

 

5. create constraints-related indexes

You can use the using index statement to create indexes related to the unique and primary key constraints. For example:

Alter table table_name

Add constraint PK_primary_keyname primary key (field_name)

Using index tablespace tablespace_name;

 

How to create a local partition index?

1) The basic table must be a partition table.

2) The number of partitions is the same as that of the base table.

3) The number of subpartitions in each index partition is the same as that in the corresponding basic table partition.

4) The index items of rows in the Self-partition of the base table are stored in the corresponding self-partition of the index, for example

Create index TG_CDR04_SERV_ID_IDX on TG_CDR04 (SERV_ID)

Pctfree 5

Tablespace TBS_AK01_IDX

Storage (

MaxExtents 32768

PctIncrease 0

FreeLists 1

FreeList Groups 1

)

Local

/

 

How do I create a global index for a range partition?

Basic Tables can be Global tables and partition tables.

Create index idx_start_date on tg_cdr01 (start_date)

Global partition by range (start_date)

(Partition p01_idx vlaues less than ('20140901 ')

Partition p01_idx vlaues less than ('20140901 ')

...

Partition p01_idx vlaues less than ('20140901 '))

/

 

How to reconstruct an existing index?

Rebuilding an existing index at the current time does not affect queries.

Re-indexing can delete additional data blocks

Improves index query efficiency

Alter index idx_name rebuild nologging;

For partition Indexes

Alter index idx_name rebuild partition partition_name nologging;

 

Why is the index deleted?

1) indexes no longer needed

2) The index does not provide the expected performance improvement for the queries published on the relevant tables.

3) The application does not use this index to query data.

4) The index is invalid and must be deleted before reconstruction.

5) The index has become too broken and must be deleted before reconstruction.

Statement:

Drop index idx_name;

Drop index idx_name partition partition_name;

 

What is the cost of creating an index?

During basic table maintenance, the system must maintain indexes at the same time. unreasonable indexes will seriously affect system resources,

It is mainly manifested in CPU and I/O.

Inserting, updating, and deleting data results in a large number of db file sequential read lock waits.