Oracle SQL Foundation (v): Data definition language (create and manage sequences, indexes, synonyms)

Many applications require a unique number to be used as the primary key value, so you can build code in your application to handle this requirement, or you can use a sequence to produce a unique number. If you want to improve the performance of some queries, you should consider creating an index, or you can enforce uniqueness on the collection of columns or columns with an index. You can use synonyms to provide an alternative name for the object. Let's introduce the sequence, index, and synonym three database objects.

I. Creating and managing sequences

A sequence is a user-created database object that can be shared by multiple users. A typical use of a sequence is to create a value for a primary key that must be unique for each row. The sequence is generated and increased or decreased by an Oracle internal program. A sequence is a time-saving object because it reduces the amount of code in the application that produces the sequence program. Serial numbers are stored and generated independently of the table, so the same sequence can be used by multiple tables.

1.1 Definition and query sequences

The sequence number is automatically generated with the CREATE SEQUENCE statement. Grammar:

CREATESEQUENCE SEQUENCE--sequence is the name of the sequence generator.[INCREMENT by N]           --The interval between the sequence numbers, where n is an integer,[START with N]             --the number of the first sequence produced, the default value of 1. [{MAXVALUE n| Nomaxvalue}] --the maximum value that the sequence can produce, default Nomaxvalue (ascending: 10^27; Descending:-1)[{MINVALUE n| Nominvalue}] --minimum sequence value, default Nominvalue (ascending: 1; descending-(10^26))[{CYCLE | Nocycle}]        --Specifies whether the sequence continues to produce after its maximum or minimum value has been reached, by default nocycle. [{CACHE n| NOCACHE}];--Specifies how many values are pre-allocated by the Oracle server and is kept in memory, with a default buffer of 20 values. 

If the increment by value is a negative number, the sequence is descending. In addition, order | The Noorder option is available, and the order option guarantees that the sequence values are generated sequentially, which is not important if you use the sequence to produce a primary key value, which is only relevant to the parallel Server (parallel services) option. If the sequence values are buffered at a high speed, the system fails and they are lost.

Once a sequence is created, it is text in the data dictionary. Because the sequence is a database object, you can identify it in the User_objects Data dictionary table. You can also confirm the set of sequences from the User_sequences data dictionary view with the selection. The following is an instance of a definition and query sequence.

--Create a sequenceCREATESEQUENCE seq_testincrement by 2START with 3MAXVALUE +nocycle--If the user generates a primary key, the cycle option is not used. NOCACHE--do not allow high-speed buffering;--Query SequenceSELECT object_name,object_id, Object_type fromuser_objectsWHERE object_name = 'seq_test';SELECT *  fromuser_sequencesWHERESequence_name= 'seq_test';

defining and querying sequence instances

1.2 Using sequences

After the sequence is created, it produces successive numbers for you to use in the table. Reference sequence values with Nextval and currval pseudo-columns. The Nextval pseudo-column is used to retrieve the next value of a contiguous sequence number from the specified sequence. You must use the sequence name to qualify Nextval when you refer to sequence. Nextval, a new sequence number is generated and the current sequence number is placed into the currval. The Currval pseudo-column is used to refer to the number of sequences that the current user has just produced, and nextval must be used to produce a sequence number in the current user's session before Currval can be referenced, and you must qualify Currval with the sequence name when sequence. When Currval is referenced, the last value returned to the user program is displayed.

If the sequence is created with an option with NOCACHE, you can view the next available sequence value with the method of querying the User_sequences table without increasing the sequence value. Often used sequences created with a cache will improve efficiency, for cached sequences, there is no way to find out what the next available sequence will be, this value is not actually obtained and used, therefore, it is recommended that the user do not look for the next sequence value, but believe that each time a sequence is used for an INSERT statement, the system provides a unique value.

 --   INSERT  into   dept (Deptno,dname,loc)  values  (seq_test. Nextval, " test  , "  test   "  --  select  Seq_test. Currval from   dual;  --  select  Seq_test. Nextval from  dual; 

working with sequence instances

1.3 Modifying a sequence

You can modify the sequence with the ALTER sequence statement. Grammar:

ALTER SEQUENCE SEQUENCE [INCREMENT by n] [{MAXVALUE n | Nomaxvalue}][{MINVALUE n | Nominvalue}][{CYCLE | Nocycle}][{CACHE n | NOCACHE}];

With the ALTER SEQUENCE statement, only the number of subsequent sequences is affected; the START with option cannot be changed. In order to restart a sequence with a different number, the sequence must be deleted and recreated; some validation is performed, for example, a new MaxValue is not available if it is less than the current sequence value.

ALTERbyten10000;

Modifying a sequence instance

1.4 Deleting a sequence

Use the drop SEQUENCE statement to remove a sequence from the data dictionary. You must be the owner of the deleted sequence or have drop any sequence permission to delete it. Grammar:

DROP SEQUENCE SEQUENCE;

For example: DROP SEQUENCE seq_test;

Ii. Creating and maintaining indexes

An index is a schema object for an Oracle server, which can be used to accelerate the retrieval of rows, an index can be created explicitly, or it can be created automatically, and a full table scan will occur if you do not have an index on the column. Indexes provide direct and fast access to rows in a table, and are designed to quickly locate data with an indexed path to reduce disk I/O. Indexes are automatically used and maintained by Oracle servers, and once an index is created, it no longer requires direct user management.

2.1 Creating an index

You can create two types of indexes, one unique index: When you define a column as a primary key in a table, or when you define a unique key constraint, the Oracle server automatically creates the index, and the name of the index is customarily the name of the constraint. Another index type is a non-unique index that can be created by the user, for example, you can create a foreign key column index for a connection in a query to improve the speed of data retrieval. To create an index syntax:

CREATE INDEX Index  on Table (column[, Column]...);

Example: CREATE Index dept_dname_idx on dept (Dname);
Establishing more indexes on a table does not imply faster querying, each DML operation that is committed on the indexed table means that the index must be updated, and the more indexes that are associated with the table, the greater the impact on the Oracle server. Therefore, you only need to create an index in the following situations:
1. The column contains a large range of values, the column contains a large number of null values, one or more columns are used frequently in a WHERE clause or join condition, the table is large, and most of the queries expect a 2–4% that returns fewer rows than the Total row count.
2. If you want to force non-unique, you should define a unique constraint in the table, and then the unique index is created automatically.
3. A composite index (also known as a Join index) is an index created on multiple columns in a table that can appear in any order in the composite index and does not need to be consistent with the columns in the table.

The following scenario is usually not worth creating an index:

The table is small and is not often used as a condition in the query, and most queries expect to retrieve more rows than are 百分之2到4 in the table, which is often updated and the column being indexed is referenced as part of the expression.

2.2 Query Index

You can confirm the existence of an index from the user_indexes data dictionary view. You can also query the User_ind_columns view to check the columns related to the index. The following example queries all created indexes on the Employees table, including the name of the affected column and the uniqueness of the index.

Select Ic.index_name,       Ic.column_name,       ic.column_position col_pos,       ix.uniquenessfrom  user_indexes Ix, User_ind_ Columns IcWhere=  ix.index_nameand='DEPT  ';

querying an Index instance

2.3 Deleting an index

You cannot modify the index, in order to change the index, you must first delete it and then recreate it. Remove the index from the data dictionary with the DROP INDEX statement, in order to delete the index, you must be the owner of the index, or have DROP any index permission. If you delete a table, the associated indexes and constraints will be automatically deleted, but the view and sequence will be preserved. To delete an index syntax:

DROP INDEX index;

For example: DROP INDEX dept_dname_idx;

Iii. Creating and managing synonyms

In order to look up a table owned by another user, you need to prefix the name of the user who created the table with a period prefixed with the name of the table. Create a synonym to remove the schema restriction that the object name must take and provide you with a replaceable table name, view name, sequence name, and procedure name or other object name. This method is useful for names with very long objects.

3.1 Creating synonyms

CREATE [public]  for object; -- Public creates a synonym that can be accessed by all users -- synonym is the name of the synonym to be created -- object that indicates the objects to create synonyms for

The database administrator can create a public synonym that can be accessed by all users. The following example creates a public synonym for Scott's Dept table:

CREATE  Public synonym Dept_syn  for scott.dept;

Create a synonym instance

3.2 Deleting synonyms

Delete a synonym with the drop synonym statement, and only the database administrator can delete a common synonym.

DROP  Public synonym synonym;

For example: DROP public synonym Dept_syn;

Oracle SQL Foundation (v): Data definition language (create and manage sequences, indexes, synonyms)