Читать книгу DBMS Lab Manual - Jitendra Inc. Patel - Страница 4

Practical 1: Create synonyms, sequence and index.

Synonyms:

A synonym is an alias for a database object (table, view, procedure, function, package, sequence, etc.). Synonyms may be used to reference the original object in SQL as wel as PL/SQL.

They can be used to hide ownership and location of the database objects they refer to and minimize the impact of moving or renaming the database objects.

The syntax for creating a synonym is:

create [or replace] [public] synonym [schema .] synonym_name

for [schema .] object_name [@ dblink];

There are two types of synonyms:

· private

Private synonyms exist only in a specific user schema. The owner of the synonym maintains control over availability to other users.

· public

A public synonym is available to all users

For example:

create public synonym suppliers

for app.suppliers;

This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:

select * from suppliers;

If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:

create or replace public synonym suppliers

for app.suppliers;

Dropping a synonym

It is also possible to drop a synonym.

For example:

drop public synonym suppliers;

This drop statement would drop the synonym called suppliers that we defined earlier.

Sequences (Autonumber)

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

The syntax for a sequence is:

CREATE SEQUENCE sequence_name

MINVALUE value

MAXVALUE value

START WITH value

INCREMENT BY value

CACHE value;

For example:

CREATE SEQUENCE supplier_seq

MINVALUE 1

MAXVALUE 999999999999999999999999999

START WITH 1

INCREMENT BY 1

CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.

If you omit the MAXVALUE option, your sequence will automatically default to:

MAXVALUE 999999999999999999999999999

So you can simplify your CREATE SEQUENCE command as follows:

CREATE SEQUENCE supplier_seq

MINVALUE 1

START WITH 1

INCREMENT BY 1

CACHE 20;

Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval.

For example:

supplier_seq.nextval

This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example:

INSERT INTO suppliers

(supplier_id, supplier_name)

VALUES

(supplier_seq.nextval, 'Kraft Foods');

This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

Index

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Create an Index

The syntax for creating a index is:

CREATE [UNIQUE] INDEX index_name

ON table_name (column1, column2, . column_n)

[ COMPUTE STATISTICS ];

UNIQUE indicates that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

For example:

CREATE INDEX supplier_idx

ON supplier (supplier_name);

In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx

ON supplier (supplier_name, city);

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX supplier_idx

ON supplier (supplier_name, city)

COMPUTE STATISTICS;

Drop an Index

The syntax for dropping an index is:

DROP INDEX index_name;

For example:

DROP INDEX supplier_idx;

In this example, we're dropping an index called supplier_idx.