SQL Basic Learning Note (v)-sequence, index, synonym

Common database Objects
Table: A basic collection of data stores, consisting of rows and columns
Views: Extracting logically related data collections from a table
Sequence: Provides a regular number of values
Index: Improve query efficiency
Synonyms: Aliases An object

(Index and synonyms to understand)

sequence sequences
database objects that can be used by multiple users to produce unique values
Automatically provide a unique value
Shared objects
Primarily used to provide primary key values
Loading sequence values into memory can improve access efficiency
How and how to create a sequence

CREATE SEQUENCE sequence[increment by n]  --value per increment [start with n]    -from which value [{MAXVALUE n | Nomaxvalue}][{minvalue N | nominvalue}][{cycle | Nocycle}]     --Do you need a loop      [{CACHE n | NOCACHE}];  --whether to cache login

Application of the sequence:
For example, in college students each will have a unique and not repeat the number, since everyone has, then there is no need for everyone's information insert, then you can use the sequence to achieve
So use a sequence to act as the primary key value
Create a sequence

Create sequence empseqincrement by 1start with 1maxvalue 10cycle--loop nocache--no cache login required

View sequence

Select Empseq.nextval from Dual;select empseq.currval from dual;

Note to use Nextval first
Create an empty table

CREATE TABLE Emp01asselect employee_id, last_name, salaryfrom employeeswhere 1 = 2

Add data

INSERT into Emp01values (1, ' A ', 2000)

Using sequences

Insert into Emp01values (Empseq.nextval, ' A ', 2000)

You don't have to fill in the employee_id value every time.
Modifying a sequence

Alter sequence empseqincrement by 2nocycle

Changes to the sequence can be made with alter in addition to the initial value, and the initial value is only achieved by removing the sequence

Loading sequence values into memory improves access efficiency

The sequence appears cracked in the following cases:
Rolling back
System exceptions
Multiple tables use the same sequence simultaneously->emp1 the primary key value that is obtained by using the EMPSEQ sequence is 3,EMP2 and the primary key value that is obtained by using the EMPSEQ sequence is 4, and the value that EMP1 uses Empseq is 5, which cracks

If you do not load the value of the sequence into memory (NOCACHE), you can use table User_sequences to view the current valid values of the sequence

Delete a sequence

Drop sequence empseq;

Index:
A table-independent schema object that can be stored in a different disk or table space than a table
The index is deleted or corrupted and does not affect the table, it only affects the speed of the query
Once an index is established, the Oracle Management system automatically maintains it, and the Oracle management system determines when the index is used. The user does not have to specify which index to use in the query statement
When you delete a table, all indexes that are based on that table are automatically deleted
Accelerate query speed for Oracle servers with pointers
Reduce disk I/O by quickly locating data
Auto-Create: The system automatically creates a unique index on the corresponding column after the PRIMARY KEY or UNIQUE constraint is defined
Create manually: Users can create non-unique indexes on other columns to speed up the query

CREATE INDEX Emp01_id_ixon EMP01 (employee_id)

To delete an index:

Drop INDEX Emp01_id_ix

When do I create an index?
You can create an index in the following situations:
A wide range of data values are distributed in columns
Columns often appear in a WHERE clause or join condition
Tables are often accessed and data volumes are large, and the data accessed is roughly 2% to 4% of the total data
When do I not create an index?
Do not create an index in the following situations:
The table is small.
Columns are not often used as join conditions or appear in a WHERE clause
Data queried is greater than 2% to 4%
Table updated frequently
Synonyms-synonym
Use synonyms to access the same objects:
Easy access to other users ' objects
Shorten the length of the object name

CREATE synonym E for employees;select * from E;

SQL Basic Learning Note (v)-sequence, index, synonym