oracle--sequence, index, synonym

I. Common database objects

Second, sequence

Sequence: A database object 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

①, creating sequences

CREATESEQUENCE SEQUENCE[INCREMENT by N]  --the value of each increment       [START with N]    --from which value to start       [{MAXVALUE N | Nomaxvalue}]       [{MINVALUE N | Nominvalue}]       [{CYCLE | Nocycle}]     --whether a loop is required       [{CACHE N | NOCACHE}];--whether to cache login

CREATEdept_deptid_seq

 by 1
 with 1
9999

Nocycle

INSERT  into departments (department_id, Department_name, location_id) VALUES  'support'2500);

②, Nextval, and currval pseudo-columns

• Nextval returns the next valid value in the sequence that any user can reference
• Current value of the stored sequence in the Currval

Currval should be specified after Nextval, otherwise the error currval has not been defined in this session will be reported.

③, Data dictionary user_sequences

SELECT     sequence_name, Min_value, Max_value,     increment_by, Last_numberfrom    user_ sequences;

• Query data dictionary View user_sequences get sequence definition information
• If you specify the NoCache option, the column Last_number displays the next valid value in the sequence

④, using sequences

1. Loading sequence values into memory improves access efficiency
2, the sequence in the following cases of cracks appear:

• Rolling back
• System exceptions
• Multiple tables use the same sequence at the same time

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

⑤, modifying a sequence

ALTER SEQUENCE dept_deptid_seq                 by  - 999999 NOCACHE               nocycle;                 

⑥, modifying sequence considerations

• Must be the owner of a sequence or have ALTER permission on a sequence
• Only future sequence values will be changed.
• Changing the initial value of a sequence can only be achieved by removing the sequence after the series is reconstructed

⑦, deleting a sequence

• To delete a sequence using the drop SEQUENCE statement
• After deletion, the sequence cannot be referenced again

DROP SEQUENCE Dept_deptid_seq; Sequence dropped. 

Third, index

• A table-independent schema object that can be stored in a different disk or table space than the 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

①, creating an index

• 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

To create an index on one or more columns

CREATE INDEX Index  on Table (column[, Column]...);

To create an index on the column last_name of table employees

CREATE INDEX  on employees (last_name); Index created.

②, query Index

You can use the data dictionary view user_indexes and user_ind_columns to view the information for the index

SELECT     ic.index_name, Ic.column_name,    ic.column_position col_pos,ix.uniquenessfrom     User_indexes IX, User_ind_columns ICWHERE    =  ix.index_name and    ='EMPLOYEES';

Iv. Synonyms-synonym

CREATE  for employees; Select *  from E;

• Create synonyms for View Dept_sum_vu

CREATE synonym  d_sumfor  dept_sum_vu;  Synonym Created. 

• Delete synonyms

DROP synonym D_sum; synonym dropped. 

oracle--sequence, index, synonym