A detailed explanation of view sequence synonym index in Oracle

1, View

1. What is a view

  • In short, a view is a virtual table, a mapping of entity tables

 

 

 

When to use views

    • In development, there are some table structures that do not want too many people to contact, so entity tables are mapped to a view. (simplified table structure)

    • In the process of project, programmers mainly focus on coding performance and business analysis. For some complex SQL statements, the designer will encapsulate them into a view in advance for the programmer to call

 

 

2. Basic operation of view:

1) Simplified version creation

--Grammar
create view view name as query statement;

 

Create view

create  view  view_owners 
as select * from t_owners;

 

Location after view creation:

 

 

2 delete view

--Grammar
drop view view name;

drop view view_owners;

 

3. Query

select * from view_owners;

 

 

3) Specific operation: add, delete and modify

--4.Addition, deletion and modification
--increase
insert into view_owners values(11,'Meimei supermarket',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 );
commit;

--change
update view_owners set name='Hair and hair' where  id=11;
commit;

--Delete
delete from view_owners where id=11;
commit;

 

 

 

3. View with conditions (detection)

Add constraint checking to views

  • re replace, if the view already exists, it will overwrite (replace)

  • with check option to check the valid data (add / update data, which must be able to be queried)

--Conditional view

--Prepare data
select * from t_owners ow where ow.ownertypeid=1;

--1;Create view
-- or  replace, If the view already exists,Will overwrite(replace)
--with check option ,Check valid data(add to/Update data,Must be able to find out)


create or replace  view view_owners1
as select * from t_owners ow where ow.ownertypeid=1
with check option;


--2.Query data
select * from view_owners1 ;


--3.Add data(Wrong conditions,ownertypeid=3 Does not match the original condition,report errors;Violation of unique constraints)
----error; insert  into view_owners1 values(11,'Meimei supermarket',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 );

--After adding successfully; It will be in the original t_owners Add a piece of data first,then view_owners1 Also add,We can find out in the end
insert  into view_owners1 values(11,'Meimei supermarket',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),1 );

commit;


--delete
drop view view_owners1;
delete from t_owners ow where ow.id=11;

 

 

4. Forced creation

force forces the view to be created, regardless of whether the query statement is incorrect or not

--0.preparation,Query table data,Table does not exist
select * from temp;

--1;Create view
create force view view_owners2
as select * from temp;

 

 

 

5. Read only view

  • with read only, the view must be read-only.
--0.preparation,Query owner table
select * from t_owners;

--1.Create read-only view
create view view_owners3
as select * from t_owners
with read only ;


--2.query
select * from view_owners3;

--3.add to--not allow
insert  into view_owners3 values(12,'Meimei supermarket 11',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),1 );

 

 

6. Associated query

1) Key retention table, non key retention table

  • --Key reservation table; which physical table does the view primary key come from? This table is called 'key reservation table'
  • --Non key retention tables; other tables are called 'non key retention tables'
  • --Feature: in the view generated by associated query, only data in 'key retention table' can be added / modified

 

 

Summary: which views cannot be updated?

1 simple view:

* conditions cannot be modified for those with check constraints

* read only view

2 complex view

* non key retention table

  • Key retention table: the primary key in the original table and also in the view

* views using aggregate functions

 

For example:

--grammar:
create [or replace] [force] view View name
as Query statement
[with check option]
[with read only]
-- or replace cover
-- force force
-- with check option Valid data check
-- with read only Read-only views 



-- 0 get ready sql 
select ow.id Owner No,ow.name Name of owner,ot.name Type name
 from t_owners ow 
 inner join t_ownertype ot on ow.ownertypeid = ot.id

--1.Create view
create or replace view view_owners4 
as select ow.id Owner No,ow.name Name of owner,ot.name Type name
 from t_owners ow 
 inner join t_ownertype ot on ow.ownertypeid = ot.id


--2.Query data
select * from view_owners4;

--3.Update data
--3.1 Key retention table
update view_owners4 set Name of owner='Wang Xiao' where Owner No=2;
commit;

--3.2 Non key retention table(Non key value retention table)--Execution unsuccessful
update view_owners4 set Type name = 'Administrative institutions' 
where Owner No = 2;
commit;

 

 

Summary:

create [or replace] [force] view view name
as query statement
[with check option]
[with read only]

--or replace override
--Force force
--with check option
--with read only view

  • View 1 is actually a virtual table, and its data is actually from the table.
  • 2 if you change the data of the view, the data of the table will change naturally
  • 3 if the data of the table is changed, the view will change naturally.
  • 4 what a view stores is not data, but an SQL statement.

 

 

2, Sequence

1. What is sequence

  • Sequence: database object used to generate unique numbers.

    • Equivalent to MySQL auto grow column (auto_increment)

  • Sequence purpose: maintain automatic growth of primary key in the table.

 

 

2. Grammar

Simplified version:

create sequence serial number;

 

Full version:

create sequence serial number
start with
increment by step
maxvalue Max
minvalue minimum

 

3. Create

--Simplified version
create sequence seq_stuno;

 

-- Full version
create sequence seq_stuno2
start with 2
increment by 3
maxvalue 20
minvalue 2

 

 

4. Use


--use
---Query to get sequence value
--query, nextval Get next sequence
select seq_stuno2.nextval from dual;




--Add auto maintain primary key
create table tt_demo(
id int primary key,
name varchar2(50)
);

insert into tt_demo(id,name)values(seq_stuno.nextval,'Zhang San');
insert into tt_demo(id,name)values(seq_stuno.nextval,'Li Si');
insert into tt_demo(id,name)values(seq_stuno.nextval,'Wang Wu');

commit;

 

 

 

3, Synonyms

1. What are synonyms?

  • Synonyms are aliases. You can alias tables, views, and so on.

  • Synonym meaning:

    • Synonyms allow base objects to be renamed or moved. In this case, only synonyms need to be redefined, and synonym based applications can continue to run without modification.

 

 

2. Synonym classification

  • Private synonym: only user can access

  • Public synonyms: accessible to all users of the database

 

Synonym itself does not involve security. When you give a synonym object permission, you are essentially giving permission to the base object of synonym. Synonym is just an alias of the base object.

effect:

  1. Easy access
  2. Shorten the length of object name
  3. Improve security

 

3. Create and delete

establish

  • private synonym

create synonym name for table name view name

 

  • Common synonyms
create public synonym name for table name view name

 

 

delete

  • private synonym

drop synonym name
  • Common synonyms
drop public synonym name

 

For example:

Note: - when using synonym query, you need to use 2 users. Public synonyms can share tables between two users


--practice
--1 Create private synonyms
create synonym owners for t_owners ;

--After creation,Data can be queried
select * from owners;



---2 Create common synonyms
create public synonym owners2 for t_owners;

--After creation,Data can be queried
select * from owners2;



---delete
drop synonym owners;

drop public synonym owners2;

 

 

 

 

4, Index

1. What is index?

  • --Index is a sort data structure in database management system, which helps to query and update data in database tables quickly
  • --An index is a data object used to speed up data access. Reasonable use of index can greatly reduce the number of i/o and improve the performance of data access.
  • --Index purpose; improve data access performance
  • --Index needs physical storage, using space for time

 

2. Index classification

  • Normal index: declare a column with the keyword index

  • Unique index: columns declared with the keyword unique

  • Compound index: declare multiple columns with the keyword index

 

3. General index

1) Grammar

create index index name on table name (column name);

 

2) Prepare data

--2 Prepare data
--1).Create table
create table tt_indextest(
id number,
name varchar2(30)
);

--2).Import 1 million pieces of data in batch
begin     --Batch start
  for i in 1..1000000   --loop: for variable  in Start value..end
  loop            --Beginning of circulatory body
    insert into tt_indextest(id,name) values(i,'AA'||i);
    end loop;    --End of circulatory body
    commit;
end;  --Batch end

--How many data to view
select count(*) from tt_indextest;

 

3) Test performance

--3.Test performance
--3.1 query(id,name)--The two results are basically the same
--0.049
select * from tt_indextest where id=765432;
--0.047
select * from tt_indextest where name='AA765432';


--3.2 Add index
create index tt_indextest_name on tt_indextest(name)

--3.3 Query again --Indexed
select * from tt_indextest where id=765432;
select * from tt_indextest where name='AA765432';

 

Test id query performance

--- Implementation plan
explain plan for select * from t_indextest where id = 765432;
select * from table(dbms_xplan.display());

 

Test name query performance (Index added)

explain plan for select * from t_indextest where name = 'AA765432';
select * from table(dbms_xplan.display());

 

 

4. Unique index

If we need to create an index on a column of a table, the value of this column will not be repeated. This is the only index we can create.

 

Syntax:

create unique index index name on table name (column name);

 

--Requirement: create a unique index in the water table number column of the owner's table

create unique index index_owners on t_owners(watermeter);

 

 

 

5. Composite index

We often need to query some columns, and we can build a composite index, that is, build an index based on more than two columns

Syntax:

create index index name on table name (column name, column name 2,...);

 

For example:

Index the owner table according to the address and house number. The statement is as follows:

create index owners_index_ah on t_owners(addressid,housenumber);

 

 

 

After watching, Congratulations, and know a little bit!!!

The more you know, the more you don't know

~Thank you for reading. Your support is the biggest driving force for my study! Come on, strangers work together and encourage together!!

Tags: Database SQL MySQL

Posted on Tue, 02 Jun 2020 07:40:40 -0700 by thomasanup