Basic Oracle Database Operations (2)-views, sequences, indexes, synonyms, and basic oracle operations

Basic Oracle Database Operations (2)-views, sequences, indexes, synonyms, and basic oracle operations

I. synonyms of Views

1. View: it is actually an encapsulation of the query result set. The view itself does not store any data, and all data is stored in the original table;

Logically, you can regard a view as a table.

2. functions:

• • Encapsulate query statements to simplify complex query requirements
  • Mask table details

3. Syntax:

Create [or replace] view name as query statement [with read only]

4. Views and synonyms:

• • Blocked query process steps
  • Blocking real table names makes Code cracking more difficult

5. Example:

select * from emp;

-- Encapsulated into a view: Obtain the employee information of Department 10, create or replace view view_test1 as select * from emp where deptno = 10; select * from view_test1;

-- Simplify the query statement create view view_test2 as select sum (cc) total, SUM (CASE yy WHEN '000000' THEN cc END) "1980 ", SUM (CASE yy WHEN '000000' THEN cc END) "1981", SUM (CASE yy WHEN '000000' THEN cc END) "1981 ", SUM (CASE yy WHEN '100' THEN cc END) "1987" FROM (SELECT TO_CHAR (HIREDATE, 'yyyy') YY, COUNT (*) cc from emp group by TO_CHAR (HIREDATE, 'yyyy') TT; select * from view_test2;

-- Block the details in the create or replace view view_test3 as select ename, job, mgr, hiredate from emp; -- update view_test3 set ename = 'Smith 'where ename = 'smi % th' through the view ';

-- Create read-only view create view view_test4 as select ename, job, mgr, hiredate from emp with read only; -- error ORA-01733: virtual column not allowed hereupdate view_test4 set ename = 'smith2 'where ename = 'Smith ';

-- Synonym: equivalent to an alias create synonym yuangong for view_test3; select * from yuangong;

Ii. Sequence

Sequence: 1, 2, 3, 4, 5, 6, 7 ....

Purpose: Simulate numbers that are automatically increased by auto_increment in mysql.

1. Syntax:

Create sequence name start with the number of increment by the number of minvalues increments each time | nominvaluemaxvalue | nomaxvaluecycle | nocyclecache cache number of 3, 2, 3, 5, 6 -- currval: the current value of the sequence, nextval must be called once before it can be used normally -- nextval: The next value in the sequence -- Note: the value in the sequence, once acquired, regardless of rollback/exception, the sequence is always ascending.

2. Example

--, Create sequence seq_test01start with 1 minvalue 1 maxvalue 6 cyclecache 3; -- the select seq_test01.currval from dual must be executed first; select seq_test01.nextval from dual; -- the development process is usually written as follows: create sequence seq_test02; -- create table louceng (lou number primary key ); -- execute two insert into louceng values (seq_test02.nextval); -- 1 2 select seq_test02.currval from dual; select * from louceng;

Iii. Index

3.1 index Overview

Index: an index is a data structure that has been sorted to help the database quickly find data. It is mainly used to help the database quickly find data, which is equivalent to a directory of a book.

Syntax:

Create index name on table name (column name 1, column name 2 ....);

Primary key constraint: comes with a unique index

Unique constraint: built-in unique index

3.2 index function example

1 -- create test data 5 million 2 -- create a table 3 create table wbw (4 c1 number primary key, 5 c2 varchar2 (20), 6 c3 varchar2 (20) 7 ); 8 -- insert 5 million records 9 create sequence seq_wbw; 10 11 -- cycle (283 s) 12 declare13 14 begin 15 for I in 1 .. 5000000 loop16 insert into wbw values (seq_wb1_nextval, 'c2: '| I, 'c3:' | I); 17 end loop; 18 commit; 19 end; 20 21 -- (2.37 s) 22 select * from wbw where c2 = 'c2: 4000000 'without creating an index '; 23 24 -- 0.00s25 select * from wbw where c1 = 4000000; 26 27 -- create an index (35.77 s) 28 create index wbw_c2 on wbw (c2 ); 29 -- 0.15 s 30 select * from wbw where c2 = 'c2: 8080'; 31 -- 2.38s32 select * from wbw where c3 = 'c3: 4000000 ';

3.3 index Extension

3.3.1 overview of INDEX OPTIMIZATION

SQL statement execution plan (when SQL statement analysis is performed): the SQL statement is sent to the database. What steps and resources are required for database analysis to execute this statement?

SQL optimization steps:

1. Find the statement to be optimized

2. Analyze SQL statements using execution plans

3. Determine the optimization scheme

Index principle: index is a sort of Data Structure BTree that helps the database quickly find data in order: B tree, Balance, balanced multi-path Search Tree

Advantages and disadvantages of indexes:

Benefit: Improves query efficiency

Disadvantage: Resource occupation affects the efficiency of adding, deleting, and modifying Resources in the reverse direction.

When to create an index?

1. When the data volume is large

2. Which columns are frequently used as query conditions?

3.3.2 example:

1 -- if no multi-column index is created, 0.50 s 2 select * from wbw where c2 = 'c2: 4000001 'and c3 = 'c3: 4000001 '; 3 4 -- create index (41.43 s) 5 create index wbw_c23 on wbw (c2, c3 ); 6 7 8 -- when multiple column indexes are created, 0.25 s 9 select * from wbw where c2 = 'c2: 4000001 'and c3 = 'c3: 4000001 '; 10 -- 1.80s11 select * from wbw where c3 = 'c3: 4000001 ';

3.3.3 execution plan diagram