Oracle _ sequence, index, synonym, oracle Synonym

Oracle _ sequence, index, synonym, oracle Synonym
Zookeeper

1. Sequence
1. sequence: database objects 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

2. create sequence statement
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.
3. 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;
If the NOCACHE option is specified, the LAST_NUMBER column displays the next valid value in the sequence.

4. 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
NEXTVAL should be specified before CURRVAL; otherwise, an error not defined in CURRVAL in this session will be reported.

5. Use Sequence
Loading Sequence values into memory improves access efficiency
The sequence has cracks in the following cases:
Rollback
System exception
Multiple tables use the same sequence at the same time
If you do not load the sequence value into the memory (NOCACHE), you can use the USER_SEQUENCES table to view the current valid values of the sequence.

6. Modify the sequence
Modify the sequence increment, maximum value, minimum value, loop option, or whether to load the memory
Alter sequence dept_deptid_seq
Increment by 20
Max value 999999
NOCACHE
NOCYCLE;
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

② Cable cited
1. 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

2. 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 uniuqe | bitmap index <schema>. <index_name>
ON <schema>. <table_name>
(<Column_name >|< expression> ASC | DESC,
<Column_name> | <expression> ASC | DESC ,...)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS <nn>
NOSORT | REVERSE
PARTITION | global partition <partition_setting>

Description
1) UNIQUE | BITMAP: specify UNIQUE as the UNIQUE value index, and BITMAP as the BITMAP index, which is omitted as the B-Tree index.
2) <column_name> | <expression> ASC | DESC: you can perform a Union Index on multiple columns. When expression is used, it is a "function-based index"
3) TABLESPACE: Specify the TABLESPACE to store the index (the index and the original table are not in the same TABLESPACE, And the validity rate is higher)
4) STORAGE: You can further set the storage parameters of the tablespace.
5) LOGGING | NOLOGGING: whether to generate redo logs for indexes (use NOLOGGING to reduce the occupied space and improve the efficiency of large tables as much as possible)
6) compute statistics: Collects STATISTICS when creating a new index.
7) NOCOMPRESS | COMPRESS <nn>: whether to use "Key compression" (you can use key compression to delete duplicate values in a key column)
8) NOSORT | REVERSE: NOSORT indicates creating an index in the same order as the table, and REVERSE indicates storing the index value in REVERSE order.
9) PARTITION | NOPARTITION: You can PARTITION the created index in a partitioned table or a non-partitioned table.

3. When to create an index
You can create an index in the following cases:
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.

4. When should I create an index?
Do not create an index in the following cases:
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: indexes are not required, but the query speed is faster when name is used. Of course, the query speed is faster, and the insertion speed will be slow. Because an index must be maintained when data is inserted.

5. 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 ';

6. delete an index
Use the drop index command to delete an INDEX
Drop index index;
Only the INDEX owner or users with the drop any index permission can delete the INDEX.
The delete operation cannot be rolled back.

③ Synonym-synonym
Use synonyms to access the same object:
Convenient access to other user objects
Shorten the Object Name Length
CREATE [PUBLIC] SYNONYM synonym
FOR object;

1. Create and delete Synonyms
Create a synonym for view DEPT_SUM_VU
Create synonym d_sum
FOR dept_sum_vu;

2. Delete Synonyms
Drop synonym d_sum;