Oracle_ sequence, index, synonym



① sequence
1. 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

2.CREATE SEQUENCE Statement
CREATE SEQUENCE SEQUENCE
[INCREMENT by N]--the value of each increment
[Start with N]-from which value begins
[{MAXVALUE n | Nomaxvalue}]
[{MINVALUE n | Nominvalue}]
[{CYCLE | Nocycle}]--Do you need a loop
[{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;
If you specify the NoCache option, 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 that any user can reference
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
Loading sequence values into memory improves access efficiency
The sequence appears cracked in the following cases:
Rolling back
System exceptions
Multiple tables use the same sequence at the same time
If you do not load the value of the sequence into memory (NOCACHE), you can use table User_sequences to view the current valid values of the sequence

6. Modifying the sequence
Modify 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 Modifying a sequence
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

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 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

2. Create 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

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, BITMAP is a bitmap index, and is omitted as a B-tree index.
2) <column_name> | <expression> ASC | DESC: Multiple columns can be indexed together, 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 duplicate values in a key column)
8) Nosort | Reverse:nosort to create indexes in the same order as the table, REVERSE to store index values in reverse order
9) PARTITION | Nopartition: You can partition created indexes on partitioned tables and non-partitioned tables

3. When to create an index
You can create an index in the following situations:
A wide range of data values are distributed in columns
Columns often appear in a WHERE clause or join condition
Tables are often accessed and data volumes are large, and the data accessed is roughly 2% to 4% of the total data

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

Note: The index does not need to be used, just that we are using the name to query the time, the speed will be faster. Of course, the speed of the check is fast, the insertion speed will be slow. Because you are inserting data, you need to maintain an index.

5. Querying the 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.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 a user with the drop any index permission can 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