Oracle (iii) -- & gt; other database objects (Summary series), oracle --

Oracle (iii) --> other database objects (Summary series), oracle --
Opening section ·_·In addition to views, other database objects include sequences, synonyms, and indexes. The most important part is the sequence (associated with the trigger). synonyms and indexes are only known.I,1. Sequence ConceptDatabase objects that can be used by multiple users to generate unique values1) the unique value is automatically provided.2) shared object3) it is mainly used to provide the primary key value.4) Loading Sequence values into memory improves access efficiency.
2. statements defining sequences
Use of two Sequence attributes (nextval and currval.1) QueryCurrent value:
Next Value
Note:Nextval and currval pseudo Columns1. nextval returns the next valid value in the sequence, which can be referenced by any user.2. Store the current value of the sequence in currval3. nextval should be specified before currval; otherwise, an error not defined in currval in this session will be reported.
The query sequence is shown as follows:
2) Sequence Modification
Change to: Increase by 1 each time, and do not cycle, that is, the table id has no upper limit.After data is inserted:
Notes for sequence modification:1. It must be the sequence owner or have the alter permission on the sequence;2. Only future sequence values will be changed;3. Changing the initial values of a sequence can only be achieved by deleting the sequence and recreating the sequence.
3) sequence usage1. Storing sequence values in memory improves access efficiency2. Cracks may occur in the sequence in the following cases:-- Rollback-- System exception-- One sequence is used for multiple tables at the same time (one table maintains one sequence instead of one sequence for multiple tables)3. If the sequence value is not stored in the memory (nocache), you can use the user_sequences table to view the current valid values of the sequence.
Ii. Indexing1. Concepts1) A table-independent schema object that can be stored in a disk or tablespace different from the table2) if an index is deleted or damaged, it does not affect the table. Only the query speed is affected.3) 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 no longer need to specify which index to use in the query statement.4) When a table is deleted, all indexes based on the table are automatically deleted.5) Accelerate the query speed of the Oracle server through pointers6) Quickly locate data to reduce disk I/O
2. Create an index1) Automatic Creation: After the primary key or unique constraint is defined, the system automatically creates a unique index on the Response column.2) manual creation: You can create non-unique indexes on other columns to accelerate queries.
Example: Create an index on one or more columnsDisplayed after successful creation: Index created
3. When to create an index-- The data value in a column has a wide distribution range.-- Columns are often used in the where clause or join condition (indexes are required if the column is frequently used)-- 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?-- The table is small.-- Columns are not often used as join conditions or appear in the where clause.-- The queried data is greater than 2% to 4%-- Tables are updated frequently.
3. for synonyms, see the document on database objects.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.