[Oracle] Chapter 3 index view sequence and Chapter 3 View

[Oracle] Chapter 3 index view sequence and Chapter 3 View

Chapter 3 index view Sequence

Sequences are database objects used to generate unique, continuous integers. A sequence is used to automatically generate a primary key or a unique key value.

Create sequence sequence_name

Start with integer

Increment by integer

MAXVALUE integer | nomaxvalue

MINVALUE integer | nominvalue

CYCLE | NOCYCLE

CACHE integer | nocache;

Start with refers to the first serial number to be generated. For Ascending Order, the default value is the minimum value. The default value for descending order is the maximum value.

Increment by: used to specify the interval between serial numbers. The default value is 1. If the value is positive, the generated sequence is in ascending order. If the value is negative, the generated sequence is in descending order.

MAXVALUE: specifies the maximum value that can be generated for a sequence.

NOMAXVALUE: If NOMAXVALUE is specified, Oracle sets the maximum value of the ascending sequence to the 27 power of 10, and sets the maximum value of the descending sequence to-1.

MINVALUE: specifies the minimum value of the sequence. MINVALUE must be smaller than or equal to the value of start with and smaller than MAXVALUE.

NOMINVALUE: If NOMINVALUE is specified, Oracle sets the minimum value of the ascending sequence to 1, or the minimum value of the descending sequence to the power 26 of-10.

CYCLE: After the sequence reaches the maximum or minimum value, it will continue to generate values from the beginning.

NOCYCLE: When the sequence reaches the maximum or minimum value, the value cannot be generated.

CACHE: You can use the CACHE option to pre-allocate a set of serial numbers and store them in the memory, so that you can access the serial numbers faster. When all serial numbers in the CACHE are used up, oracle will generate another set of values and keep them in memory

NOCACHE: If the NOCACHE option is used, the access speed is not accelerated and the serial number is pre-allocated. If the CACHE and NOCACHE options are ignored during sequence creation, Oracle caches 20 serial numbers by default.

The following code creates a sequence named "emp_id" (The serial number is automatically increased to 1 from 10 and the maximum value is 2000)

Create sequence mytable_id start with 10 increment by 1 maxvalue 2000

The following code inserts the values generated by the emp_id sequence into the mytable table

Insert into mytable values (mytableid. nextval, 'Rose ', 'beijinging', 'Rose @ sdi.com', date '2017-10-10 ');

The following code modifies the information of the mytable_id sequence. Reset the new maxvalue value and set the increment value to 2.

Alter sequence mytable_id increment by 2 maxvalue 5000;

* ******** Note: the start with parameter of the sequence cannot be modified.

The following code deletes a sequence:

Drop sequence emp_id;

You can query the user_sequence dictionary table to obtain information about the sequence.

Some columns in the user_sequence table

Sequence_name	Sequence name
Min_value	Minimum value
Max_value	Maximum Value
Increment_by	Sequence Increment
Cycle_flag	Whether the sequence is cyclic. The value is Y or N.
Order_flag	Whether the sequence is ordered. The value is Y or N.
Cache_size	Number of sequential values stored in memory
Last_number	The last number generated or cached by the sequence

The following query displays the details in the user_sequence table.

Select * from user_sequences

Delete sequence:

Drop sequence mytable_id;

Create View:

A view is a predefined query on one or more tables.

The following code creates the order_view view in the order_master table:

Create or replace view order_view as select * from order_master where vencode = 'v001'

Use with check option in the view. After the view is modified, the modified rows cannot be displayed in the view. Use with check option to prevent the View data from being modified.

-- Create the order_view view in the order_master table and add the with check option constraint,

-- Modifying the value of the vencode column in the view violates the with check option constraint.

Create or replace view order_view as select * from order_master where vencode = 'v001' with check option

-- The with check option constraint is added to the view.

Update order_view set vencode = 'v002 'where orderno = 'o003'

-- Create read-only view

Create or replace view order_viewOnly as select * from order_master where vencode = 'v001' with read only

Select * from order_viewonly

-- Try to modify the read-only view

Update order_viewonly set ostatus = 'C' where orderno = 'v001'

Create an error View:

The following statement creates a view based on the table venmast, but there is no table named "venmast" in the database:

Create force view vtable as select * from viewtable;

Use Index

Unique index:

Unique indexes ensure that no duplicate values exist in the columns that define the index.

Oracle automatically creates a unique index on the primary key column of the table.

Use the create unique index statement to CREATE a UNIQUE INDEX

The following code creates a unique index named jobnameindex in the name column of the jobs table:

Create unique index jobnameindex on jobs (name );

The rebuild option of the alter index statement can be used to re-create an existing index, which is more efficient than using the drop index and create index statements to re-create an index. The following statements demonstrate how to re-create a jobnameindex index:

Alter index jobnameindex rebuild;

Combined index:

A composite index is an index created on multiple columns of a table.

The column order in the index is arbitrary.

If the WHERE clause of an SQL statement references all or most columns of the composite index, the retrieval speed can be improved.

The following code creates a composite index in the employee2 table:

Create index comp_index on employees2 (first_name, last_name );

Reverse key index:

Reverse key index reverses each byte of the index column key value

Normally, the data is evenly distributed on the entire index based on columns whose values are continuously increasing.

Use the REVERSE keyword when creating an index

The following code creates a reverse key index:

Create index rev_detail on employees2 (salary) reverse

Use the noreverse keyword to re-create the reverse key index as the Standard Index:

Alter index rev_1_rebuild noreverse;

Bitmap index:

Bitmap indexes are suitable for low-base columns.

Bitmap indexes do not directly store ROWID, but store the rowing between byte and ROWID.

Reduce response time

Space saving

The following code creates a bitmap index:

Create bitmap index bit_region on employees2 (division_id );

Function-based index

Indexes created based on functions or expressions on one or more columns

Aggregate functions cannot appear in expressions.

Cannot be created in a column of the LOB type.

The query rewrite permission must be granted during creation.

The following code creates a function-based index:

Create index lowercase_partition on employees2 (lower (job_id ));

Modify the index. The following code renames the index.

Alter index vn_ind rename to c_vn_ind;

Delete An index:

Drop index cvn_ind;

View created index information

Select * from user_indexes
View index-based Columns
Select * from user_ind_columns