Oracle Foundation Note 11

11th Chapter Other database objects

1. What is a sequence?

Sequence: Provides a regular value.

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

1.2. Defining a Sequence

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

1.3. Create a sequence

Create sequence DEPT_DEPTID_SEQ provides a primary key for table departments without the CYCLE option

CREATE SEQUENCE Dept_deptid_seq

INCREMENT by 10

START with 120

MAXVALUE 9999

NOCACHE

Nocycle;

Example:

Create sequence seq;

Select Seq.nextval from dual;

Insert into EMP values (Seq.nextval, ' C ');

Where CREATE TABLE emp as select Employee_id,last_name name from Employees where 1=2;

Query sequence

SELECTsequence_name, Min_value, Max_value,

Increment_by, Last_number

Fromuser_sequences;

Query data dictionary View user_sequences Gets the sequence definition information, and if the NoCache option is specified, the column Last_number displays the next valid value in the sequence

1.4NEXTVAL 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.

1.5 Sequence Application Examples

INSERT into departments (DEPARTMENT_ID,

Department_name, location_id)

VALUES (Dept_deptid_seq. Nextval,

' Support ', 2500);

1.6 Using sequences

Loading sequence values into memory improves access efficiency

The sequence cracks in the following situations: Rollback, System exception, multiple tables using 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

1.7 Modifying a 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

1.8 Deleting a sequence

To delete a sequence using the drop SEQUENCE statement

After deletion, the sequence cannot be referenced again

DROP SEQUENCE Dept_deptid_seq;

Sequence dropped.

2. Index: Improve the efficiency of queries

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.1 Creating 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

To create an index on one or more columns:

CREATE Index Index

On table (column[, column] ...);

To create an index on the column last_name of table employees:

CREATE INDEX emp_last_name_idx

On Employees (last_name);

Index created.

2.2 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

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

Query index: You can use the data dictionary view user_indexes and user_ind_columns to view the information for the index

Delete Index: Delete index using DROP INDEX command

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

3. Synonym synonym: Alias an object

Use synonyms to access the same objects:

Easy access to other users ' objects

Shorten the length of the object name

For example:

1.CREATE synonym e for employees;

SELECT * from E;

2.CREATE synonym D_sum

For Dept_sum_vu;

Synonym Created.

DROP synonym D_sum;

Synonym dropped.

This article from the "Ah Cheng Blog" blog, reproduced please contact the author!

Oracle Foundation Note 11