Oracle study note 12-sequence, index, synonym

Database objects include tables, views, sequences, indexes, and synonyms. The tables and views are learned in the previous notes.

Database objects, that is, sequences, indexes, and synonyms.

Sequence: provides regular values. Database objects that can be used by multiple users to generate unique values are mainly used to provide primary key values.

Index: Improves query efficiency.

Synonym: alias for the object.

In MYSQL and SQL SERVER databases, the value of the primary key is set to automatically increase, so that when data is inserted, no need to display

The specified data value. If you want to achieve automatic growth in Oracle, You need to rely on sequences.

Syntax format:

Create sequence sequence [increment by n] -- the value of each increase [start with n] -- Starting from which value [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] -- whether to loop [{CACHEn | NOCACHE}]; -- cache logon?

Example: Create a sequence

Create sequence em1_q
Increment by 1 -- increase by 1 each time
Start with 1 -- increases from 10
Nomaxvalue -- no maximum value, unlimited growth
Cycle -- loop required
Nocache-no cache required

After the sequence is created, all automatic growth should be handled by the user. The sequence provides the following two operations:

NextVal: gets the next content of the sequence.

CurrVal: Get the current content of the sequence.

Note: Run nextVal before using currVal. Otherwise, the returned currVal has not been defined in the session.

When you create empseq, set the growth rate to 1. You can change the value after increment by to set the increase speed. For example, you can set it to 10 or 20. However, the growth rate of the primary key is generally set to 1. By default, the starting value of the sequence is 1. You can use start with n to set the value you need.

Example: Sequence

Insert into emp (empno, ename, sal) values (em1_q. nextVal, 'lisi', '123 ');

Empseq. nextVal is used to replace the value that needs to be manually set, improving the efficiency.

Note: In this case, cracks in the sequence are easily generated.

System rollback, system exception, multiple tables simultaneously access one sequence.

Modify Sequence

Use the alter keyword to modify the sequence. The syntax format is as follows:

Alter sequence seq_name

[Increment by n] -- the value of each increase

[{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] -- whether to loop [{CACHEn | NOCACHE}];

Only the sequence owner can modify the sequence. After the sequence is modified, the future data is affected, and the previous data is not affected. The starting value of the sequence can only be modified by deleting and recreating the sequence.

Delete Sequence

Syntax format:

Drop sequence seq_name;

After the sequence is deleted, it cannot be referenced.

Index

A table-independent schema object that can be stored in a disk or tablespace that is different from the table. The index is deleted or damaged without affecting the table, it only affects the speed of the query. Once an index is created, the Oracle management system automatically maintains the index, and the Oracle management system determines when to use the index. You do not need to specify the index used in the query statement to delete a table, all indexes based on this table are automatically deleted. Using pointers to accelerate the query speed of the Oracle server, you can quickly locate data to reduce disk I/O.

Creation Syntax:

Create index index ON table (column [, column]...);

Delete Index

Drop index index_name; delete an INDEX statement. This operation cannot be rolled back.

Synonym-synonym

When creating a query, some tables have long names. This can be used to create a synonym for a table to shorten the table name.

Create synonym e FOR employees; CREATE a SYNONYM e FOR the table employees; execute select * from e; the query results FOR the statement are the same as those FOR executing select * from employees.

Delete Synonym

Drop synonym syn_name;