Oracle: Other Data Objects (sequences, indexes, synonyms)

Oracle: Other Data Objects (sequences, indexes, synonyms)
1. sequence)

Sequence: A set of database objects that can be used by multiple users to generate unique values.
Unique value automatically provided
Shared object
It is mainly used for primary key auto-increment.
Loading Sequence values into memory improves 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 the data dictionary view USER_SEQUENCES to obtain the sequence definition information.
SELECT sequence_name, min_value, max_value,
Increment_by, last_number
FROM user_sequences;
Note: If the NOCACHE option is specified, the LAST_NUMBER column displays the next valid value in the sequence.

Get the next valid value of the sequence: sequence_name.nextval get the current value of the stored sequence: sequence_currval NEXTVAL should be specified before CURRVAL; otherwise, an error is reported.

Cracks may occur in the sequence below:
1. rollback
2. System exception
3. Multiple tables use a sequence at the same time

Modify sequence:
Alter sequence sequence
Increment by n
Maxvalue n | nomaxvalue
Minvalue n | no maxvalue
Cycle | nocycle
Cache | nocache
Note that no start

Delete sequence:
DROP sequence sequence_name

2. Index

Concept:
1. A table-independent schema object that can be stored on disks or other tablespaces different from the table
2. If the index is damaged or deleted, the query speed is not affected.
3. Once an index is created, Oracle automatically maintains it. Oracle determines when to use the index, and users cannot specify
4. When a table is deleted, the index based on the table is automatically deleted.
5. Accelerate the query speed of the Oracle server through pointers

Create an index:
Automatic Creation: After the primary key and unique constraints are defined, the unique index is automatically created after the corresponding column.
Manual creation:
Create index index

When to create an index:
A wide range of data values in a column
Columns often appear in the WHERE clause or join condition.
Tables are frequently accessed and the data volume is large. The accessed data accounts for about 2% to 4% of the total data volume.
When to create an index:
The table is small.
The column is not often used as a join condition or appears in the WHERE clause.
The queried data is greater than 2% to 4%
Frequently updated tables

Note that:Creating an index also accounts for a certain amount of resources. When using an index, the query speed is faster and the insert speed is slower, because the index must be maintained.

Query index:Use the data dictionary view user_indexs and 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

Delete An index:
Drop index index_name;

3. Synonyms (synonym ['s? N? N? M])

Use synonyms to access the same object
Shorten the Object Name Length
Create synonym:
Create synonym synonym_name
FOR object
Delete synonym:
Drop synonym synonym_name

So far, the basic Oracle knowledge has been completed. The stored procedures and triggers will be gradually learned in the future. Next, we will learn about jdbc and the summary of Oracle, in the near future, Oracle exercises also need to be done, mainly for subqueries. Of course, I think the summary is also very important. Learning is one aspect, but the summary is the time for improvement, when you keep all your knowledge in mind, you will learn the knowledge here. Otherwise, you will have learned the knowledge and summarized the program. Believe me, you can. Self-encouragement.