Oracle Notes (13) Views, synonyms, indexes

Oracle Notes (13) Views, synonyms, indexes

First, the View

In all the SQL syntax previously studied, the query operation is the most troublesome, if the program developers will be a lot of energy wasted on the query writing, it will certainly affect the work of the Code, so a good database designer, in addition to the business operation of the design of the data table, You also need to provide a number of views for the user, and each view wraps a complex set of SQL statements, with the following syntax for creating the view:

CREATE [OR REPLACE] View name

As sub-query;

Example: Creating a View

CREATE VIEW MyView as

SELECT D.deptno,d.dname,d.loc,count (e.empno) count,avg (e.sal) AVG

From EMP e,dept D

WHERE E.deptno (+) =d.deptno

GROUP by D.deptno,d.dname,d.loc;

Now that a view has been created, the name is MyView, so now query MyView:

SELECT * from MyView;

With a simple view query operation, the functionality of the previous complex SQL statement can be completed, so the view is wrapping the SQL query operation.

Example: Create a view that contains a simple query statement

DROP VIEW MyView;

CREATE VIEW MyView as

SELECT * from emp WHERE deptno=;

However, the above operation is actually a replacement for a view, so you can also use another syntax at this point:

CREATE OR REPLACE VIEW MyView as

SELECT * from emp WHERE deptno=;

At this point, if the view exists and is replaced, a new view is created, and the concept of the view is well understood, but there are two options when creating the view.

• Option One: With CHECK OPTION

The view created above is a "WHERE deptno=20" that creates a condition, so if you update this condition in the view now?

UPDATE myview SET deptno= WHERE empno=7369;

At this point the update is a view, but the view itself is not a specific data table, and now the updated operation is the creation condition of the view, it is obvious that this is undesirable, so in order to solve this problem, you can add with CHECK OPTION;

CREATE OR REPLACE VIEW MyView as

SELECT * from emp WHERE deptno=

With CHECK OPTION;

The update operation for the view is performed again at this time with the following error prompt:

ORA-01402: View with CHECK OPTIDN WHERE clause violation

means that it is not possible to update the view creation condition at all.

• option two: With READ only

While using with CHECK option ensures that the creation criteria for the view are not updated, the other fields allow updates.

UPDATE myview SET sal=9000 WHERE empno=7369;

As with the previous question, the view itself is not specific to the actual data, but some query statements, so this update is not reasonable, then when creating the view, it is recommended to set it as read-only view:

CREATE OR REPLACE VIEW MyView as

SELECT * from emp WHERE deptno=

with READ only;

At this point, the updated operation is issued again, and the following error is directly indicated:

ORA-01733: Virtual columns are not allowed here

It is also important to note that the above gives a simple view of the operation statement, if the query statement in the view is a statistical operation, it is impossible to update at all.

CREATE OR REPLACE VIEW MyView as

SELECT D.deptno,d.dname,d.loc,count (e.empno) count,avg (e.sal) AVG

From EMP e,dept D

WHERE E.deptno (+) =d.deptno

GROUP by D.deptno,d.dname,d.loc;

The information is now statistical, and it is impossible to update it at all.

In a project, the number of views can be more than the number of tables because there are many query statements.

Second, synonyms

Synonyms are a group of words that are similar in meaning, and are used before the operation of synonyms, for example, there is now a query statement like this:

SELECT sysdate from dual;

Before said "dual" is a virtual table, but the virtual table must also have its users, after the query can be found that this table belongs to the SYS user, but this time there is a problem, explained before, different users want to access other users of the table, you need to write "user. Table name", So why is it that when Scott users visit the dual, instead of using "sys.dual", this is actually synonymous application, Dual represents a synonym for sys.dual, and synonyms are called synonym in Oracle, and the synonyms are created in the following syntax:

CREATE [public] sysnonym the name of the synonym for the user name. Table name;

Example: create a synonym for myemp, this synonym points to scott.emp

CREATE synonym myemp for scott.emp;

After the creation is successful, the name of the synonym for the myemp can be used later in the SYS user, but this synonym is only suitable for the SYS user and cannot be used by other users because it is not used when it is created, and if it is not used, it means that it is not created as a public synonym.

Example: creating a public synonym

CONN Sys/change_on_install as SYSDBA;
DROP synonym Myemp;

CREATE public synonym myemp for scott.emp;

CONN System/manager;

SELECT * from Myemp;

But synonyms are just Oracle's own concept, and that's OK.

Third, index

The primary function of an index is to improve the operational performance of the database.

The following code analyzes the problem of one of the simplest index operations;

For example, the following action statement was previously written:

SELECT * from emp WHERE sal>;

At this point, because there is no index on the SAL, so its query process is done in line-by-row judgment, this operation with the increase in the amount of data, the performance will appear more and more problems, but if the data are arranged?

For example, it is now working in memory to form such a data structure;

If you now assume that all the data are arranged in the tree structure above, the same query will now query all records? Only parts are queried.

There are two ways to create an index among Oracle:

• PRIMARY KEY constraint: automatically creates an index if a primary KEY constraint exists on a column in a table;
• Create manually: Specify an index on an action column;

Example: Creating an index on the Emp.sal field

CREATE INDEX emp_sal_ind on EMP (SAL);

Although the index creation is complete, it is virtually impossible to observe the features.

However, this index has one of the biggest problems, namely: if you want to achieve performance improvement, you must always maintain the above tree, then if the tree is now the data need to be changed frequently, the performance of the code will certainly be reduced.

So the general index is used only in tables that are not frequently modified, and if the data is frequently modified on a table and the index is used, the performance is definitely degraded, so the performance range is always relative.

The index above is just one of the more than 10 indexes in Oracle, and the simplest one, called the B-Tree index, the bitmap index, the reverse index, the function index, and so on.

Oracle Notes (13) Views, synonyms, indexes