Oracle Foundation (6): sequence, view, index

Time:2020-10-27

1、 Sequence

Sequence is a special object in Oracle database, which can generate continuous integer values. It can be used by multiple objects in the database, and is mainly used as the primary key value.
1. Create sequence
Start value with:1
Increment by: 1
Maximum value: maxvalue:100
Minimum value: minvalue:1
Cycle | nocycle
Buffer: cache 20 | nocache (default: 20)
  create sequence s1;
Note: a) the position of each option is not in order
B) when increment by N is a positive number,
The default maxvalue is: 1E + 27, and the default minValue is: 1
When increment by N is negative,
The default maxvalue is: – 1, and the default minValue is: – 1E + 26
Increment by must be a non-zero integer,
With or without nomaxvalue, this is the default value
C) cache n can pre generate 15, 20 at a time, and put them into memory to improve performance.
Do not write default cache 20
D) the cycle is used for the cycle, and the cycle after the maximum number is reached (not recommended)
2. Use sequence
Next Val: take the next value of the sequence
Curval: de sequence the current value
    select s1.nextval from dual;
    select s1.currval from dual;
  
    create sequence s2
    start with 100
    increment by 2;
  
    select s2.nextval from dual;
    select s2.currval from dual;
Note: execute nextval before enabling, and then query the current value
/ / relationship between sequence and table
    insert into s_stu(id,name)
    values(s2.nextval,’rui’);
3. Modify the sequence
ALTER SEQUENCE sequence
 [INCREMENT BY n]
 [{MAXVALUE n | NOMAXVALUE}]
 [{MINVALUE n | NOMINVALUE}]
 [{CYCLE | NOCYCLE}]
 [{CACHE n | NOCACHE}]
Note: sequence cannot modify the starting value
    alter sequence s_stu_id
    increment by -2;
4. Delete the sequence
   drop sequence sequenceName

2、 View

1. View
A view is part of the data of one or more tables. It can perform CRUD (create, retrieve, update, delete) operations like a table, but it has no specific storage data structure. It exists in the database in the form of a select statement.
Essence: a named select statement
Performance: part of one to more tables
Function: improve the efficiency of retrieval
Table 10000 data
10 pieces of frequently used data are queried out and placed in the view.
Query the created view from the data dictionary table:
   select view_name
   from user_views;
2. Create a view
Create view view name
As subquery
   eg:
/ / give briup permission to create views
      conn system/system;
      grant create view to briup;
  
    create view v1
    as
    select id,last_name,salary,dept_id
    from s_emp
    where dept_id=41;
  
 CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(alias[, alias]…)]
 AS subquery     
[with check option [constraint constraint constraint name]]
 [WITH READ ONLY]
Note: a) or replace: if there is a view object with the same name, replace it.
B) force: if the source table does not exist in subquery, first create the view object. At this time, the view object is created, but it cannot be used normally.
The default value is noforce, that is, the source table does not exist and the view cannot be created normally.
C) with check option: indicates that you can insert, update, and delete,
But the condition of where clause should be satisfied.
D) query of view can use complex select syntax, including join / group query and subquery
E) without with check option and with read only, the order by clause cannot be used in the query
F) if the with check option constraint is not named, the system will automatically name it in the form of sys_ Cn
Eg: create a view:
                     create or replace view a 
           as
           select id from s_emp
                     where id<9;
G) with read only: only select operation is allowed.
Note: after modifying (inserting, updating, and deleting) the real table that created the view,
The data viewed through the view is the modified data.
3. View classification
1) simple view
The data comes from a table,
Does not contain functions and groups,
DML operation can be performed;
2) complex view
Data comes from multiple tables,
Contains functions and groups,
DML operation is not allowed
4. Modify the view
Use or replace to replace directly
5. Delete the view
    drop view view_name;

3、 Index

1. Index
Index is a kind of object in Oracle. Database uses it to speed up the retrieval. Almost all relational databases use B * tree class index, which is also the most used. Its tree structure is similar to that of binary tree. It can locate the accessed records quickly according to ROWID.
The table of contents equivalent to a book is the same.
2. Create index
1) automatic index creation
Create primary key constraint and unique key constraint
And make the primary key constraint and unique key constraint
A unique index is automatically created when it takes effect.  
   select user_indexes;
2) create the index manually
    create index in1
    on s_stu(gender);
3. Use index
1) columns are frequently used in where clauses or join conditions
2) the range of column values is wide
3) the tables are large and the number of records is large
4) the percentage of the results returned by the query in the total number of records is within 2 ~ 4%
5) the more indexes, the better
4. Drop index
  DROP INDEX index_name
5. Query automatic index
    select index_name
    from user_indexes
    where table_name=’S_STU’;

Recommended Today

mcmnhdlr.exe What process can be deleted? Mcmnhdl process query

Process file: mcmnhdl or mcmnhdlr.exeProcess name: vsocecktaskProcess category: processes with security risksEnglish Description: mcmnhdlr.exe is vital process for McAfee SecurityCenter and Virusscan Online. Removing this process will dissable the automatic scanning. Chinese reference:mcmnhdlr.exe It is related to McAfee security center.Produced by: network associates, IncOf McAfee VirusScanSystem process: noBackground program: YesNetwork related: noCommon error: n / […]