Index In Oracle Database with Examples

Index Management

Main index types: b-tree, reverse key, and bitmap indexes.

/* unique index, use a low PCTFREE value for ID columns */

CREATE UNIQUE INDEX schema2.idx_invoice
ON schema2.invoice ( inv_id )
PCTFREE 10
STORAGE ( INITIAL 200K 
          NEXT 200K
          PCTINCREASE 0
          MAXEXTENTS 50 )
TABLESPACE idx01 ;


/* non-unique index ;  
   note the NOLOGGING parm, which is recommended for large indexes
   also note the NOSORT option, which is good for large tables where the
   data has been loaded in sorted order.
*/

CREATE UNIQUE INDEX schema2.idx_customer
ON schema2.customer ( stat_cd, name )
PCTFREE 40
STORAGE ( INITIAL 200K 
          NEXT 200K
          PCTINCREASE 0
          MAXEXTENTS 50 )
NOLOGGING          
NOSORT
TABLESPACE idx01 ;


/* index extent increase */

ALTER INDEX schema2.idx_customer
STORAGE ( NEXT 300K ) ;


/* indexes should be rebuilt, when there are many deleted entries */

ALTER INDEX schema2.idx_customer
REBUILD
TABLESPACE idx02 ;


alter index PK_EMPLOYEE rebuild compute statistics nologging ; 



/* update internal stats on the index, for the optimizer ...
  also check for corruption */

ANALYZE INDEX schema2.idx_customer VALIDATE STRUCTURE ;



/* disable a constraint */

ALTER TABLE EMPLOYEE DISABLE CONSTRAINT FK_DEPT ;


/* add a constraint */

alter table  REPORT_LIST
add constraint  PK_REPORT_LIST primary key(REPORT_ID)
using index tablespace prod_idx_01;