The Great Oracle SQL Quiz – answers to two sample questions – why we do no longer need journalling tables

Earlier today I wrote about The Great Oracle SQL Quiz session at Oracle Open World. I presented two sample questions – that will not be in the quiz at OOW but are fairly similar to the questions that will be (see: Sample Questions). Here we will look at the answers to those two questions.

The first question introduced a strange phenomenon in SQL:

The question was what on earth could have caused this:

A)  alter system set sysdate = NLS_FRIENDLY

B)  declare
      sysdate varchar2(10):= ‘Today’;
    begin

  
C)  alter system set NLS_DATE_FORMAT=’RELATIVE’ 
 
D)  update dual set sysdate =’TODAY’

The correct answer
is B. The entire code fragment is this one – strange as it may seem:

declare
  sysdate varchar2(10):= 'Today';
begin   
  dbms_output.put_line (sysdate);
end;
 

The second question is slightly more useful. It arose during a presentation we did last week where Alex and I demonstrated the most important new features in Oracle 11g for Developers.

It is my believe that probably the best new feature for developers in 11g is the Flashback Data Archive (aka Oracle Total Recall). It is the previously missing link that makes the Flashback technology – primarily Flashback Query and Flashback Versions Query – available to developers. Prior to 11g, Flashback Query is a great tool for DBAs and Application Administrators. It allows them to recover data from the past – restore situations that once existed in the database, thus recovering from physical or human errors. However, since the Flashback technology was driven from the single huge pile of UNDO data, it was impossible to build application code that could rely on the availability of historical data; there was no certainty of that data still being around in the Flashback Area.

With the Flashback Data Archive, we can do fine grained flashback enabling of our tables: for each table that we want to preserve historical data for we can create an association with a Flashback Data Archive with a specified Retention Time.

Now we can include those Retention Times in our database design. With almost no effort, we can enable Flashback Query on any table we like.

When I had presented all that, and added that this means we no longer need journalling tables that are populated from DML triggers, the question came up if we could then still easily query historical trends. After all, a Flashback Query can query historical records, but only for a specific moment in time. With a journal table that contains all various states of the data, it is easy to query on changes through time.

That is where the sample quiz question originated from:

Here we see the employees and their salaries. That
is the current situation. We see nothing about the past. And we would
like to answer the question: "Who has received the biggest payraise in
history?" (biggest both as a percentage and an absolute number).

Which database feature do you think we can best employ for answering that question?

A. The supplied package DBMS_LOGMNR

B. Aggregate Change Index

C. Flashback Versions Query

D. Materialized View in combination with After Update Row trigger

With my previous rambling on Flashback technology, it will probably come as no surprise that the correct answer to this question is C. The Flashback Versions Query does exactly what the question in our demonstration was after: it returns all states the data has been in between two moments in time.

A simple query that returns history for the Employees would like something like:

select empno
,      ename
,      sal
,      versions_starttime
,      versions_endtime
,      versions_endscn
from   emp versions between scn minvalue and maxvalue 

We  need to do little bit more – including a bit Analytical functions (LAG) to find the salary changes between these records:

with sal_values as 
( select empno
  ,      ename
  ,      sal
  ,      versions_starttime
  ,      versions_endtime
  ,      versions_endscn
  from   emp versions between scn minvalue and maxvalue
)
, salary_raises as
( select empno
  ,      sal
  ,      lag(sal) over (partition by empno order by versions_endscn asc) previous_sal
  ,      ename
  ,      versions_starttime
  ,      versions_endtime
  ,      versions_endscn
  from   sal_values
)
select empno
,      ename
,      sal
,      (sal - previous_sal) absolute_raise
,      (sal - previous_sal)/previous_sal*100  percentage_raise
,      versions_starttime
from   salary_raises
order
by     ename
,      versions_endscn 

This gives us all history of the Employees, their salary values and the salary changes that occurred, including the timestamp of that particular change. Note: I do not filter for only salary changes – so changes in Job or Deptno will also result in records returned by this query.

To determine who had the biggest payraise, we can finally execute the following query:

with sal_values as 
( select empno
  ,      ename
  ,      sal
  ,      versions_starttime
  ,      versions_endtime
  ,      versions_endscn
  from   emp versions between scn minvalue and maxvalue
)
, salary_raises as
( select empno
  ,      sal
  ,      lag(sal) over (partition by empno order by versions_endscn asc) previous_sal
  ,      ename
  ,      versions_starttime
  ,      versions_endtime
  ,      versions_endscn
  from   sal_values
)
, ordered_absolute_payraises as
( select empno
  ,      ename
  ,      sal
  ,      (sal - previous_sal) absolute_raise
  ,      (sal - previous_sal)/previous_sal*100  percentage_raise
  ,      versions_starttime
  from   salary_raises
  order
  by     absolute_raise desc nulls last
)
select empno
,      ename
,      absolute_raise
,      percentage_raise
,      versions_starttime 
from   ordered_absolute_payraises
where  rownum = 1

Resources

File with all scripts biggestrayraise.txt