Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Oracle » Administration » DBA »

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;














Oracle : Related Topics

Oracle : Administration : Server Manager
Oracle : Administration : O/S Password Authentication
Oracle : Administration : Database Creation
Oracle : Administration : Tablespace Creation
Oracle : Administration : Increasing the size of a tablespace
Oracle : Administration : Rollback segments
Oracle : Administration : Adding a redo log file set
Oracle : Administration : Altering table storage
Oracle : Administration : Backing up data files
Oracle : Administration : Taking tablespaces offline
Oracle : Administration : Creating users
Oracle : Administration : DB Verify
Oracle : Administration : Clearing Log Files
Oracle : Administration : Moving Data Files
Oracle : Administration : Creating a Schema
Oracle : Administration : Enabling Archive Logging
Oracle : Administration : Updating statistics for a table or schema
Oracle : Administration : Killing Sessions
Oracle : Administration : Recovering an Instance
Oracle : Administration : Drop a table and deallocate space
Oracle : Administration : Dropping a Tablespace
Oracle : Administration : Restricted Mode
Oracle : Administration : Oracle Issues, Facts You Should Know

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.