What are best practices for creating an index for a table?
Unless your table is being frequently updated (i.e., rows are being added or deleted), the recommendation is to use a bitmap index rather than a b-tree index. To create a bitmap index on a column (or set of columns) in a given table, use:
DESDB ~> CREATE BITMAP INDEX <index name> on <table name>(<column name(s)>);
If your table has a unique column that you would like to serve as an index, you might consider creating a primary key. The creation of a primary key is a bit different from a normal index, since it also sets a constraint on the table (no duplicate entries are allowed). To create a primary key on an existing table:
DESDB ~> ALTER TABLE <table name> ADD CONSTRAINT <index name> PRIMARY KEY (<column name(s)>);
Powered by a free Atlassian Confluence Open Source Project License granted to NCSA OpenSource. Evaluate Confluence today.