Oracle note (13) view, synonym, Index

I. View

Among all the SQL syntaxes I have learned before, query operations are the most troublesome.ProgramIf developers waste a lot of effort on writing queries, it will certainly affectCodeTherefore, a good Database Designer not only designs data tables according to business operations, but also needs to provide users with several views, each view encapsulates a complex SQL statement. The view creation syntax is as follows:

 
Create [Or replace] ViewView nameAsSubquery;
 

Example:Create a view

Create ViewMyviewAsSelectD. deptno, D. dname, D. Loc,Count(E. empno)Count,AVG(E. Sal)AVGFromEmp e, DEPT dWhereE. deptno (+)=D. deptnoGroup ByD. deptno, D. dname, D. LOC;
 

A view named myview has been created. Therefore, you can query myview now:

 
Select * FromMyview;
 

In this case, you can use a simple View query operation to complete the previous complex SQL statement functions. Therefore, the view encapsulates the SQL query operations.

Example:Create a view containing simple query statements

 
Drop ViewMyview;Create ViewMyviewAsSelect * FromEMPWhereDeptno=20;
 

However, the above operation actually belongs to a view replacement operation, so you can also use another syntax at this time:

Create Or Replace ViewMyviewAsSelect * FromEMPWhereDeptno=20;
 

This indicates that if the view exists, it is replaced. If the view does not exist, a new view is created. Although the view concept is easy to understand, there are two options when creating the view.

• Option 1:With check Option

The view created above has a "where deptno = 20" created condition. What if I update this condition in the view now?

UpdateMyviewSetDeptno=30 WhereEmpno=7369;
 

In this case, a view is updated, but the view itself is not a specific data table, and the update operation is a condition for creating the view. Obviously, this approach is not desirable, therefore, to solve this problem, you can add with check option;

Create Or Replace ViewMyviewAsSelect * FromEMPWhereDeptno=20With Check Option;
 

When you update the view again, the following error message is displayed:

 
ORA-01402: view with check optidn WHERE clause violations
 

This means that you cannot update the conditions for creating a view.

• Option 2:With read only

Using with check option ensures that the view creation conditions are not updated, but other fields are allowed to be updated.

UpdateMyviewSetSal=9000 WhereEmpno=7369;
 

As with the previous issue, the view itself is not a specific real data, but a query statement, so such an update is not reasonable. We recommend that you set it as a read-only view when creating the View:

Create Or Replace ViewMyviewAsSelect * FromEMPWhereDeptno=20With Read Only;
 

In this case, the following error is prompted:

 
ORA-01733: Virtual columns are not allowed here
 

Note that the preceding is a simple operation statement view. If the query statements in the view are statistical operations, they cannot be updated at all.

Create Or Replace ViewMyviewAsSelectD. deptno, D. dname, D. Loc,Count(E. empno)Count,AVG(E. Sal)AVGFromEmp e, DEPT dWhereE. deptno (+)=D. deptnoGroup ByD. deptno, D. dname, D. LOC;
 

The current information is statistical and cannot be updated at all.

In a project, the number of views may exceed the number of tables, because there are many query statements.

2. Synonyms

Synonyms are a group of words with similar meanings. They have been used for synonym operations. For example, the following query statement is used:

 
SelectSysdateFromDual;
 

Previously, "dual" is a virtual table, but the virtual table must also have its users. After query, we can find that this table belongs to SYS users, however, there is a problem at this time. As explained previously, if different users want to access tables of other users, they need to write "user. table Name ", why does Scott directly use dual instead of" sys. dual ", this is actually a synonym application. Dual represents sys. dual synonyms. synonyms are called synonym in Oracle. The syntax for creating synonyms is as follows:

 
Create [Public]Name of the sysnonym SynonymForUsername. Table Name;
 

Example:The following is a synonym for myemp, which points to Scott. EMP.

CreateSynonym myempForScott. EMP;
 

After the creation is successful, the name of myemp can be used in SYS users. However, this synonym is only applicable to SYS users and is unavailable to other users, because public is not used during creation. If public is not used, it indicates that the public synonym is not created.

Example:Create a public Synonym

 
Conn sys/change_on_install as sysdba;
DropSynonym myemp;Create PublicSynonym myempForScott. EMP;
 

 
Conn System/Manager;Select * FromMyemp;
 

However, synonyms are just Oracle's own concepts. You just need to know them.

Iii. Index

The index is mainly used to improve the operational performance of the database.

The following code analyzes the simplest index operation problem;

For example, you have previously written the following Operation statements:

 
Select * FromEMPWhereSal>1500;
 

At this time, because no index is set on Sal, its query process is completed by row-by-row judgment. As the data volume increases, the performance will become more and more problems, but what if we want to arrange the data?

For example, a data structure like this will be formed in the memory;

If all the data is arranged in the preceding tree structure, will all the records be queried in the same query? Only the part is queried.

There are two ways to create an index in Oracle:

• Primary key constraint: If a column in a table has a primary key constraint, an index is automatically created;
• Manual creation: specify an index in an operation column;

Example:Create an index on the EMP. Sal Field

Create IndexEmp_sal_indOnEMP (SAL );
 

Although the index creation is complete, it is basically impossible to observe the characteristics.

However, this index has the biggest problem: If you want to improve the performance, you must always maintain the preceding tree. If the data on this tree needs to be modified frequently, the performance of the Code is certainly reduced.

Therefore, the general index is only used in tables that do not frequently modify data. If a table frequently modifies data and uses an index, the performance will be seriously reduced, therefore, performance is always relative.

The above indexes are only one of the more than a dozen Oracle indexes and the simplest one. They are called B-tree indexes, bitmap indexes, reverse indexes, and function indexes.