- 1). Identify the type of index required. This will be one of the following types: normal, bitmapped, partitioned, function or domain-based. You will need to gain an understanding of these before you can attempt to create index attributes based on them.
- 2). Choose the field or field(s) in the database for which you want to create an index. Primary keys in the database are automatically indexed, but if you use other fields in the database that are accessed often, it is worth creating an index for the field.
- 3). Create an index for the chosen field or fields as in the following examples.
A single field:
CREATE INDEX ord_sales_ix ON sales (client_id);
An index with two fields:
CREATE INDEX ord_sales_ix ON orders (client_id, sales_repid); - 4). Allocate indexes to other objects, such as clusters. In addition to creating indexes on tables, if you have an Oracle cluster, an index can be created on it to improve performance as shown:
CREATE INDEX idx_cluster ON CLUSTER pers_cluster; - 5). Filter your indexes to be used only when a specific function is called on the data as illustrated:
CREATE INDEX upper_name_ix ON customers (UPPER(surname));
For every instance the "UPPER" function is called on the surname in this example, the index "upper_name_ix" will be used.
SHARE
