"Oracle" chapter III indexed view sequence

Chapter III Indexed view sequence

A sequence is a database object used to generate a unique, contiguous integer. A sequence is a value that is used to automatically generate a primary key or unique key.

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 sequence number to be generated, and for ascending, its default value is the minimum value. For descending, its default value is the maximum value.

INCREMENT by: used to specify the interval between serial numbers. Its default value is 1. If positive, the resulting sequence is sorted in ascending order, and if negative, the resulting sequence is sorted in descending order

MAXVALUE : Specifies the maximum value that a sequence can generate

Nomaxvalue : If nomaxvalueis specified,Oracle Sets the maximum value of the ascending sequence to ten the maximum value of the descending sequence is set to -1.

MINVALUE : Specifies the minimum value of the sequence. the MINVALUE must be less than or equal to the value of START with and must be less than MAXVALUE .

Nominvalue : If nominvalue is specified ,Oracle Sets the minimum value of the ascending sequence to 1 , or set the minimum value in descending order to -10 of the - The second party.

CYCLE : Specifies that the sequence will continue to generate values from scratch after reaching the maximum or minimum value

Nocycle : Specifies that a sequence cannot continue to generate a value when it reaches the maximum or minimum value

Cache : Use the cache option to pre-assign a set of serial numbers and save them in memory, so that you can access the serial number faster, and when all the serial numbers in the cache are exhausted,Oracle Another set of values will be generated and kept in memory

NOCACHE : Using the NOCACHE option, the serial number is not pre-allocated for faster access, and if the CACHE is ignored when the sequence is created NOCACHE option,Oracle caches the number of serial numbers by default.

The following code creates a sequence named:emp_id , with the serial number starting from ten and automatically growing to 1, with a maximum value of (+ )

C reate sequence mytable_id start with ten increment by 1 MaxValue

The following code Inserts the value produced by the emp_id sequence into the mytable table

INSERT INTO mytable values (Mytableid.nextval, ' Rose ', ' Beijing ', ' [email protected] ', date ' 2007-10-10 ');

The following code modifies the information for the mytable_id sequence. The new maxvalue value is reset and the increment value is set to 2

A lter sequence mytable_id increment by 2 maxvalue;

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

The following code demonstrates the deletion of a sequence

Drop sequence emp_id;

You can get information about a sequence by querying the user_sequence dictionary table.

User_sequence Some columns in a table

Sequence_name	Sequence Name
Min_value	Minimum value
Max_value	Maximum Value
Increment_by	Increment of sequence
Cycle_flag	Whether the sequence is looped, the value is Y or N
Order_flag	Whether the sequence is ordered, the value is Y or N
Cache_size	Number of sequence values saved in memory
Last_number	The last number that the sequence generates or caches

The following query shows detailed information in the User_sequence table

Select  *  from user_sequences

To delete a sequence:

Drop Sequence mytable_id;

To create a 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 . Modified rows cannot be displayed in the view after the view has been modified. Use with check option to prevent the view data from being modified.

--Creates a order_view view in the order_master table and adds a with check option constraints,

-- If modifying the value of the vencode column in the view will violate the WITH check option constraint.

Create or Replace view Order_view as SELECT * from Order_master where vencode= ' V001 ' with CHECK option

-- due to the addition of the WITH check option constraint in the view

Update Order_view set vencode= ' V002 ' where orderno= ' o003 '

-- Create a 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 above

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

Create a view with error:

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;   

Working with Indexes

Unique index:

Unique index ensures that there are no duplicate values in the column that defines the index

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

To Create a unique index using the CREATE UNIQUE index statement

The following code creates a unique index named jobnameindex on 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 rebuild an existing index, which is more efficient than using drop index and Create the index statement re-creates the indexes , and the following statements demonstrate how to rebuild the jobnameindex index:

Alter index JOBNAMEINDEX rebuild;

Combined index:

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

The order of the columns in the index is arbitrary

If all or most of the columns of a composite index are referenced in the WHERE clause of the SQL statement , you can increase the retrieval speed

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

Usually built on columns with values that are continuously growing, so that the data is evenly distributed across the entire index

Using the REVERSE keyword when creating an index

The following code demonstrates creating a reverse key index

Create INDEX Rev_inx on employees2 (salary) reverse

Use the noreverse keyword to rebuild the reverse key index to a standard index:

ALTER index REV_INX rebuild Noreverse;

Bitmap index:

Bitmap indexes are suitable for creating on low cardinality columns

Bitmap indexes do not store ROWIDdirectly, but instead store byte-to- ROWID Mappings

Reduced response time

Space-saving footprint

The following code demonstrates how to create a bitmap index:

Create bitmap index Bit_inx on employees2 (division_id);

Function-based indexing

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

aggregate function cannot appear in an expression

cannot create on a column of LOB type

must have QUERY REWRITE permission at creation time

The following code shows how to create a function-based index

Create INDEX Lowercase_inx on Employees2 (Lower (job_id));

Modify the index, and the following code renames the index.

Alter index Vn_ind Rename to C_vn_ind;

To delete an index:

DROP index cvn_ind;

View the index information you have created

SELECT * FROM User_indexes
View the columns on which the index is based
SELECT * FROM User_ind_columns

"Oracle" chapter III indexed view sequence