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