Common database objects

What is a sequence?
Sequence: a database object that can be used by multiple users to generate unique values
Automatically provide unique values
Shared object
It is mainly used to provide primary key values
Loading sequence values into memory can improve access efficiency

CREATE SEQUENCE statement
Define sequence:

CREATE SEQUENCE sequence
       [INCREMENT BY n]  --Value of each increase
       [START WITH n]    --Which value to start with
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]     --Is a cycle required
       [{CACHE n | NOCACHE}];  --Cache login

Create sequence
Create sequence DEPT_DEPTID_SEQ provide primary key for table DEPARTMENTS
Do not use the CYCLE option

CREATE SEQUENCE dept_deptid_seq
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NOCACHE
                NOCYCLE;
Sequence created.

Create sequence seq;
Select seq.nextval from dual;
Insert into emp values(seq.nextval,'c');

among create table emp as select employee_id,last_name name from employees where 1=2;

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;

If the NOCACHE option is specified, the column is LAST_NUMBER displays the next valid value in the sequence

NEXTVAL and CURRVAL pseudo columns
NEXTVAL returns the next valid value in the sequence, which can be referenced by any user
The current value of the sequence is stored in CURRVAL
NEXTVAL should be specified before CURRVAL, otherwise an error that CURRVAL has not been defined in this session will be reported.

Sequence application example

INSERT INTO departments(department_id, 
            department_name, location_id)
VALUES      (dept_deptid_seq.NEXTVAL, 
            'Support', 2500);
1 row created.

Sequence DEPT_DEPTID_SEQ current value of

SELECT	dept_deptid_seq.CURRVAL
FROM	dual;

Use sequence
Loading sequence values into memory improves access efficiency
The sequence cracks under the following conditions:
RollBACK
System exception
Multiple tables use the same sequence at the same time
If you do not load the values of the sequence into memory (NOCACHE), you can use the table USER_SEQUENCES view the current valid values of the sequence

Modify sequence
Modify the increment, maximum value, minimum value, loop option of the sequence, or whether to load memory

ALTER SEQUENCE dept_deptid_seq
               INCREMENT BY 20
               MAXVALUE 999999
               NOCACHE
               NOCYCLE;
Sequence altered.

Considerations for modifying sequences

Must be the owner of the sequence or have access to the sequence ALTER jurisdiction
 Only future sequence values will be changed
 Changing the initial value of the sequence can only be realized by deleting the sequence and reconstructing the sequence

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

Indexes
Indexes:
A table independent schema object that can be stored on a different disk or table space than a table
If the index is deleted or damaged, it will not affect the table, but the speed of query
Once the index is established, the Oracle management system will automatically maintain it, and the Oracle management system will decide when to use the index. The user does 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 Oracle server through pointer
Reduce disk I/O by quickly locating data

Create index
Auto create: after defining the PRIMARY KEY or UNIQUE constraint, the system automatically creates a UNIQUE index on the corresponding column
Manual creation: users can create non unique indexes on other columns to speed up queries

Create an index on one or more columns

CREATE INDEX index
ON table (column[, column]...);

In column last of table EMPLOYEES_ Create index on name

CREATE INDEX 	emp_last_name_idx
ON 		employees(last_name);
Index created.

When is the index created
Indexes can be created when:
The data values in the column are widely distributed
Columns often appear in WHERE clauses or join conditions
Tables are frequently accessed and have a large amount of data. The accessed data accounts for about 2% to 4% of the total data

When not to create an index
Do not create an index when:
The watch is small
Columns do not often appear as join conditions or in WHERE clauses
The data queried is greater than 2% to 4%
Tables are updated frequently

Desc emp;
Create index name_index on emp(name);

The index does not need to be used. It just means that when we query with name, the speed will be faster. Of course, if the search speed is fast, the insertion speed will be slow. Because you need to maintain an index while inserting data.

Query index
You can use the data dictionary view USER_INDEXES and USER_IND_COLUMNS view the information of the index

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 = 'EMPLOYEES';

Delete index
Use the DROP INDEX command to delete an index

DROP INDEX index;

Delete index UPPER_LAST_NAME_IDX

DROP INDEX upper_last_name_idx;
Index dropped.

Only the owner of the index or the user with DROP ANY INDEX permission can delete the index
The delete operation cannot be rolled back

Synonym synonym
Use synonyms to access the same object:
Objects that facilitate access to other users
Shorten the length of object names

CREATE [PUBLIC] SYNONYM synonym
FOR    object;

CREATE SYNONYM e FOR employees;
select * from e;

Create and delete synonyms
Set dept for view_ SUM_ Vu create synonyms

CREATE SYNONYM  d_sum
FOR  dept_sum_vu;
Synonym Created.

drop synonym

DROP SYNONYM d_sum;
Synonym dropped.

Summary
Use sequence
Using indexes to improve query efficiency
Define synonyms for data objects