Lesson 11i
DML operation on Views & Join Views, TOP-N Analysis
Restrictions to perform DML operations on views.
Restrictions on DML(Insert, update, delete, select) operations for views use the following criteria
in the order listed:
1. If a view is defined by a query that contains SET (UNION,UNION ALL, INTERSECT, or MINUS)
or DISTINCT operators, a GROUP BY clause, or a group function(AVG,COUNT,MAX,MIN,etc) then
rows cannot be inserted into, updated in, or deleted from the base tables using the view.
2. If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or updated
in, the base table (using the view), if the view cannot select the row from the base table.
3. If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then a
row cannot be inserted into the base table using the view.
4. If the view was created by using an expression, such as DECODE (deptno, 10, "SALES", ...),
then rows cannot be inserted into or updated in the base table using the view.
EXAMPLE
Creating view WITH CHECK OPTION [constraints consName] and INSERTING values.
CREATE VIEW sales_staff AS
SELECT empno, ename, deptno
FROM emp
WHERE deptno = 10
WITH CHECK OPTION CONSTRAINT sales_staff_cnst;
The query that defines the sales_staff view references only rows in department 10.
Furthermore, the CHECK OPTION creates the view with the constraint (named
sales_staff_cnst) that INSERT and UPDATE statements issued against the view cannot result
in rows that the view cannot select. For example, the following INSERT statement
successfully inserts a row into the emp table by means of the sales_staff view, which
contains all rows with department number 10:
INSERT INTO sales_staff VALUES (7584, 'OSTER', 10);
However, the following INSERT statement returns an error because it attempts to insert a
row for department number 30, which cannot be selected using the sales_staff view:
INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);
1. USING JOIN VIEWS
Key-Preserved Tables
The concept of a key-preserved table is fundamental to understanding the restrictions on
modifying join views. A table is key preserved if every key of the table can also be a key of the
result of the join
The key-preserving property of a table does not depend on the actual data in the table. It is,
rather, a property of its schema. For example, if in the emp table there was at most one
Lesson 11i
DML operation on Views & Join Views, TOP-N Analysis
employee in each department, then deptno would be unique in the result of a join of emp and
dept, but dept would still not be a key-preserved table.
If you SELECT all rows from emp_dept, the results are:
EMPNO
ENAME
DEPTNO
DNAME
LOC
7782
CLARK
10
ACCOUNTING
NEW YORK
7839
KING
10
ACCOUNTING
NEW YORK
7934
MILLER
10
ACCOUNTING
NEW YORK
7369
SMITH
20
RESEARCH
DALLAS
7876
ADAMS
20
RESEARCH
DALLAS
7902
FORD
20
RESEARCH
DALLAS
7788
SCOTT
20
RESEARCH
DALLAS
7566
JONES
20
RESEARCHDALLAS
8 rows selected.
In this view, emp is a key-preserved table, because empno is a key of the emp table, and
also a key of the result of the join. dept is not a key-preserved table, because although
deptno is a key of the dept table, it is not a key of the join( E.G 20 & 10 is duplicated several
times).
Rules for JOIN VIEWS while performing the following operations:
UPDATE
DELETE
INSERT
Rule
Description
General
Rule
Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base
table in any single SQL operation.
UPDATE
Rule
Only a key-preserved table can be updated. If the view is defined with the WITH CHECK
OPTION clause, then the columns that join (used in WHERE clause) the base tables cannot be
updated.
DELETE
Rule
Rows from a join view can be deleted as long as there is exactly one key-preserved
table in the join. If the view is defined with the WITH CHECK OPTION clause and the key
Lesson 11i
Rule
DML operation on Views & Join Views, TOP-N Analysis
Description
preserved table is repeated, then the rows cannot be deleted from the view.
INSERT
Rule
INSERT statement cannot refer to any columns of a non-key-preserved table. If the join view is
defined with the WITH CHECK OPTION clause, INSERT statements are not permitted.
2. Viewing Allowable DML Operations on view.
The following data dictionary views that indicate whether the columns in a join view are
updatable.
USER_UPDATABLE_COLUMNS view: - views own by the user.
ALL_UPDATABLE_COLUMNS view : - views to which the user has access.
DBA_UPDATABLE_COLUMNS view : - All views in the database.
Example
SQL> select column_name,updatable,insertable,deletable
from user_updatable_columns
WHERE owner= HR
AND table_name= COUNTRY_REGION;
3. TOP-N Analysis.
Why perform a TOP-N Analysis?
Suppose you want to know:
What are the ten best selling products?
What are the ten worst selling products?
What is TOP-N Analysis?
TOP-N queries ask for the n largest or smallest values of a column.
What do you need to know in order to perform TOP-N Analysis?
create INLINE VIEW.
Using INLINE VIEW
