In SQL ssh Oracle database 11g 1 How can a sequence be used

In SQL  ssh, Oracle database 11g:

1- How can a sequence be used in a database?

2-How can you indicate that the values generated by a sequence should be in descending order?

3- When is an index appropriate for a table?

4- What command is used to modify an index?

5- What is the purpose of a synonym?

Solution

(1)

Sequence refers to numbers that increase/decrease monotonically. Usually they see usage in the form of the primary key.

CREATE SEQUENCE oddsequence

INCREMENT BY 2

START with 1

MAXVALUE 10

NOMINVALUE

NOCYCLE

The above would create the following sequence: 1 3 5 7 9

-------------------------------------------------------------------------------------------------------

(2)

To generate a sequence in descending order, use the INCREMENT BY clause. Just give some negative number in it.

CREATE SEQUENCE descendingseq

INCREMENT BY -1

START with 1

NOMAXVALUE

MINVALUE -5

NOCYCLE

The above would create the following sequence: 1 0 -1 -2 -3 -4 -5

-------------------------------------------------------------------------------------------------------

(3)

An index is appropriate in the following cases:

Index is created as follows:

CREATE INDEX index_name ON table_name (column_name);

-------------------------------------------------------------------------------------------------------

(4)

Modifying an index is very limited. Operation such as a name change is possible. However, to make any major changes in the index, it has to be dropped and recreated.

To drop the index use the following command:

            DROP INDEX index_name;

-------------------------------------------------------------------------------------------------------

(5)

Synonyms provide abstraction. It provides an alias to a database object.

For example, if there is a remote database object maintained by someone else. Then using synonyms can decouple the object’s name and also its location. This enables you to write code for the object using the synonym. So, a change in the actual object doesn’t affect the code. The synonym is updated as and when required depending on when the object’s location or name changes.