Oracle: Other data Objects (sequence, index, synonym)

1. Sequence (sequence)

sequence : A collection of database objects that can be used by multiple users to produce unique values
Automatically provide unique values
Shared objects
Mainly used for primary key self-increment
Loading sequence values into memory can improve access efficiency
Create :
CREATE SEQUENCE SEQUENCE
Increment by n
Start with N
MaxValue N | Nomaxvalue
MinValue N | No MaxValue
Cycle | Nocycle
Cache | NoCache

Query sequence:
Query data dictionary View user_sequences get sequence definition information
SELECT Sequence_name, Min_value, Max_value,
Increment_by, Last_number
From User_sequences;
Note: If you specify the NoCache option, the column Last_number displays the next valid value in the sequence

   获取序列下一个有效值：sequence_name.nextval   获取存放序列的当前值：sequence_currval   NEXTVAL 应在 CURRVAL 之前指定，否则会报错。

the sequence will crack in a moment:
1. Rollback
2. System exceptions
3. Multiple tables using one sequence at a time

To Modify a sequence:
ALTER SEQUENCE SEQUENCE
Increment by n
MaxValue N | Nomaxvalue
MinValue N | No MaxValue
Cycle | Nocycle
Cache | NoCache
Note that there is no start with

To delete a sequence:
DROP sequence Sequence_name

2. Index

Concept :
1. A table-independent schema object that can be stored in a different disk or table space than the table
2. The index is damaged or deleted, it will not affect the table, only affect the query speed
3. Once the index is established, Oracle automatically maintains it, and Oracle decides when to use the index, and the user cannot specify
4. When a table is deleted, an index based on that table is automatically deleted
5. Accelerate the query speed of Oracle servers with pointers

To create an index:
Auto-Create: Creates a unique index after the corresponding column after the primary key and unique constraints are defined automatically
Create manually:
CREATE Index Index

when to create an index:
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 not to create an index:
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

It is important to note that the creation of an index is also a certain amount of resources, 21 when using the index will speed up the query, the corresponding insertion speed is slow, because also to maintain the index

Query Index: Use the data dictionary view user_indexs,user_ind_columns to view index information
SELECT Ic.index_name, Ic.column_name,
Ic.column_position col_pos,ix.uniqueness
From User_indexes IX, User_ind_columns IC
WHERE Ic.index_name = Ix.index_name

To delete an index:
DROP INDEX index_name;

3. Synonyms (synonym[' s?n?n?m])

Use synonyms to access the same object
Shorten the length of the object name
To create a synonym:
CREATE synonym Synonym_name
For object
To delete a synonym:
DROP synonym Synonym_name

To illustrate, so far, the basic Oracle knowledge has been learned, about the stored procedures and triggers will be gradually mastered in the subsequent learning, the next will learn the knowledge of JDBC, the summary of Oracle will be completed in the near future, Oracle's practice also need to do more, mainly sub-query, Of course, I think the summary is also very important, learning is one hand, but the summary is the promotion of time, when all knowledge is memorized in the heart, you learned the knowledge here, otherwise you just learned this knowledge, procedures this thing, more practice more summary. Believe in myself, I can. encourage each other

Oracle: Other data Objects (sequence, index, synonym)