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

1

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<br />  sysdate varchar2(10):= 'Today';<br />begin   <br />  dbms_output.put_line (sysdate);<br />end;<br />&nbsp;

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<br />,      ename<br />,      sal<br />,      versions_starttime<br />,      versions_endtime<br />,      versions_endscn<br />from   emp versions between scn minvalue and maxvalue <br />

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 <br />( select empno<br />  ,      ename<br />  ,      sal<br />  ,      versions_starttime<br />  ,      versions_endtime<br />  ,      versions_endscn<br />  from   emp versions between scn minvalue and maxvalue<br />)<br />, salary_raises as<br />( select empno<br />  ,      sal<br />  ,      lag(sal) over (partition by empno order by versions_endscn asc) previous_sal<br />  ,      ename<br />  ,      versions_starttime<br />  ,      versions_endtime<br />  ,      versions_endscn<br />  from   sal_values<br />)<br />select empno<br />,      ename<br />,      sal<br />,      (sal - previous_sal) absolute_raise<br />,      (sal - previous_sal)/previous_sal*100  percentage_raise<br />,      versions_starttime<br />from   salary_raises<br />order<br />by     ename<br />,      versions_endscn&nbsp;

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

Resources

File with all scripts biggestrayraise.txt 

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. drop table emp;
    create table emp as select * from scott.emp ;
    host sleep 30 ;
    delete emp where ename=’SCOTT';
    insert into emp(ename,empno,sal) values (‘MIKE’,7788,100);
    commit;
    EMPNO ENAME ABSOLUTE_RAISE PERCENTAGE_RAISE VERSIONS_STARTTIME
    ———- ———- ————– —————- ———————
    7788 SCOTT 2900 2900 08-NOV-07 11.32.37 AM

    Scott did not get a salary increase of 2900, new employee Mike started with a salary which is 30x smaller…

    I would add a condition on versions_operation to filter only updates.