Creating Indexes, Sequences and Synonyms

Creating an Index on a Table

To improve performance, an index can be created for a table using the CREATE INDEX command. The index is automatically maintained by Oracle. An index is not referenced explicitly in commands which access the table; instead Oracle decides when it will use any existing indexes when it processes a command. Note that by specifying a column as a primary key, a unique index is automatically created by Oracle. Also creating a unique constraint in a table definition creates an index.

Format:

CREATE [UNIQUE] INDEX index-name
    ON table-name (column-name1 [ASC | DESC]
    [,column-name2 [ASC | DESC]]....)

UNIQUE: if included, specifies that the index is unique; that is, no two rows in the indexed table can have the same value for this column.

Note: The CREATE UNIQUE INDEX command will fail if it is issued for a table that already has multiple values in the columns indexed.

index-name: the name of the index to be created. Same rules as for table names.

table-name: the name of the table to be indexed.

column-names: names of the columns on which the index is to be created.

ASC | DESC: specifies that the index is to be in ascending or descending order; the default is ascending.

Examples:

To create an index called STNAMEDX on the STOCK table on the column STOCKNAME:

create index stnamedx on stock (stockname);

The following command creates a unique index called HOLDNDX on the HOLDING table on the columns PORTFOLIONO and STOCKSYMBOL:

create unique index holdndx on holding (portfoliono,stocksymbol);

The following is a function-based index:

create index mgr_managername_idx on manager (upper(managername));

Deleting an index

To delete an index, use the DROP INDEX command:

DROP INDEX index-name

Note: it is not possible to use this command to delete an index created by a primary key. Use the ALTER TABLE command to remove the primary key if required.

Creating an Sequence

A sequence is a sharable database object which automatically generates unique numbers. It is typically used as a primary key.

Format:

CREATE SEQUENCE sequence
    [increment by n]
    [start with n]
    [{MAXVALUE n | NOMAXVALUE}]
    [{MINVALUE n | NOMINVALUE}]
    [{Cycle | NoCycle}]
    [{Cache n | NOCACHE}];

sequence: name of sequence object

increment by n: incremental interval (default 1)

start with n: . first sequence number (default 1)

Maxvalue n: maximum sequence value

NoMaxvalue: 10^27 for ascending, -1 for descending sequence

Minvalue n: minimum sequence value

NoMinvalue: 1 for ascending, -(10^26) for descending sequence

Cycle|Nocycle: whether sequence begins again after reaching maximum (do not use cycle option if sequence is used for primary key value)

Cache n|Nocache: how many values Oracle server preallocates in memory ( 20 default)

Examples:

To create an sequence holdseq:

create sequence holdseq start with 1001;

Using a Sequence:

Insert into holding values (' P001', 'JFM ', '1-OCT-1998 ',300,19.55,holdseq.nextval);

Inserts a row into the holding table using the next available unique sequence number.

Select holdseq.currval from dual;

Currval is used to obtain the current sequence number.

Modify a Sequence

Alter sequence holdseq increment by 10 nocycle;

Delete a Sequence

DROP SEQUENCE sequencename

Example:

Drop sequence holdseq;

Creating Synonyms

A synonym is another name for an object.

Format:

CREATE [PUBLIC] SYNONYM synonym
    FOR object;

Examples:

create a synonym cash for manager_cash;

creates a synonym cash for the view manager_cash.

Drop synonym cash;