Oracle Database - Indexes
Table of Contents
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
2 - Articles Related
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.
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
3.3.2 - Usable
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 INDEX OWNER.NAME ON OWNER.TABLE_NAME (COLUMN_NAME,COLUMN_NAME,...) TABLESPACE "TABLESPACE_NAME";
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.
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