Oracle learning - view, sequence, synonym, index

1. View

1.1 what is a view

  • View is a mapping of virtual table and entity table.

  • 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 project process, programmers mainly focus on the performance of coding and business analysis. For some complex SQL statements, the designer will package them into a view in advance for the programmer to call

1.2 basic operation of view

1.2.1 creation of simplified version

 create view view name as query statement;

1.2.2 deletion

 drop view view name;

1.2.3. Specific operation: CURD

-- 0.Prepare data and query the owner table
select * from t_owners;
-- 1 Create view
create view view_owners 
as select * from t_owners;

-- 2 Delete view
drop view view_owners;

-- 3 query -- Same syntax as query table
select * from view_owners;

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

update view_owners set name = 'RT Mart' where id = 11;

delete from view_owners where id = 11;

1.3 view with conditions (detection)

  • Add constraint checking to views
    • 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
-- 0 preparation
select * from t_owners where ownertypeid = 1

-- 1 Create view
create or replace view view_owner1 
as select * from t_owners where ownertypeid = 1
with check option

-- 2 query
select * from view_owner1;

-- 3 Add data
-- Failed to execute, constraint violation provided
insert into view_owner1 values(11,'Meilianmei supermarket 666',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 );
-- Execution successful
insert into view_owner1 values(11,'Meilianmei supermarket 666',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),1 );
delete from t_owners where id = 11;

1.4. Forced creation (understanding)

  • force forces the view to be created, regardless of whether the query statement is incorrect or not
--- Force creation
-- 0 Preparatory work, query table data, table does not exist
select * from temp;

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

1.5 read only view

  • with read only, the view must be read-only.
-- Read-only views 
-- 0.Prepare data and query the 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(11,'Meilianmei supermarket 666',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),1 );

1.6. Associated query

1.6.1 term explanation: key retention table, non key retention table

  • Feature: in the view generated by the associated query, only the data in the key retention table can be added / modified.

1.6.2 SQL writing

-- Associated query (complex view)
-- 0 get ready sql 
select Owner No, Name of owner, Type name from t_owners ow
inner join t_ownertype ot on ow.ownertypeid =
-- 1 Create view
create or replace view view_owners4
as select Owner No, Name of owner, Type name from t_owners ow
inner join t_ownertype ot on ow.ownertypeid =
-- 2 Query data
select * from view_owners4
-- 3 Update data
-- 3.1 Key retention table
update view_owners4 set Name of owner = 'Wang Xiaoqiang' where Owner No = 2;
-- 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;

1.7 summary

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 

2. Sequence

2.1 what is sequence

  • Series: database objects used to produce unique numbers
    • Equivalent to MySQL auto grow column (auto_increment)
  • Sequence purpose: maintain automatic growth of primary key in the table.

2.2 grammar

  • Simplified version:
create sequence serial number;
  • Full version:
create sequence serial number
 start with
 increment by step
 maxvalue Max
 minvalue minimum

2.3. Create

-- Simplified version
create sequence seq_stuno;

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

2.4 use

  • Query to get sequence value
-- query , nextval Get next sequence
select seq_stuno2.nextval from dual;
  • Add auto maintain primary key
-- Table primary key auto maintenance
create table t_demo(
  id int primary key,
  name varchar2(50)
insert into t_demo(id,name) values(seq_stuno.nextval , 'Zhang San');
insert into t_demo(id,name) values(seq_stuno.nextval , 'Li Si');
insert into t_demo(id,name) values(seq_stuno.nextval , 'Wang Wu');

3. Synonyms

3.1 what is synonym

  • Synonyms are aliases that can be given to 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.

3.2 classification of synonyms

  • Private synonym: only user can access
  • Public synonyms: accessible to all users of the database

3.3 creation and deletion

  • establish

    • private synonym

      create synonym name for table name view name
    • Public synonyms

      create public synonym name for table name view name
  • delete

    • private synonym

      drop synonym name
    • Public synonyms

      drop public synonym name

3.4 demonstration

  • When you use synonym query, you need to use two users for testing, and common synonyms can share tables between two users.
--- synonym
-- Create private synonyms
create synonym owners for t_owners;
-- After creation, you can query the data
select * from owners;

-- Create common synonyms
create public synonym owners2 for t_owners;
select * from owners2;

--- delete
drop synonym owners;
drop public synonym owners2

4. Index

4.1 what is index

  • 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: to improve data access performance.
  • Index requires physical storage. Use space for time.

4.2 index classification

  • Normal index: declare a column with the keyword index
  • Unique index: columns declared with the keyword unique
  • Compound index: use the keyword index to declare multiple columns

4.3 general index

4.3.1 grammar

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

4.3.2 data preparation

-- 1 Prepare data
-- 1.1 Create table
drop table t_indextest;
create table t_indextest(
  id number,
  name varchar2(30)
-- 1.2 Import 1 million pieces of data in batch
  for i in 1..1000000
    insert into t_indextest(id,name) values(i, 'AA' || i);
  end loop;

4.3.3 test performance

-- 2 test
-- 2.1 query(id,name)  -- The two results are basically the same
select * from t_indextest where id = 765432;
select * from t_indextest where name = 'AA765432';
-- 2.2 Add index
create index t_indextest_name on t_indextest(name);
-- 2.3 Query again   -- Index fast
select * from t_indextest where id = 765432;
select * from t_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.4 unique index (understanding)

  • If we need to create an index on a column of a table, the value of this column will not be repeated. At this point we can create a unique index.
create unique index index name on table name (column name);

4.5 composite index (understanding)

  • We often need to query some columns, and we can build a composite index, that is, an index based on more than two columns
create index index name on table name (column name, column name 2,...);

Tags: Database SQL MySQL

Posted on Mon, 01 Jun 2020 11:55:47 -0400 by ldtiw