Oracle note (13) view, synonym, Index

Oracle note (13) view, synonym, and index links: Oracle note (1) Oracle introduction and Installation http://www.bkjia.com/database/201209/154049.html ; Oracle notes (2) SQLPlus command http://www.bkjia.com/database/201209/154051.html Oracle note (3) Scott's table structure http://www.bkjia.com/database/201209/154052.html Oracle notes (4) simple query, restricted query, and data sorting http://www.bkjia.com/database/201209/154054.html Oracle notes (5) Single Row Functions http://www.bkjia.com/database/201209/154056.html ; Oracle notes (6) Multi-Table query http://www.bkjia.com/database/201209/154060.html Oracle notes (7) data update, transaction processing, and data pseudo Columns http://www.bkjia.com/database/201209/154062.html Oracle notes (8) complex queries and summary http://www.bkjia.com/database/201209/154063.html Oracle notes (9) Table creation and management http://www.bkjia.com/database/201209/154316.html Oracle note (10) Constraints http://www.bkjia.com/database/201209/154317.html Oracle notes (11) Comprehensive Table creation, update, and query exercises http://www.bkjia.com/database/201209/154621.html Oracle note (12) set and Sequence http://www.bkjia.com/database/201209/154623.html 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; www.2cto.com 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 www.2cto.com: 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; www.2cto.com 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: www.2cto.com 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 www.2cto.com 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. www.2cto.com: 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.