Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Tag Archives: Oracle 11g R1

Compound Trigger – A Performance Improvement for Trigger Executions

In Oracle database 11g release version 1, among the enhancements in trigger execution an important and efficient feature known as ‘Compound Trigger’ is rolled out. This feature is particularly exciting because of the performance improvements it may bring in along with it.

Auditing Bulk INSERT/UPDATE of a Table Efficiently

Assume you have a transactional table, this table is highly accessed by a front end application which does a lot of inserts and updates every minute. You also have a functional requirement of keeping track of the changes against the values inserted / updated. The traditional setup one would have for such a scenario is to write a separate trigger each for AFTER INSERT and AFTER UPDATE to fetch the values and insert them into an auditing table as well. Read more of this post

Virtual Columns in Oracle 11g R1

I just found out this good feature in Oracle 11g release 1. How many times you felt tired because you were coding again and again for a particular function while selecting data from table?

Consider the following table. The table contains employee name, date of joining. Year of joining will not be stored in database as it can be always derived from one data. An dependable data! Assume, you need it desperately for some sake. One way is use views to achieve the purpose.

With Oracle 11gR1 virtual columns, you can add an virtual column in table for easy selection.

SQL> create table empTable
 2         ( name varchar2(20)
 3         , doj date
 4         , yoj generated always as (to_char(doj,'yyyy')) virtual
 5         )
 6 /
Table created.

Read more of this post