1. Sequence

summary

A sequence is a database object that helps tables to create automatically growing primary keys. Sequence is an object held in oracle database, which is not available in other databases.

establish

Default syntax for creating sequences

create sequence Sequence name;

Much like creating a table

The full amount is created as follows:

create sequence Sequence name
[INCREAMENT BY n]
[START WITH n] 
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE| NOCYCLE }]
[{CACHE n | NOCACHE}]

explain:
(1) INCREMENT BY, used to define the step size of the sequence. If omitted, it defaults to 1
(2) The first defined value of the sequence, i.e. with 1, is the initial value of the generated sequence
(3) MAXVALUE, which defines the maximum value that the sequence can produce. NOMAXVALUE is the default value, which means there is no maximum value defined
(4) MINVALUE, which defines the minimum value that the sequence can produce. NOMINVALUE is the default value, which means there is no minimum value defined
(5) CYCLE and NOCYCLE indicate whether to CYCLE when the value of the sequence reaches the limit value
(6) CACHE defines the size of the buffer for storing sequence values. The default is 20. NOCACHE means that the sequence is not buffered in memory

Sequence operation

View the current value of the sequence

select Sequence name.currval from dual;

View the next value of the sequence,

select Sequence name.nextval from dual;

Note that after creating a sequence, you can't use Curval first, because currently there is no Curval. You only need to use nextval before you can use Curval

Delete sequence

drop sequence Sequence name;

Create a sequence and set properties

create sequence test_seq
increment by 5
start with 10
maxvalue 100
minvalue 10
cycle
nocache;

The following table is assumed:

create table t_user(
	id number constraint user_id_primary key,
	name varchar2(100),
	salary number
);

Then create the sequence as follows:

create sequence t_user_seq;

Automatically generate id value through sequence

insert into t_user(id,name,salary) value (t_user_seq.nextval,'tom',2000);

Query the sequence of the current user

select sequence-name
from user_sequences;

2. View

summary

View is a kind of database object. In short, it is a mapping generated by extracting the data of one or more tables. Operate the view to achieve the effect of operating the original table and facilitate data management and safe operation. The main function of the view is to hide the important data in the table and replace the longer sql statement.

classification

1. Simple view
In the sql represented by the view, if there is no group by statement, no group function, and only one table is queried, then such a view is a simple view

2. Complex view
If there are group by statements, group functions, or multiple tables in the sql represented by the view, such a view is a complex view

difference:
The data in the original table can be modified by simple view, but the data in the original table cannot be modified by complex view.

establish

To create a view, the user needs to have the permission to create the view. If not, the user can be empowered

grant create view to tom;

The syntax is as follows

create or replace view View name
as
sql sentence

Simple view

For example, suppose there is a table below

create table t_user
as
select id,last_name,salary
from s_emp;

Create a simple view from this table

create or replace view v_test
as
select id,last_name,salary
from t_user
where id<10;

You can delete, update and insert data from the original table through a simple view

delete from v_test where id = 6;

update v_test set last_name = 'zhangsan' wehre id = 0;

insert into v_test(id,last_name,salary) values (6,'tom1',3000);

select* from v_test;

View view content

select * from v_test

with read only statement
When creating a view, you can add a with read only statement to indicate that data can only be queried through the view and cannot be modified

create or replace view v_test
as 
select *
from t_user
where id<10
with read only;

This view v_test can only be queried in the future and cannot be modified. At this time, if you use the above DML for operation, the data will report an error

with check option statement
When creating a view, you can add a with check option statement to indicate the changes made through the view, which must also be displayed through the view, otherwise the operation will fail.

For example:

create table t_user(
	id number constraint user_id_pk primary key,
	name varchar2(100),
	salary number
);

insert into t_user values(1,'tom1',1000);
insert into t_user values(2,'tom2',2000);

Create a view (view of data with id=2) and add a with check option statement,

create or replace view v_test
as
select id,name,salary
from t_user
where id = 2
with check option;

View data in view

select * from v_test

Can view

insert data

insert into t_test values(3,'tom3',3000);

Failed. This operation cannot be displayed through the view

Update data

update v_test
set name = 'lily'
where id = 1;

Failed. This operation view cannot be displayed

Update data

update v_test
set name = 'lily'
where id=2;

Update successful

Complex view

create or replace view v_test
as 
select avg(salary) avgSal
from t_user;

Complex view is only used for query and cannot be modified

select * from v_test;

View deletion

drop view View name

3. Index

summary

1. Catalogue structure of similar books
2. oracle index 1 is an object, an optional object associated with a table, which can improve the speed of SQL query statements
3. The index points directly to the position of the row containing the queried value, reducing disk I/O
4. Indexes and tables are independent physical structures
5. Oracle automatically uses and maintains the index. After inserting, deleting and updating the table, the index will be updated automatically

Principle: suppose there is a name field in a table. Suppose you want to find the data of name='tom ', but there may be multiple data

1. If there is no index, when searching for this record, you need to search all the records in the table. Because there is no guarantee that there is only one tom, you must search all the data in the table
2. If you build an index on name, oracle will search the whole table once, arrange the name value of each record according to certain rules, and then build index entries and store them. When you query the name as tom, you can directly find the corresponding place of the data.
   The index may not be used after it is created, because oracle will decide whether to use the index after automatically counting the information of the table. When there is little data in the table, the scanning speed of the whole table is very fast, so there is no need to use the index.

Index creation

There are two ways to create an index:

1. Automatic index creation: when the primary Key or unique constraint is specified in the table, the unique value index will be automatically created.
2. Users create indexes: users can create non unique value indexes to improve the efficiency of accessing data.

Syntax:

create index Index name
on Table name(Listing);

View indexes created by the current user

select index_name from user_indexes;

Note that if an index is added to a column, the efficiency of the query can be reflected only when there is a large amount of data. Once the index is established successfully, the index will be managed by the database, which is beyond our control

Principles for creating an index for a column:

1. Columns are often used as qualifiers for where clauses or as join conditions
2. The column contains a large amount of data and many non empty values.
3. Create an index on the order by, group by, and distinct columns, which can be used to speed up the sorting query time;
4. The more indexes, the better. The more indexes, the faster the search.
5. The table to be indexed is not frequently modified

Index deletion

drop index Index name;

Type of index

1. unique index
   When creating an index, the column constraint is a unique constraint or a primary key constraint
2. Non unique index
   When creating an index, the column constraint is not a unique constraint or a primary key constraint
3. single index
4. Multiline index concatenated index

Index structure

Common structures are
1,B-tree
This is the default index structure. Its characteristics are as follows:

1. Suitable for a large number of additions and deletions
2. You cannot use a query that contains an OR operator
3. Suitable for columns with more unique values
4. Typical tree structure

2. Bitmap index
When the data base is relatively small, it is more suitable to build bitmap index. The characteristics are as follows:

1. It is very suitable for the query of OR operator
2. The cost of updating is very high, because oracle needs to UPDATE the index according to each modification of the table
   

You need to check whether the current database supports this index structure, as follows: query whether it has the function of bitmap index

If the value value is false, this version of Oracle does not have the bitmap indexing function

3. Reverse order index
Reverse index is a branch of B-tree index. It mainly reverses the bytes of the index key value of the index column when creating the index. It can disperse the index key values into different nodes.

4. Functional index
When you often query a field with function operation, then the function index can play a role and improve the retrieval speed.

4. Notes

After the table is created, you can use the comment keyword to add comments to the table or column, and the comments can be queried in the future.
For example: add comments to a table

comment on table t_user is 'very good';

View comments in table

select * from user_tab_comments where table_name = upper('t_user');

For example: add a comment to a column

comment on column t_user.name is 'good';

View comments in columns

select * from user_col_comments
where comments is not null
and table_name = upper('t_user');