Skip to Main Content
  • Questions

  • SQL query for history / as-of a certain date

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: January 29, 2003 - 9:43 am UTC

Last updated: September 09, 2004 - 4:07 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

I have a simple history table that captures all the :new values in a row-level after insert/update/delete (:old, of course) trigger.

I want to answer the question: show me the value for column A as of 1/1/2001

I would need to do something like

select columna from hist_table
where timestamp=(select max(timestamp) from hist_table where timestamp < '1/1/2001')

This seems very awkward.

Using the new analytic functions in 8i, is there a more elegant/efficent way to get SQL to do this?

Thanks a lot.

and Tom said...

yes, analytics could do this but -- this would be the most efficient:

select *
from ( select *
from history
where timestamp <= to_date('01/01/2001','dd/mm/yyyy')
order by timestamp DESC )
where rownum = 1
/

assumming index on timestamp that is.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why?

A Reader, January 29, 2003 - 1:10 pm UTC

Tom,

I always wonder "how do you know this", know this to be more efficient in this case, when I see your answers - is it from running both and looking at the stats, or just from experience ?

Regards,

Paul

Tom Kyte
January 29, 2003 - 1:19 pm UTC 

In this case -- i knew.  most of the time I benchmark and supply that.

one thing I neglected, you would consider using first rows optimization:

ops$tkyte@ORA817DEV> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create index t_idx on t(created);

Index created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> analyze table t compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

Table analyzed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> set autotrace traceonly
ops$tkyte@ORA817DEV> select *
  2    from t
  3   where created = ( select max(created)
  4                       from t
  5                       where created < to_date( '01/01/2003', 'dd/mm/yyyy' ) );


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=38 Bytes=3686)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=38 Bytes=3686)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=38)
   3    2       SORT (AGGREGATE)
   4    3         FIRST ROW (Cost=2 Card=23751 Bytes=166257)
   5    4           INDEX (RANGE SCAN (MIN/MAX)) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=23751)




Statistics
----------------------------------------------------------
        242  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       1210  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select *
  2    from (select /*+ FIRST_ROWS */ * from t order by created DESC )
  3   where rownum = 1
  4  /


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1544 Card=24103 Bytes=3085184)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=1544 Card=24103 Bytes=3085184)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1544 Card=24103 Bytes=2337991)
   4    3         INDEX (FULL SCAN DESCENDING) OF 'T_IDX' (NON-UNIQUE) (Cost=65 Card=24103)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1135  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> set autotrace off


like that.

Where's the rating "6 - I OWE TOM" ?!?!?!

A Reader, January 29, 2003 - 2:10 pm UTC

Tom,

I do this many times in my app, in, dare I say it, loops - I tried it with a sample 100,000 iterations and your way takes half the LIO which for me equates to about half the time!!!!

Thanks again - its so good to pick up these things each day and add them to your toolkit !

Cheers

Paul

Seldom error

Michael, January 30, 2003 - 3:53 am UTC 

I tried this rownum solution, but i get a seldom error:

select e.ename, 
       (select max(e1.empno)
        from (select e2.empno
              from emp e2
              where e2.ename > e.ename
              order by e2.ename asc) e1
        where rownum = 1) next_empno
from emp e
where e.empno = 7839;

I expected to get ENAME = 'KING' and NEXT_EMPNO = 7654 - the empno of 'MARTIN', but i get:

SQL> select e.ename, 
  2         (select max(e1.empno)
  3          from (select e2.empno
  4                from emp e2
  5                where e2.ename > e.ename
  6                order by e2.ename asc) e1
  7          where rownum = 1) next_empno
  8  from emp e
  9  where e.empno = 7839;
              where e2.ename > e.ename
                               *
ERROR in Line 5:
ORA-00904: invalid identifier

It seems that oracle doesn't recognize the table e in the subquery. Why?

Tom Kyte
January 30, 2003 - 8:55 am UTC

because the correlation names only go one level down.


you should query:

select ename, lead(empno) over (order by ename)
from emp;


easier no?

Easier, but ...

Michael, January 30, 2003 - 10:04 am UTC

Yes, it would be easier, but when i query:

select ename, lead(empno) over (order by ename)
from emp
where empno = 7839;

I get:

ENAME LEAD(EMPNO)OVER(ORDERBYENAME)
---------- -----------------------------
KING

The where-clause "killed" the sense of the lead function.
(I tried using the lead function in an inline view, but - with a big emp table this is quite slow...)

Tom Kyte
January 30, 2003 - 11:24 am UTC

you can use min/max to do this in a single non-nested query


scott@ORA920> select ename
2 from (select ename from emp order by hiredate)
3 where rownum = 1
4 /

ENAME
----------
SMITH

scott@ORA920>
scott@ORA920> select substr( min( to_char(hiredate,'yyyymmddhh24miss') || ename)
, 15 )
2 from emp
3 /

SUBSTR(MIN
----------
SMITH

scott@ORA920>
scott@ORA920>
scott@ORA920> select ename
2 from (select ename from emp order by SAL desc)
3 where rownum = 1
4 /

ENAME
----------
KING

scott@ORA920>
scott@ORA920> select substr( max( to_char(sal,'fm000000000.00') || ename) , 13 )
2 from emp
3 /

SUBSTR(MAX(
-----------
KING

scott@ORA920>
scott@ORA920>
scott@ORA920> select empno
2 from (select empno from emp order by ENAME desc)
3 where rownum = 1
4 /

EMPNO
----------
7521

scott@ORA920>
scott@ORA920> select substr( max( rpad(ename,30) || empno) , 31 )
2 from emp
3 /

SUBSTR(MAX(RPAD(ENAME,30)||EMPNO),31)
----------------------------------------
7521

What i want to do ...

Michael, January 31, 2003 - 2:53 am UTC

Thanks for your answer.

I thought of this rownum and min/max approach, too ...

Perhaps it's best to explain what i want to do (even when this will open a completly new question ;-).
I want to get some data about a certain emp and the rowids of the first, previos, next and last emp (first and next etc. in the sense of order by ename).

I came up with the following query:

select e.empno, e.ename, e.mgr, e.sal, e.comm,
e.deptno, d.rowid,
(select min(e1.rowid) from emp e1
where e1.ename in
(select min(e2.ename) from emp e2)) first,
(select max(e1.rowid) from emp e1
where e1.ename in
(select max(e2.ename) from emp e2
where e2.ename < e.ename)) prev,
(select min(e1.rowid) from emp e1
where e1.ename in
(select min(e2.ename) from emp e2
where e2.ename > e.ename)) next,
(select max(e1.rowid) from emp e1
where e1.ename in
(select max(e2.ename) from emp e2)) last
from emp e, dept d
where e.rowid = 'SOME_ROWID_OF AN EMP'
and e.deptno = d.deptno (+);

This query works - there is an index on ename. But it's speed could (better: should) be better. Any idea or is this a (nearly) optimal solution?

(Seldom - i can reference e in the prev and next sub-subquery ...)

Tom Kyte
January 31, 2003 - 8:23 am UTC 

say you have:

ops$tkyte@ORA920> /*
DOC>drop table emp;
DOC>drop table dept;
DOC>
DOC>create table emp as
DOC>select owner || '.' || object_name || '.' || object_type ename, a.*
DOC>  from all_objects a;
DOC>
DOC>create index ename_idx on emp(ename);
DOC>
DOC>create table dept as
DOC>select username deptno, a.*
DOC>  from all_users a;
DOC>
DOC>alter table dept add constraint dept_pk primary key(deptno);
DOC>
DOC>analyze table emp compute statistics
DOC>for table for all indexes for all indexed columns;
DOC>analyze table dept compute statistics
DOC>for table for all indexes for all indexed columns;
DOC>*/
ops$tkyte@ORA920>
ops$tkyte@ORA920> variable rid varchar2(255)
ops$tkyte@ORA920> begin
  2          select rowid into :rid
  3            from emp
  4           where object_id = ( select percentile_cont(0.5) within group (order by object_id)
  5                             from emp );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> print rid

RID
-----------------------------------------------------------------------------------------------------------------------------------
AAAJVcAAJAAAVeQAAg

ops$tkyte@ORA920>

<b>then:
</b>

ops$tkyte@ORA920> set autotrace on
ops$tkyte@ORA920> select object_id empno, ename, data_object_id mgr, dept.deptno dname
  2    from (
  3  select *
  4    from ( select /*+ first_rows */ *
  5             from emp
  6            where ename < (select ename from emp where rowid = :rid)
  7            order by ename DESC
  8              )
  9   where rownum = 1
 10   union all
 11  select *
 12    from emp
 13   where rowid = :rid
 14   union all
 15  select *
 16    from ( select /*+ first_rows */ *
 17             from emp
 18            where ename > (select ename from emp where rowid = :rid)
 19            order by ename
 20              )
 21   where rownum = 1
 22         ) e, dept
 23   where e.owner = dept.deptno(+)
 24  /

     EMPNO ENAME                                 MGR DNAME
---------- ------------------------------ ---------- ------------------------------
      8051 SYS./b7cb866f_Imports.JAVA CLA            SYS
           SS

     14983 SYS./b7d18c9f_ObjectTypeChange            SYS
           dExc.JAVA CLASS

      7469 SYS./b7fa67b8_CustomizerHarnes            SYS
           sMan.JAVA CLASS


3 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=19 Card=3 Bytes=273)
   1    0   NESTED LOOPS (OUTER) (Cost=19 Card=3 Bytes=273)
   2    1     VIEW (Cost=19 Card=3 Bytes=255)
   3    2       UNION-ALL
   4    3         COUNT (STOPKEY)
   5    4           VIEW (Cost=9 Card=1481 Bytes=125885)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=9 Card=1481 Bytes=198454)
   7    6               INDEX (RANGE SCAN DESCENDING) OF 'ENAME_IDX' (NON-UNIQUE) (Cost=3 Card=267)
   8    7                 TABLE ACCESS (BY USER ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=44)
   9    3         TABLE ACCESS (BY USER ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=87)
  10    3         COUNT (STOPKEY)
  11   10           VIEW (Cost=9 Card=1481 Bytes=125885)
  12   11             TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=9 Card=1481 Bytes=198454)
  13   12               INDEX (RANGE SCAN) OF 'ENAME_IDX' (NON-UNIQUE) (Cost=3 Card=267)
  14   13                 TABLE ACCESS (BY USER ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=44)
  15    1     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        718  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

ops$tkyte@ORA920>
ops$tkyte@ORA920> select e.object_id empno, e.ename, e.data_object_id mgr, d.deptno dname,
  2         (select min(e1.rowid) from emp e1
  3          where e1.ename in
  4                (select min(e2.ename) from emp e2)) first,
  5         (select max(e1.rowid) from emp e1
  6          where e1.ename in
  7                (select max(e2.ename) from emp e2
  8                 where e2.ename < e.ename)) prev,
  9         (select min(e1.rowid) from emp e1
 10          where e1.ename in
 11                (select min(e2.ename) from emp e2
 12                 where e2.ename > e.ename)) next,
 13         (select max(e1.rowid) from emp e1
 14          where e1.ename in
 15                (select max(e2.ename) from emp e2)) last
 16  from emp e, dept d
 17  where e.rowid = :rid
 18  and e.owner = d.deptno (+);

     EMPNO ENAME                                 MGR DNAME                          FIRST              PREV
---------- ------------------------------ ---------- ------------------------------ ------------------ ------------------
NEXT               LAST
------------------ ------------------
     14983 SYS./b7d18c9f_ObjectTypeChange            SYS                            AAAJVcAAJAAAGFMAAU AAAJVcAAJAAAVeQAAf
           dExc.JAVA CLASS
AAAJVcAAJAAAVeQAAh AAAJVcAAJAAAGEmAAj


1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=93)
   1    0   NESTED LOOPS (OUTER) (Cost=1 Card=1 Bytes=93)
   2    1     TABLE ACCESS (BY USER ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=87)
   3    1     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        863  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA920>
ops$tkyte@ORA920> set autotrace off


<b>but if you can get away from ROWID and just get back to basics and use ENAME</b>

ops$tkyte@ORA920> variable ename varchar2(255)
ops$tkyte@ORA920> begin
  2          select ename into :ename
  3            from emp
  4           where rowid = :rid;
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> print ename

ENAME
------------------------------
SYS./b7d18c9f_ObjectTypeChange
dExc.JAVA CLASS


ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> set autotrace on
ops$tkyte@ORA920> select object_id empno, ename, data_object_id mgr, dept.deptno dname
  2    from (
  3  select *
  4    from ( select /*+ first_rows */ *
  5             from emp
  6            where ename < :ename
  7            order by ename DESC
  8              )
  9   where rownum = 1
 10   union all
 11  select *
 12    from emp
 13   where rowid = :rid
 14   union all
 15  select *
 16    from ( select /*+ first_rows */ *
 17             from emp
 18            where ename > :ename
 19            order by ename
 20              )
 21   where rownum = 1
 22         ) e, dept
 23   where e.owner = dept.deptno(+)
 24  /

     EMPNO ENAME                                 MGR DNAME
---------- ------------------------------ ---------- ------------------------------
      8051 SYS./b7cb866f_Imports.JAVA CLA            SYS
           SS

     14983 SYS./b7d18c9f_ObjectTypeChange            SYS
           dExc.JAVA CLASS

      7469 SYS./b7fa67b8_CustomizerHarnes            SYS
           sMan.JAVA CLASS


3 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=19 Card=3 Bytes=273)
   1    0   NESTED LOOPS (OUTER) (Cost=19 Card=3 Bytes=273)
   2    1     VIEW (Cost=19 Card=3 Bytes=255)
   3    2       UNION-ALL
   4    3         COUNT (STOPKEY)
   5    4           VIEW (Cost=9 Card=1481 Bytes=125885)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=9 Card=1481 Bytes=198454)
   7    6               INDEX (RANGE SCAN DESCENDING) OF 'ENAME_IDX' (NON-UNIQUE) (Cost=3 Card=267)
   8    3         TABLE ACCESS (BY USER ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=87)
   9    3         COUNT (STOPKEY)
  10    9           VIEW (Cost=9 Card=1481 Bytes=125885)
  11   10             TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=9 Card=1481 Bytes=198454)
  12   11               INDEX (RANGE SCAN) OF 'ENAME_IDX' (NON-UNIQUE) (Cost=3 Card=267)
  13    1     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        718  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

ops$tkyte@ORA920> set autotrace off

<b>we can do even better.  some food for thought</b>

Help

V, September 09, 2004 - 3:42 pm UTC

I have the following:

SELECT unique a.name
from unit a
where a.time = (select time from (select * from unit
where id = a.id order by time DESC) where rownum=1)

I keep getting invalid identifier error ORA-00933
What would another approach be?

Tom Kyte
September 09, 2004 - 4:07 pm UTC

In english:

get the rows such that the time for the row is the max time for the set of rows with the same id....

well, without a create table, insert into -- given some sample data -- you'll have to debug these, i'm just typing "freestyle" here:

select unique name
from (select name, row_number() over (partition by id order by time desc) rn
from unit )
where rn = 1;

that assigned a row_number for each set of ID's after ordering by time desc, just keep the first from each id and distinct them...


or

select distinct name
from (
select id, substr( max( to_char(time,'yyyymmddhh24miss') || name ), 15 ) name
from unit
group by id
)
/

that, by id, found the record with the max time and saved the name on the end of it. we substr it back and and distinct it.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library