Views Advanced Features

1
Views - Advanced Features

• Carl Dudley

2
Merging Queries and Views

• View definition
• CREATE VIEW emp_view AS
• SELECT empno
• ,ename
• ,sal
• ,loc
• FROM emp
• ,dept
• WHERE emp.deptno dept.deptno
• AND dept.deptno 10

Parent query SELECT ename FROM emp_view WHERE
empno 9876
Final query SELECT ename FROM emp ,dept
WHERE emp.deptno dept.deptno AND dept.deptno
10 AND emp.empno 9876 In some cases, view
definitions cannot be merged with the user-issued
query. This may disable the use of indexes
3
Updatable Views

• Columns within simple views on single tables
  are updatable
• Views containing any of the following clauses are
  non-updatable
• DISTINCT
• group functions AVG, COUNT, MAX, MIN, STDDEV,
  SUM, or VARIANCE
• Set operations UNION, UNION ALL, INTERSECT,
  MINUS
• GROUP BY or HAVING clauses
• Views on more than one table (join-views) have
  restrictions for updates
• Columns which are updatable must be in
  key-preserved tables
• Key-preserved tables have a known 11 mapping of
  rows in the view to rows in the underlying base
  table

4
Updatable Views (continued)

• CREATE OR REPLACE VIEW deptemp AS
• SELECT empno
• ,ename
• ,job
• ,mgr
• ,hiredate
• ,sal
• ,comm
• ,dept.deptno
• ,dname
• ,loc
• FROM emp
• ,dept
• WHERE dept.deptno emp.deptno
• If a primary key is defined on the deptno column
  in the dept table, the emp table becomes a
  key-preserved table
• columns derived from emp become key-preserved

5
Updatable Views (continued)

• Query user_updatable_columns to obtain updatable
  view information
• SELECT
• FROM user_updatable_columns
• WHERE table_name DEPTEMP
• OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
• --------- ---------- ----------- --- --- ---
• SCOTT DEPTEMP EMPNO YES YES YES
• SCOTT DEPTEMP ENAME YES YES YES
• SCOTT DEPTEMP JOB YES YES YES
• SCOTT DEPTEMP MGR YES YES YES
• SCOTT DEPTEMP HIREDATE YES YES YES
• SCOTT DEPTEMP SAL YES YES YES
• SCOTT DEPTEMP COMM YES YES YES
• SCOTT DEPTEMP DEPTNO NO NO NO
• SCOTT DEPTEMP DNAME NO NO NO
• SCOTT DEPTEMP LOC NO NO NO

6
Updatable Views (continued)
SQLgt SELECT FROM deptemp EMPNO ENAME JOB
MGR HIREDATE SAL COMM DEPTNO DNAME
LOC ----- ------- --------- ----- --------- ----
---- ------ ----------- -------- 7934 MILLER
CLERK 7782 23-JAN-82 1300 10
ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839
09-JUN-81 2450 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000
10 ACCOUNTING NEW YORK 7369 SMITH CLERK
7902 17-DEC-80 800 20 RESEARCH
DALLAS 7566 JONES MANAGER 7839 02-APR-81
2975 20 RESEARCH DALLAS 7788 SCOTT
ANALYST 7566 19-APR-87 3000 20
RESEARCH DALLAS 7876 ADAMS CLERK 7788
23-MAY-87 1100 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000
20 RESEARCH DALLAS 7499 ALLEN SALESMAN
7698 20-FEB-81 1600 300 30 SALES
CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81
1250 500 30 SALES CHICAGO 7654 MARTIN
SALESMAN 7698 28-SEP-81 1250 1400 30 SALES
CHICAGO 7698 BLAKE MANAGER 7839
01-MAY-81 2850 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 SALES CHICAGO 7900 JAMES CLERK
7698 03-DEC-81 950 30 SALES
CHICAGO

• Primary key on dept(deptno)
• deptno, dname and loc are non-updatable
• Rows can be deleted but not inserted

7
Updatable Views (continued)
DELETE FROM deptemp WHERE deptno 10 3 rows
deleted. UPDATE deptemp SET job
'CLERK WHERE ename 'FORD' 1 row
updated. UPDATE deptemp SET dname
'ACCOUNTING WHERE ename 'FORD'
ERROR at line 1 ORA-01779 cannot modify a
column which maps to a non
key-preserved table

• Primary key is necessary on the non key-preserved
  table
• Otherwise no updates or deletes are possible

8
Updatable Views (continued)

• Views based on outer joins are entirely non
  updatable
• CREATE OR REPLACE VIEW deptemp AS
• SELECT empno
• ,ename
• ,job
• ,mgr
• ,hiredate
• ,sal
• ,comm
• ,dept.deptno
• ,dname
• ,loc
• FROM emp
• ,dept
• WHERE dept.deptno emp.deptno()
• The following delete operation will cause an
  error
• DELETE FROM deptemp
• WHERE deptno 10

9
Updatable Views (continued)

• INSTEAD OF triggers can be used to provide
  updatability
• Can not be used on base tables
• CREATE OR REPLACE TRIGGER deptemp_nocons
• INSTEAD OF DELETE ON deptemp
• FOR EACH ROW
• BEGIN
• DELETE FROM dept WHERE deptno OLD.deptno
• DELETE FROM emp WHERE deptno OLD.deptno
• END

10
Constraints

• The deptemp_nocons trigger will not work if the
  following referential constraint is applied
• ALTER TABLE emp
• ADD CONSTRAINT fk_emp_dept
• FOREIGN KEY (deptno) REFERENCES dept(deptno)
• Could rewrite the trigger as follows
• deletes from the tables in correct order for the
  constraint
• CREATE OR REPLACE TRIGGER deptemp_nocons
• INSTEAD OF DELETE ON deptemp
• FOR EACH ROW
• BEGIN
• DELETE FROM emp WHERE deptno OLD.deptno
• DELETE FROM dept WHERE deptno OLD.deptno
• END

11
Deferred Constraints

• An alternative to rewriting the trigger
• the constraint could be set to DEFERRED
• SET CONSTRAINT fk_emp_dept DEFERRED
• Checks not made until the end of the transaction
• allows transactions which would internally
  (temporarily) violate constraints
• Constraints must be built DEFERRABLE
• CREATE TABLE ...
• CONSTRAINT ...
• INITIALLY IMMEDIATE DEFERRABLE

12
Inline Views

• Sometimes referred to as
• view on the fly
• Subqueries in the FROM clause SELECT
  emp.ename
• ,emp.sal
• ,avgdeptview.avgdeptsals
• ,avgallsalsview.avgallsals
• FROM emp
• ,(SELECT AVG(v1.sal) AS avgallsals
• FROM emp v1) avgallsalsview
• ,(SELECT v2.deptno
• ,AVG(v2.sal) AS avgdeptsals
• FROM emp v2
• GROUP BY v2.deptno) avgdeptview
• WHERE avgdeptview.deptno emp.deptno
• GROUP BY emp.ename
• ,emp.sal
• ,avgdeptview.avgdeptsals
• ,avgallsalsview.avgallsals

13
Inline Views (continued)

• SELECT emp.ename
• ,emp.sal
• ,avgdeptview.avgdeptsals
• ,avgallsalsview.avgallsals
• FROM emp
• ,
• (SELECT AVG(v1.sal) AS avgallsals
• FROM emp v1)
  avgallsalsview
• ,
• (SELECT v2.deptno
• ,avg(v2.sal) AS avgdeptsals
• FROM emp v2
• GROUP BY v2.deptno)
  avgdeptview
• WHERE avgdeptview.deptno emp.deptno
• GROUP BY emp.ename
• ,emp.sal
• ,avgdeptview.avgdeptsals

14
Inline Views (continued)
ENAME SAL AVGDEPT AVGALLSALS --------
-- --------- --------- ---------- ADAMS
1100 2175 2073.2143 ALLEN 1600
1566.6667 2073.2143 BLAKE 2850
1566.6667 2073.2143 CLARK 2450
2916.6667 2073.2143 FORD 3000
2175 2073.2143 JAMES 950 1566.6667
2073.2143 JONES 2975 2175
2073.2143 KING 5000 2916.6667
2073.2143 MARTIN 1250 1566.6667
2073.2143 MILLER 1300 2916.6667
2073.2143 SCOTT 3000 2175
2073.2143 SMITH 800 2175
2073.2143 TURNER 1500 1566.6667
2073.2143 WARD 1250 1566.6667
2073.2143
15
Inline Views for Database Administration

• SELECT username
• ,COUNT() User_processes
• ,All_processes
• FROM vprocess
• ,(SELECT COUNT() AS All_processes
• FROM vprocess) allprocs
• GROUP BY username
• ,All_processes
• USERNAME USER_PROCESSES ALL_PROCESSES
• --------------- -------------- -------------
• GREEN 8 103
• FRANK 1 103
• COX 2 103
• BROWN 1 103
• JONES 5 103

16
Distributed Query Processing - Views

• Views can be used to fix the processing of
  distributed queries at specified sites
• Suppose the following query needs to be
  performed SELECT empno
• ,ename
• ,sal
• ,dname
• ,loc
• FROM emp
• ,dept
• WHERE dept.deptno_at_B emp.deptno_at_A

17
Distributed Query Processing - Views (continued)

• Create a view held (stored) at site B
• CREATE OR REPLACE VIEW deptemp AS
• SELECT empno
• ,ename
• ,sal
• ,dname
• ,loc
• FROM emp
• ,dept
• WHERE dept.deptno_at_A emp.deptno_at_B
• The following query, designed to retrieve
  employee and department details, is issued at
  site A
• SELECT FROM deptemp
• WHERE deptno 10
• Using the view will force
• local dept data at site A to be sent to site B
• the join performed at site B (the site holding
  the view)
• the results sent back to the local site A