Oracle view, index, sequence

What are view "views"
(1) A view is a virtual table
(2) The view is based on existing tables, and the tables on which they are built are called base tables.
(3) The statement that provides the data content to the view is a SELECT statement that can be interpreted as a stored SELECT statement
(4) View provides a different representation of the base table data to the user
(5) The view does not store real data, the real data is stored in the base table
(6) While the programmer is working on a view, the final view is also turned into the operating base table
(7) A base table can have 0 or more views

What happens when a view is used
(1) If you do not want users to see all the data (fields, records), just want to let the user see some of the data, you can use the view at this time
(2) When you need to reduce the writing of SQL query statements, you can use the view, but not improve the efficiency of the query

View areas of application
(1) Banking, telecommunications, metals, securities, etc. inconvenient to let users know all the data in the project

The role of the view
(1) Restricting data access
(2) Simplifying complex queries
(3) Mutual independence of the provided data
(4) The same data can be displayed in different ways

Create a view based on all the columns of the EMP table emp_view_1,create view view name as select query for one or more gee tables
CREATE VIEW Emp_view_1
As
SELECT * from EMP;

By default, a normal user does not have permission to create a view, allowing SYSDBA to assign you creare view permissions

Authorize Scott users to create view rights as Sysdba
Grant CREATE view to Scott;

Revoke Scott user CREATE view rights as a SYSDBA identity
Revoke CREATE view from Scott;

Specifies the column based on the EMP table, creates the view Emp_view_2, which contains the number/name/Payroll/Yearly Income/year revenue (using column aliases in the query)
CREATE VIEW Emp_view_2
As
Select Empno "Number", ename "name", Sal "salary", sal*12 "annual salary", SAL*12+NVL (comm,0) "Annual income"
from EMP;

Specify columns based on an EMP table, create a View emp_view_3 (a,b,c,d,e), including number/name/Payroll/Annual income/year revenue (use column name in view)
Create View Emp_view_3 (a,b,c,d,e)
As
Select Empno "Number", ename "name", Sal "salary", sal*12 "annual salary", SAL*12+NVL (comm,0) "Annual income"
from EMP;

Query Emp_view_3 Create a view structure
Desc Emp_view_3;

Modify Emp_view_3 (id,name,salary,annual,income) view, create or Replace view view name as subquery
Create or Replace view Emp_view_3 (id,name,salary,annual,income)
As
Select Empno "Number", ename "name", Sal "salary", sal*12 "annual salary", SAL*12+NVL (comm,0) "Annual income"
from EMP;

Check the EMP table to find out the minimum wage, maximum wage and average wage of each department.
Select min (sal), Max (SAL), round (avg (SAL), 0), Deptno
From EMP
Group BY Deptno;

Create a view emp_view_4, which contains the minimum wage for each department, the maximum wage, the average wage
Create or Replace view Emp_view_4
As
Select Deptno "department number", Min (sal) "minimum wage", Max (SAL) "maximum wage", round (avg (SAL), 0) "average wage"
From EMP
Group BY Deptno;

Create a view emp_view_5 with employee number, name, salary, department name, salary level
Create or Replace view Emp_view_5
As
Select E.empno "Number", E.ename "name", E.sal "salary", d.dname "department name", S.grade "wage Grade"
From EMP e,dept D,salgrade s
where (E.deptno=d.deptno) and (E.sal between S.losal and S.hisal);

Delete the record of employee number No. 7788 in view Emp_view_1, will the base table be affected by using the delete operation?
Delete from emp_view_1 where empno=7788; the correct wording will affect the base table

Is it OK to modify the emp_view_1 to read-only view "with Read Only" and perform the above delete operation?
Create or Replace view Emp_view_1
As
SELECT * FROM emp
with Read only;
The delete operation is not possible.

Does deleting the "one" record in the view affect the base table?
will affect the base table

Does deleting the entire view affect the table?
Does not affect the base table

Do you want to delete the view and go to the Recycle Bin?
Does not enter the Recycle Bin

Does deleting a base table affect the view?
Will affect the view

Does the view have an impact after you flash back to the base table?
The view will work again.

-------------------------------------------------------------------------------------synonyms

What is the synonym "synonym"?
(1) For some long names of objects (table, view, index, sequence, ...) ) Do the subtraction, replace with aliases

The role of synonyms
(1) Shorten the length of the object name
(2) Easy access to other users ' objects

Creating synonyms for Salgrade tables, create synonym synonyms for table names/views/other objects
Create synonym E for salgrade;
Create synonym ev5 for emp_view_5;

Grant Scott Normal user create synonym permissions as sys
Grant create synonym to Scott;

Revoke create synonym permissions from the Scott normal user as Sys
Revoke create synonym from Scott;

Using synonyms to manipulate salgrade tables
select * from S;

Delete synonyms
drop synonym ev5;

Does deleting a synonym affect the base table?
Does not affect the base table

Does deleting a base table affect synonyms?
Will affect synonyms

-------------------------------------------------------------------------------------sequence

What is the sequence "Sequence"
(1) Similar to the Auto_increment automatic growth mechanism in MySQL, but no auto_increment mechanism in Oracle
(2) is a mechanism provided by Oracle to produce a unique numeric value
(3) The main health value of the table is usually used
(4) Sequence can only be guaranteed to be unique and cannot guarantee continuous
Disclaimer: In Oracle, only rownum is always maintained starting at 1 and continues
(5) Sequence value, can be placed in memory, take the faster

Question: Why does Oracle not use RowNum directly?
Rownum=1 This record can't always be the only one that says Smith this user
But the primary key =1 can always uniquely represent the Smith user

Why use a sequence
(1) Before we set the value for the main health, we need to set the value manually, error-prone
(2) The main health value of each table before, is independent, cannot share

Create sequence emp_empno_seq,create sequence sequence name for the Empno field of the EMP table
Create sequence emp_empno_seq;

Delete sequence Emp_empno_seq,drop sequence sequence name
Drop sequence emp_empno_seq;

Querying the current value of the emp_empno_seq sequence Currval and the next value Nextval, the first time you use a sequence, you must choose: Sequence name. nextval
Select Emp_empno_seq.nextval from dual;
Select Emp_empno_seq.currval from dual;

Using sequences, inserting records into the EMP table, empno fields using sequence values
INSERT into EMP (EMPNO) values (emp_empno_seq.nextval);
INSERT into EMP (EMPNO) values (emp_empno_seq.nextval);
INSERT into EMP (EMPNO) values (emp_empno_seq.nextval);

Modify the increment by property of the emp_empno_seq sequence to 20, and the default start with is the 1,alter sequence sequence name
Alter sequence EMP_EMPNO_SEQ
Increment by 20;

Modify the increment by property of the modified EMP_EMPNO_SEQ sequence to 5
Alter sequence EMP_EMPNO_SEQ
Increment by 5;

Modify the start with property of the emp_empno_seq sequence, OK?
Alter sequence EMP_EMPNO_SEQ
Start with 100;

With a sequence, can you set the value manually for the master Jian?
INSERT into EMP (EMPNO) VALUES (9999);
INSERT into EMP (EMPNO) values (7900);

Does deleting a table affect the sequence?
You can't do an insert operation

Does deleting a sequence affect the table?
The table is really dead, the sequence is dead

In Hibernate, if you are accessing an Oracle database server, what about the <id> tags in the User.hbm.xml mapping file?
<id name= "id" column= "id" >
<generator class= "Increment/identity/uuid/" sequence "/" native ""/>
</id>

-------------------------------------------------------------------------------------Index

What is the index?
(1) is a mechanism for quickly querying the contents of a table, similar to the directory of the Xinhua dictionary
(2) apply to one or more fields in a table, but when stored, independent of the table

Why do you use an index
(1) Accelerate query speed for Oracle servers with pointers
(2) Reduce disk I/O by rowID fast location of data
rowID is the only identity card in Oracle that uniquely identifies different records for each table

Features of rowID
(1) In each table, but not visible on the surface, for example: Desc EMP is invisible
(2) Only in Select, the display writes out the ROWID, the party can see
(3) It binds to each table, the table is dead, the table rowid, two tables rownum can be the same, but ROWID must be unique
(4) rowID is a 18-bit case-and-number hybrid, and the unique table is the location of the record in the DBF file
(5) rowID can participate in =/like comparisons, wrap the value of rowid with ' single quotation marks, and be case-sensitive
(6) rowID is a bridge between the contact form and the DBF file

Features of the index
(1) Once an index is established, the Oracle Management system automatically maintains it, and the Oracle management system decides when to use the index
(2) The user does not specify which index to use in the query statement
(3) The system automatically creates an index on the corresponding column after defining the primary KEY or UNIQUE constraint
(4) The user can also add an index to a specified single field or multiple fields according to their own needs.

When to create an index
(1) Tables are frequently SELECT operations
(2) The table is very large (record super many), the record content distribution is very wide
(3) column names often appear in the WHERE clause or join condition
Note: You can create an index that meets one of the above requirements, and creating an index is an optimization problem, as well as a policy issue

When do I "don't" create an index
(1) The table often insert/update/delete operation
(2) The table is very small (record very little)
(3) Column names are not often used as join conditions or appear in the WHERE clause
Ditto note

Create index EMP_EMPNO_IDX for the Empno single field of the EMP table, called single-column index, create index name on table name (field,...)
CREATE INDEX Emp_empno_idx
On EMP (EMPNO);

Ename,job multiple fields for the EMP table, CREATE index EMP_ENAME_JOB_IDX, multi-column index/Federated Index
CREATE INDEX Emp_ename_job
On EMP (ename,job);
If only the job does not use the index in where
If only ename is used in the where index
We advocate the simultaneous presence of ename and job

Note: After the index is created, only query tables are relevant, and other (Insert/update/delete)-independent, solve the speed problem

Delete Emp_empno_idx and Emp_ename_job_idx indexes, DROP INDEX index name
Drop index EMP_EMPNO_IDX;
Drop index EMP_ENAME_JOB_IDX;

Oracle view, index, sequence