About
Relation - Index (Indices) in Oracle
Articles Related
Management
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
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.
Usable / Unusable
Unusable
alter index your_index unusable;
Usable
alter index your_index rebuild;
Conf
The index that are marked UNUSABLE can be skipped with the parameter skip_unusable_indexes;
DDL
SELECT DBMS_METADATA.get_ddl('INDEX',index_name,owner)
from
dba_indexes
where
owner || '.' || index_name in ('USER.INDEX_NAME');
Tablespace
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;
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';
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