In this blog posting I'd like to write a little bit about views and DML operations on them. The features the Oracle database offers, are not known widely. First of all: A view in the Oracle database is basically updatable. This can be easily tested ...

You'll get an error message when you try a DML operation on this view. To explain the behaviour it's useful to difference between theoretical updatable and theoretical not updatable views. This last example is not updatable at all - the database cannot know how to distribute the salary sum of (here: 20000) to individual employee rows.

There are view which are theoretical updatable, but for which Oracle cannot handle DML operations. Typical cases are views with complex joins (more than one additional table). The data dictionary provides information about updatable columns in the USER|ALL|DBA_UPDATABLE_COLUMNS view. First we check this out for the very first example in this blog posting ...

TABLE_NAME COLUMN_NAME INS UPD DEL
------------------------------ ------------------------------ --- --- ---
EMP_AGG DEPTNO NO NO NO
EMP_AGG DNAME NO NO NO
EMP_AGG SUM_SAL NO NO NO
EMP_AGG AVG_SAL NO NO NO
EMP_AGG HEADCOUNT NO NO NO

Such a NO is indeed not the last word. Coding an INSTEAD-OF Trigger makes every view updatable: The trigger code tells the database what to do with a DML operation on the view. Such a trigger might look as follows ...

INSTEAD-OF triggers cannot be created for tables - they're only allowed for views. The trigger "catches" the original DML operation and executes the code in its body instead. The dictionary reflects the existence of the INSTEAD-OF trigger as the following output indicates ...

Putting it all together we can say that views are very powerful in the Oracle database. Using INSTEAD-OF triggers views can be used as specific interfaces for every kind of dataset. DML operations can be "catched" by INSTEAD-OF triggers. A nice application of this concept is Oracle Workspace manager which allows to implement long running transactions and different versions of table data.

This looks much simpler than "classic" sequences where you need the additional sequence object and a trigger for populating the column (sequences cannot be used in the default clause of a column). Another advantage is the global uniqueness of the GUID. When the data gets merged with data from
other databases there will be no conflicts. If you don't like working with RAW columns you can also convert them to a NUMBER column.

To compare the disk space consumption we're going to create three tables: The first will be populated with values from a "classic" sequence - the second will contain GUIDs as RAW(16) and the last one will have the GUIDs converted to a NUMBER datatype. After creating and populating the table we'll determine the size of the table and index segments with the script I posted some months ago. Be aware that this script is written for 11g.

It's obvious that the larger GUID values consume more
space on disk. These are the numbers for 100.000 values. You can now easily calculate the
size for your amount of tables and rows. Using the VSIZE function
you can also determine the size of an indivual value in bytes. You then see that the
"classic" sequence values (between 1 and 100.000) have a size from two up to four bytes; the
GUID values need 16 bytes each when using the RAW datatype and up to 21 bytes when
converting them to a NUMBER column.

At the first glance this seems to be a less
important point. But keep in mind that this also
affects the buffer cache usage. The bigger the tables get, the less fit into the buffer
cache - and increasing the physical memory of the database machine is not that easy
as increasing disk space. And when you think about DWH scenarios with really huge
tables ... then these numbers will matter.

In summary, GUIDs are an interesting alternative for
populating primary key values. The advantages are their easy setup and global uniqueness,
which is particularly useful for merging tables from different database instances.
The disadvantage is the large size which leads to more disk and buffer cache
consumption. For the Oracle developer ... it's always good to know about both ...

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.