Oracle_ sequence, index, synonym



① sequence
1. Sequence: A database object that can be used by multiple users to produce unique values
offer a unique value on your own initiative
Shared objects
Primarily used to provide primary key values
Loading sequence values into memory improves access efficiency

2.CREATE SEQUENCE Statement
CREATE SEQUENCE SEQUENCE
[INCREMENT by N]--the value of each increment
[Start with N]-from which value starts
[{MAXVALUE n | Nomaxvalue}]
[{MINVALUE n | Nominvalue}]
[{CYCLE | Nocycle}] – Need to cycle
[{CACHE n |  NOCACHE}]; --whether to cache login
3. 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;
Assuming the NoCache option is specified, the column Last_number displays the next valid value in the sequence

4.NEXTVAL and Currval pseudo-columns
Nextval returns the next valid value in the sequence, regardless of what the user can refer to
Current value of the stored sequence in the Currval
Nextval should be specified before Currval, otherwise it will report an error currval has not been defined in this session.

5. Using sequences
Load sequence values into memory for increased access efficiency
The sequence appears cracked in the following cases:
Rolling back
System exceptions
Multiple tables use the same sequence at the same time
Assuming that the value of the sequence is not loaded into memory (NOCACHE), you can use table User_sequences to view the current valid values of the sequence

6. Change sequence
Change the increment, max, min, loop option, or load memory of a sequence
ALTER SEQUENCE Dept_deptid_seq
INCREMENT by 20
MAXVALUE 999999
NOCACHE
Nocycle;
Considerations for altering sequences
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 the method of reconstructing the sequence after deleting the sequence

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

② Index
1. 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 proactively maintains it, and the Oracle management system decides when to use the index. The user does not have to specify which index to use in the query statement
When you delete a table, all indexes based on that table are deleted on their own initiative
Accelerate query speed for Oracle server with pointers
Reduce disk I/O by locating data at high speed

2. Create an index
Create your own initiative: After defining the PRIMARY KEY or UNIQUE constraint, the system itself proactively creates a unique index on the corresponding column
Create manually: Users can create non-unique indexes on other columns to speed up the query

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>

Related instructions
1) UNIQUE | BITMAP: Specifies that unique is a unique value index and BITMAP is a bitmap index. Omitted as B-tree index.

2) <column_name> | <expression> ASC | DESC: Ability to federate multiple columns, when expression is "function-based index"
3) Tablespace: Specifies the tablespace where the index is stored (the index and the original table are more efficient when not a tablespace)
4) STORAGE: Can further set the table space storage parameters
5) LOGGING | Nologging: Whether to generate redo logs for indexes (use nologging for large tables to reduce space consumption and increase efficiency)
6) COMPUTE STATISTICS: Collect statistics when creating new indexes
7) nocompress | COMPRESS<NN>: Whether to use "key compression" (using key compression to remove repeated values in a key column)
8) Nosort | Reverse:nosort to create indexes in the same order as in the table, REVERSE to store index values in reverse order
9) PARTITION | Nopartition: Ability to partition created indexes on partitioned tables and non-partitioned tables

3. When to create an index
The following conditions enable you to create an index:
A wide range of data values are distributed in columns
Columns often appear in a WHERE clause or join condition
The table is often visited and the volume of data is very large, which accounts for about 2% to 4% of the total amount of data

4. When not to create an index
Do not create an index in the following situations:
The table is very small
Columns are not often used as join conditions or are present in the WHERE clause
Data queried is greater than 2% to 4%
Tables are often updated

Note: The index does not need to be used, just to say that we are querying with name. Speed will be faster. Of course, the speed of the check is fast, the insertion speed will be slow.

Because the same time you insert data, you need to maintain an index.

5. Querying the Index
Ability to view index information using the data dictionary view user_indexes and User_ind_columns
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 ';

6. Deleting an index
To delete an index by using the DROP INDEX command
DROP index Index;
Only the owner of the index or the user with the drop any index permission is able to delete the index
The delete operation is not rolled back

③ Synonyms-synonym
Use synonyms to access the same objects:
Easy access to other users ' objects
Shorten the length of the object name
CREATE [public] synonym synonym
for object;

1. Create and delete synonyms
Create synonyms for View Dept_sum_vu
CREATE synonym D_sum
For Dept_sum_vu;

2. Delete synonyms
DROP synonym D_sum;

Oracle_ sequence, index, synonym