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;
|
|
