Oracle sequence, index, and synonym

Oracle sequence, index, and synonym

A simple record of knowledge about Oracle sequences, indexes, and synonyms.

1. Common database objects

Ii. Sequence

Sequence: a database object that can be used by multiple users to generate unique values.

• Unique value automatically provided
• Shared object
• It is mainly used to provide the primary key value.
• Loading Sequence values into memory improves access efficiency

① Create a sequence

Create sequence sequence
[Increment by n] -- the value of each increase
[Start with n] -- START from
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] -- whether loop is required
[{CACHE n | NOCACHE}]; -- indicates whether to log on to the CACHE.

Create sequence dept_deptid_seq
Increment by 1
Start with 1
Max value 9999
NOCACHE
NOCYCLE

Insert into orders ments (department_id, department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL, 'support', 2500 );

② NEXTVAL and CURRVAL pseudo Columns

• NEXTVAL returns the next valid value in the sequence, which can be referenced by any user.
• Store the current value of the sequence in CURRVAL

CURRVAL should be specified after NEXTVAL; otherwise, an error not defined in this session will be reported.

③ Data dictionary USER_SEQUENCES

SELECT sequence_name, min_value, max_value,
Increment_by, last_number
FROM user_sequences;

• Query the data dictionary view USER_SEQUENCES to obtain the sequence definition information.
• If the NOCACHE option is specified, the LAST_NUMBER column displays the next valid value in the sequence.

④ Use sequence

1. Loading Sequence values into memory improves access efficiency

2. Cracks may occur in the sequence in the following cases:

• Rollback
• System exception
• Multiple tables use the same sequence at the same time

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

⑤ Modify the sequence

Alter sequence dept_deptid_seq
Increment by 20
Max value 999999
NOCACHE
NOCYCLE;

6. Notes for Sequence Modification

• It must be the sequence owner or have the ALTER permission on the sequence.
• Only future sequence values will be changed
• Changing the initial value of a sequence can only be achieved by deleting the sequence and recreating the sequence.

7. Delete Sequence

• Use the drop sequence statement to delete a SEQUENCE
• After deletion, the sequence cannot be referenced again

Drop sequence dept_deptid_seq;
Sequence dropped.

Iii. Index

• A table-independent schema object that can be stored in a disk or tablespace different from a table.
• If an index is deleted or damaged, it does not affect the table. Only the query speed is affected.
• Once an index is created, the Oracle management system automatically maintains the index, and the Oracle management system determines when to use the index. You do not need to specify which index to use in the query statement.
• When a table is deleted, all indexes based on the table are automatically deleted.
• Accelerate the query speed of the Oracle server through pointers
• Reduces disk I/O by quickly Locating data

① Create an index

• Automatic Creation: After the primary key or UNIQUE constraint is defined, the system automatically creates a UNIQUE index on the corresponding column.
• Manual creation: You can create non-unique indexes on other columns to accelerate queries.

Create an index on one or more columns

Create index index
ON table (column [, column]...);

Create an index on LAST_NAME In the EMPLOYEES column of the table.

Create index emp_last_name_idx ON employees (last_name );
Index created.

② Query Indexes
You can use the data dictionary views USER_INDEXES and USER_IND_COLUMNS to view the 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
AND ic. table_name = 'ployees ';

4. synonym-synonym

Create synonym e FOR employees;
Select * from e;

Create a synonym for view DEPT_SUM_VU

Create synonym d_sum
FOR dept_sum_vu;
Synonym Created.

Delete Synonym

Drop synonym d_sum;
Synonym dropped.

Https://www.bkjia.com/topicnews.aspx? Tid = 12

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/151090.htm