# Oracle Database - Indexes

## 1 - About

Indexing is a crucial aspect of the application design. Too many indexes and the performance of DML will suffer. Too few indexes and the performance of queries (including inserts, updates, and deletes) will suffer. Finding the right mix is critical to your application's performance.

When to index and what column to index are things you need to pay attention to in your design. An index does not always mean faster access, in fact, you will find that indexes will decrease performance in many case if Oracle uses them. See this example with B*Tree

• btree : Conventional indexes. The most common indexes in use in Oracle and most other databases.
• Bitmap index : Normally in a btree, there is a one-to-one relationship between an index entry and a row: an entry points to a row. With bitmap index, a single entry uses a bitmap to point to many rows simultaneously (One-to-Many Relationship). They are appropriate for highly repetitive data (data with a few distinct values relative to the total number of rows in the table)

Oracle can use an index on a column to count the number of rows in the table only when the column has been declared NOT NULL

## 3 - Management

### 3.1 - Creation

Create Statement for columns table:

CREATE [UNIQUE|BITMAP} INDEX [SCHEMA].INDEX_NAME
ON [SCHEMA].TABLE_NAME (COLUMN1, COLUMN2, ...)
PCTFREE 10 -- Physical Storage Property
INITRANS 2 -- Physical Storage Property
MAXTRANS 255 -- Physical Storage Property
COMPUTE STATISTICS
NOLOGGING -- logged in the redo log file ?
STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TABLESPACE_NAME" -- Tablespace

### 3.2 - LOGGING

The LOGGING/NOLOGGING setting determines if operations are logged in the redo log file.

The LOGGING/NOLOGGING setting also determines whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against the index are logged or not logged.

If you omit this clause, then the logging attribute is that of the tablespace in which it resides.

### 3.3 - Usable / Unusable

#### 3.3.1 - Unusable

ALTER INDEX your_index unusable;

#### 3.3.2 - Usable

ALTER INDEX your_index rebuild;

#### 3.3.3 - Conf

The index that are marked UNUSABLE can be skipped with the parameter skip_unusable_indexes;

### 3.4 - DDL

SELECT DBMS_METADATA.get_ddl('INDEX',index_name,owner)
FROM
dba_indexes
WHERE
owner || '.' || index_name IN ('USER.INDEX_NAME');

### 3.5 - Tablespace

#### 3.5.1 - Normal

If you omit the Tablespace clause, then Oracle Database creates the index in the default tablespace of the owner of the schema containing the index.

• Create
CREATE INDEX OWNER.NAME ON OWNER.TABLE_NAME (COLUMN_NAME,COLUMN_NAME,...) TABLESPACE "TABLESPACE_NAME";
• (Modify|Move|Rebuild)
ALTER INDEX OWNER.NAME REBUILD TABLESPACE "TABLESPACE_NAME";
• Select
SELECT index_name, tablespace_name FROM dba_indexes;

#### 3.5.2 - Partition

• Modify. The blocksize of a partitioned table should be the same for the whole table. You can't move a single partition to another tablespace with a different block_size. Exp/Import method / drop create must be used.
SELECT 'alter index ' ||
index_owner || '.' ||
index_name || ' REBUILD PARTITION ' ||
partition_name ||  ' TABLESPACE  TARGET_TABLESPACE_NAME;'
FROM DBA_IND_PARTITIONS WHERE index_name = 'MYINDEXNAME';

## 4 - DataWarehouse

Basic indexes are created and provided out of the box. These indexes are created based on the kind of reports. Heap and Bitmap indexes are created out of the box.

• Bitmap indexes are typically created when the cardinality of rows are low.
• All indexes are created on the Aggregated and Central FACT tables only
• No additional indexes are created on OFSA Tables
• If the tables are partitioned then 'LOCAL' indexes are created in case of BITMAP indexes
• All Indexes are dropped/disabled before load and enabled/created post loading
database/oracle/index.txt · Last modified: 2016/02/13 07:56 by gerardnico