Database of Neusoft Internship (12)--sequence, index, synonym

#序列

The concept of a sequence

A sequence is a database object used to generate unique numeric column values, typically using sequences to automatically generate either a master code value or a unique key value, and the sequence can be ascending or descending.

Sequence Features:

• • • can automatically generate unique values;
    • Can be shared by multiple users;
    • A typical application is to generate a primary key value that identifies the uniqueness of the record;
    • Speed of access using cache acceleration sequences.

#创建序列语法

　　　　　　

To create a sequence, you must have create sequence or create any sequence permission.

After the sequence is created, you can view the sequence information user_sequences by querying the data dictionary view.

Create a sequence

1. 1. Name of the sequence sequence generator
   2. INCREMENT by n Specifies the interval between sequence values n, if omitted the item interval is 1
   3. Start with n Specifies the starting sequence value N, if the entry omits the starting value of 1
   4. MAXVALUE n Specifies the maximum sequence value
   5. NOMAXVALUE specifies that the maximum sequence value is 10 of 27, and that the maximum descending value is-1, which is a default option
   6. MINVALUE n Specifies the minimum sequence value
   7. Nominvalue Specifies the ascending minimum sequence value of 1, whereas the descending minimum sequence value is negative 10 of 26 times, which is a default option
   8. CYCLE | NOCYCLE specifies that the sequence value continues to produce a sequence value after reaching the maximum or minimum value, nocyle means no longer being generated, and Nocyle is the default option.
   9. CACHE N | NOCACHE The specified sequence value is pre-allocated and stored in memory by the Oracle server, NOCACHE means no pre-allocation and storage, and CACHE 20 is the default option

　　　　　　

Nextval and Currval pseudo-columns

Available statements sequence_name. Currval and Sequence_name. Nextval to access the sequence;

• • • Currval: Gets the current sequence value;
    • Nextval: Returns the next valid sequence value;
    • Both Currval and Nextval return the number value;
    • Before currval can be referenced, a sequence value must be generated using Nextval;

Nextval and Currval pseudo-columns

You can use Nextval and currval in the following situations:

• • • Select statements in the select list, but not the SELECT statement in the subquery
    • Sub-query in INSERT statement in select list
    • In the values clause of the INSERT statement
    • In the SET clause of the UPDATE statement

Nextval and Currval cannot be used in the following situations:

• • • In the select list of the view
    • In a SELECT statement that contains the DISTINCT keyword
    • In a SELECT statement that contains a GROUP by, have, ORDER by clause
    • SELECT, DELETE, in the subquery of the UPDATE statement
    • CREATE table, ALTER table statement containing the default expression

#创建序列

#修改序列

　　　　　　

　　　　　　

#删除序列

　　　　　　

#索引

The concept of an index

The index is: a database object in the schema;

In the database, to accelerate the query speed of the table;

The fast path access method is used to quickly locate the data and reduce the disk I/O;

Separate from the table, but need to be attached to the table, is created on the basis of the table;

Automatically maintained by Oracle database;

(Note: If you delete a table, the indexes associated with that table are also deleted.) ）

#创建索引

There are two ways to create an index: auto-Create, create manually

Automatic creation: When a primary KEY or UNIQUE constraint is defined on a table, the Oracle database automatically creates a corresponding unique index;

Create manually: Users can create indexes to speed up queries and create the required indexes on fields that need to be indexed.

#测试索引

　　　　　　

Suitable for creating indexing conditions

1. 1. Query columns have a wide range of data;
   2. The query column contains a large number of null values, because null values are not included in the index;
   3. A column in a Where condition or a column in a multiple table join is suitable for creating an index;
   4. The amount of table data to be queried is very large, and most of the queries get the 2%~4% of the total record amount of the result set;
   5. Too many indexes are not always more beneficial, too many indexes do not mean faster queries, and each DML operation on the table with indexes means that the index must be updated, and too many indexes will cause the Oracle server to spend more time on the update of the index after DML operations.

Not suitable for creating an index

1. 1. A table with a small amount of data;
   2. A column that is not commonly used as a query condition in a query;
   3. The result set that the query finally obtains is very big;
   4. Tables that are frequently updated (indexes have partial negative effects on DML operations);
   5. When an indexed column is used as part of an expression (for example, the condition of a constant query is salary*12, it is not effective to create an index on the salary column);

#删除索引

After the index is deleted, the data and definitions in the index are deleted, and the data space occupied by the index is freed, but the data in the table still exists.

Common index-related data dictionary views are:

• • • User_indexes: The definition of the user's Index object, containing the index's name and some related attributes of the index (such as uniqueness, etc.)
    • User_ind_columns: The column definition information for the user Index object, which lists the index name, table name, and column name, and so on.

# #本章重点总结

1. Understand the functions and concepts of sequences and indexed objects;
2. Mastering the creation and use of sequences;
3. Master how to create and use indexes;
4. Master when the index should be created;

Database of Neusoft Internship (12)--sequence, index, synonym