20 Things You Should Know About Oracle Views

1. A view is like a virtual table. It takes the output of a query and treats it like a table.
2. A view can be based on one or more tables or other views. These tables/views are called base tables.
3. A view takes up no storage space other than for the definition of the view in the data dictionary.
4. A view contains no data. All the data it shows comes from the base tables.
5. A view can provide an additional level of table security by restricting access to a set of rows or columns of a table.
6. A view hides implementation complexity. The user can select from the view with a simple SQL, unaware that the view is based internally on a join between multiple tables.
7. A view lets you change the data you can access, applying operators, aggregation functions, filters etc. on the base table.
8. A view isolates applications from changes in definitions of base tables. Suppose a view uses two columns of a base table, it makes no difference to the view if other columns are added, modified or removed from the base table.
9. Using views encourages the use of shared SQL, which improves efficiency of frequently invoked SQL.
10. An updatable view allows you to insert, update, and delete rows by propagating the changes to the base table. A view can be updatable provided its definition does not contain any of the following constructs: SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery (or collection expression) in a SELECT list.
11. The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS,and USER_UPDATABLE_COLUMNS indicate which view columns are updatable.
12. Views that are not updatable can be modified using an INSTEAD OF trigger.
13. A view can be created even if the defining query of the view cannot be executed, using the CREATE FORCE VIEW command. Such a view is called a view with errors. This option can be useful for import/installation tools to create a view before the underlying objects are present.
14. A view can be replaced with a CREATE OR REPLACE VIEW statement. The REPLACE option updates the current view definition  but preserves the present security authorizations.
15. A view lets you reorder columns easily with a CREATE OR REPLACE, rather than going into a messy drop column for the base table with data.
16. To know about the views in your own schema, look up user_views.
17. The underlying SQL definition of the view can be read via select text from user_views for the view.
18. Oracle does not enforce constraints on views. Instead, views are subject to the constraints of their base tables.
19. Be careful when you define views of views. Don’t do it just because it is easy to code – it may not be the optimal query. Check if you would be better off using the base tables directly instead.
20. To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user’s schema, you must have the CREATE ANY VIEW system privilege.

The Special DUAL Table

DUAL is a special one-row, one-column table in Oracle’s data dictionary.

Of what use is DUAL?

DUAL comes in handy when you want to select just one row through a query. Oracle SQL structure requires you to have a FROM <table> clause, but some queries don’t need a table – if you want to know the current system date, for example, or the answer for (3+1)*5. DUAL is useful for queries you’d write for such cases:

1	select user from dual;

1	select (3+1)*5 from dual;

But why DUAL? Won’t any table with a rownum < 2 filter work equally well?

Yes, why not simply:

1	select user from scott.emp where rownum < 2;

This query gives you the same result but DUAL has an edge over any other table in the query – the Oracle optimizer recognizes DUAL as a special tableand prepares the best execution plan for it.

What will happen if I insert more rows or delete from DUAL?

The Oracle optimizer trusts DUAL to have exactly one row – no more, no less. If you mess up its data you can lead to unpredictable behavior in the database. Don’t try it!

If DUAL is supposed to have only one row, why is it called DUAL? Why not SINGLE?!

According to this old article on the history of Oracle’s DUAL table, DUAL was originally not meant to be seen itself but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. In that context, the name DUAL seemed fine.

How to find duplicate records in a table

A usual situation we come across in development: a table missed a unique key and allowed duplicate rows to be entered unchecked.  Now we want to find and delete those duplicates.

In this article, we’ll see how to achieve this.

Consider the table dup_emp with these values:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> select *   2  from dup_emp   3  order by empno;        EMPNO ENAME                 SAL ---------- -------------- ----------          1 Adam                  400          2 Sandy                 300          2 Ted                   450          3 Mark                  450          4 Alan                  450          4 Carol                 200          4 Peter                 250          5 David                 350   8 rows selected.

As you can see, there are two entries for empno 2 and three entries for empno 4. We want each empno to correspond to a single employee only.

The query below finds all duplicate records i.e. all empnos with more than one entry in the table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> select *   2  from   3  (select d.*   4        , count(*) over   5          (partition by empno) cnt   6   from dup_emp d   7  )   8  where cnt > 1;        EMPNO ENAME   SAL        CNT ---------- ------ ---- ----------          2 Sandy   300          2          2 Ted     450          2          4 Alan    450          3          4 Carol   200          3          4 Peter   250          3

Deleting the duplicate records

The usual need is to delete all but one such row. The first thing is to know the deciding factor -out of the duplicates, which one is to be retained?

The next SQL retains the row with the minimum rowid:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> delete from dup_emp a   2  where rowid >   3    (select min(rowid)   4     from dup_emp b   5     where a.empno = b.empno);   3 rows deleted.   SQL> select *   2  from dup_emp;        EMPNO ENAME   SAL ---------- ------ ----          1 Adam    400          2 Sandy   300          3 Mark    450          4 Alan    450          5 David   350

Change the WHERE condition according to your needs.