Oracle note (13) view, synonym, Index

Source: Internet
Author: User

Oracle note (13) view, synonym, and index links: Oracle note (1) Oracle introduction and Installation ; Oracle notes (2) SQLPlus command Oracle note (3) Scott's table structure Oracle notes (4) simple query, restricted query, and data sorting Oracle notes (5) Single Row Functions ; Oracle notes (6) Multi-Table query Oracle notes (7) data update, transaction processing, and data pseudo Columns Oracle notes (8) complex queries and summary Oracle notes (9) Table creation and management Oracle note (10) Constraints Oracle notes (11) Comprehensive Table creation, update, and query exercises Oracle note (12) set and Sequence I. View query operations are the most troublesome among all the SQL syntaxes that have been learned before. If programmers waste a lot of effort on writing queries, therefore, a good Database Designer not only designs data tables based on business operations, but also provides several views for users, each VIEW encapsulates a complex SQL statement. The VIEW creation syntax is AS follows: CREATE [OR REPLACE] VIEW name AS subquery; example: 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 you have created a view named myview. Query myview: SELECT * FROM myview. In this case, you can use a simple View query operation to complete the functions of the preceding complex SQL statements. Therefore, the view encapsulates the SQL query operation. Example of CREATE a VIEW containing simple query statements drop view myview; create view myview as select * FROM emp WHERE deptno = 20; however, the above operation is actually a replacement operation for a VIEW, so you can also use another Syntax: create or replace view myview as select * FROM emp WHERE deptno = 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: The view created above with check option has a "WHERE deptno = 20" condition for creation. What if I update this condition in the view now? UPDATE myview SET deptno = 30 WHERE empno = 7369; in this case, a view is updated, but the view itself is not a specific data table, and the UPDATE operation is the condition for creating the view, obviously, this approach is not advisable. To solve this problem, you can add with check option;
Create or replace view myview as select * FROM emp WHERE deptno = 20 with check option; execute the VIEW update operation again WITH the following error prompt: ORA-01402: view with check optidn where clause violations mean that you cannot update the view creation conditions at all.
OPTION 2: with read only, although with check option can ensure that the view creation conditions are not updated, other fields can be updated. UPDATE myview SET sal = 9000 WHERE empno = 7369; similar to the previous problem, the view itself is not a specific real data, but a query statement, so such an UPDATE is not reasonable, when creating a VIEW, we recommend that you set it to a READ-only view: create or replace view myview as select * FROM emp WHERE deptno = 20 with read only. At this time, the update operation is sent again, the following error is prompted directly: ORA-01733: Virtual columns are not allowed here and it must be noted that the above is a simple operation statement view, if the query statement in the current view is a statistical operation, it is impossible to update it. 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 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 are a group of words with similar meanings. They have been used for synonym operations. For example, the following query statement is used: select sysdate from dual; 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, which are called SYNONYM in Oracle. The syntax FOR creating synonyms is as follows: CREATE [PUBLIC] sysnonym synonym name FOR username. table name. Example: Create a synonym named myemp, which points to scott. empCREATE S YNONYM myemp FOR scott. 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 cannot be used by 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; drop synonym myemp; create public synonym myemp FOR scott. emp; CONN system/manager; SELECT * FROM myemp; however, synonyms are just Oracle's own concepts. III. The primary function of index indexing is to improve the operational performance of the database. The following code analyzes a simple index operation problem. For example, you have previously written the following statement: SELECT * FROM emp WHERE sal> 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 records be queried now? 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. You can create an index manually: specify an index in an operation column. In emp. create index emp_sal_ind ON emp (sal) ON the sal field; although the INDEX is created, 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.