[ Pobierz całość w formacie PDF ]
." to hide data complexityFor example, a single view might be defined with a join, which is acollection of related columns or rows in multiple tables.However, theview hides the fact that this information actually originates from severaltables." to simplify commands for the userFor example, views allow users to select information from multipletables without actually knowing how to perform a join." to present the data in a different perspective from that of the base tableFor example, the columns of a view can be renamed without affectingthe tables on which the view is based." to isolate applications from changes in definitions of base tablesFor example, if a view s defining query references three columns of afour column table and a fifth column is added to the table, the view sSchema Objects 7-11 definition is not affected and all applications using the view are notaffected." to express a query that cannot be expressed without using a viewFor example, a view can be defined that joins a GROUP BY view with atable, or a view can be defined that joins a UNION view with a table.Forinformation about GROUP BY or UNION, see the Oracle8 Server SQLReference." to save complex queriesFor example, a query could perform extensive calculations with tableinformation.By saving this query as a view, the calculations can beperformed each time the view is queried." to achieve improvements in availability and performanceFor example, a database administrator can divide a large table intosmaller tables (partitions) for many reasons, including partition levelload, purge, backup, restore, reorganization, and index building.Oncepartition views are defined, users can query partitions, rather than verylarge tables.This ability to prune unneeded partitions from queriesincreases performance and availability.The Mechanics of ViewsOracle stores a view s definition in the data dictionary as the text of the querythat defines the view.When you reference a view in a SQL statement, Oraclemerges the statement that references the view with the query that defines theview and then parses the merged statement in a shared SQL area and executesit.Oracle parses a statement that references a view in a new shared SQL areaonly if no existing shared SQL area contains an identical statement.Therefore,you obtain the benefit of reduced memory usage associated with shared SQLwhen you use views.NLS ParametersIn evaluating views containing string literals or SQL functions that have NLSparameters as arguments (such as TO_CHAR, TO_DATE, and TO_NUMBER),Oracle takes default values for these parameters from the NLS parameters forthe session.You can override these default values by specifying NLSparameters explicitly in the view definition.7-12 Oracle8 Server Concepts Using IndexesOracle determines whether to use indexes for a query against a view bytransforming the original query when merging it with the view s definingquery.Consider the viewCREATE VIEW emp_view ASSELECT empno, ename, sal, locFROM emp, deptWHERE emp.deptno = dept.deptno AND dept.deptno = 10;Now consider the following user-issued query:SELECT enameFROM emp_viewWHERE empno = 9876;The final query constructed by Oracle isSELECT enameFROM emp, deptWHERE emp.deptno = dept.deptno ANDdept.deptno = 10 ANDemp.empno = 9876;In all possible cases, Oracle merges a query against a view with the view sdefining query (and those of the underlying views).Oracle optimizes themerged query as if you issued the query without referencing the views.Therefore, Oracle can use indexes on any referenced base table columns,whether the columns are referenced in the view definition or the user queryagainst the view.In some cases, Oracle cannot merge the view definition with the user-issuedquery.In such cases, Oracle may not use all indexes on referenced columns.Dependencies and ViewsBecause a view is defined by a query that references other objects (tables,snapshots, or other views), a view is dependent on the referenced objects.Oracle automatically handles the dependencies for views.For example, if youdrop a base table of a view and then re-create it, Oracle determines whetherthe new base table is acceptable to the existing definition of the view.SeeChapter 18, Oracle Dependency Management , for a complete discussion ofdependencies in a database.Schema Objects 7-13 Updatable Join ViewsA join view is defined as a view with more than one table or view in its FROMclause and which does not use any of these clauses: DISTINCT,AGGREGATION, GROUP BY, START WITH, CONNECT BY, ROWNUM,and set operations (UNION ALL, INTERSECT, and so on).An updatable join view is a join view, which involves two or more base tablesor views, where UPDATE, INSERT, and DELETE operations are permitted [ Pobierz całość w formacie PDF ]