Relation - Index (Indices) in Oracle
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
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.
alter index your_index unusable;
alter index your_index rebuild;
The index that are marked UNUSABLE can be skipped with the parameter skip_unusable_indexes;
SELECT DBMS_METADATA.get_ddl('INDEX',index_name,owner)
from
dba_indexes
where
owner || '.' || index_name in ('USER.INDEX_NAME');
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";
ALTER INDEX OWNER.NAME REBUILD TABLESPACE "TABLESPACE_NAME";
select index_name, tablespace_name from dba_indexes;
select 'alter index ' ||
index_owner || '.' ||
index_name || ' REBUILD PARTITION ' ||
partition_name || ' TABLESPACE TARGET_TABLESPACE_NAME;'
from DBA_IND_PARTITIONS where index_name = 'MYINDEXNAME';
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.