Skip to Main Content
  • Questions

  • Efficient way to grab 'current effective dated row' from a table

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Nathan.

Asked: June 04, 2008 - 12:30 pm UTC

Last updated: June 10, 2008 - 8:00 am UTC

Version: 10.2.0

Viewed 1000+ times

You Asked 


create table tbl ( 
  institution varchar(20), 
  subject varchar(20), 
  effdt date, 
  other1 varchar(20), 
  other2 varchar(20), 
  other3 varchar(20) 
);


pk is institution, subject, effdt

Need to select the most recent active row from this table (effdt < sysdate).

Below query works, but I've seen you use many neat tricks that I thought I'd ask if there is any way to do this cleaner/shorter. It seems almost like a 'group by' type of query, but with extra non-aggregate columns that aren't part of the grouping.

select 
 institution, subject, effdt, other1, other2, other3
from 
 tbl a 
where 
 a.effdt = (
  select 
   max(effdt) 
  from 
   tbl b 
  where 
   a.institution=b.institution and a.subject=b.subject and b.effdt < sysdate
 )



and Tom said...

ops$tkyte%ORA10GR2> select *
  2    from (select t.*, rank() over (partition by deptno order by hiredate DESC) rn
  3            from scott.emp t
  4           where hiredate < sysdate)
  5   where rn = 1
  6   order by deptno
  7  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10          1
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20          1
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30          1

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select t.*
  2    from scott.emp t
  3   where (deptno,hiredate) in
  4            ( select deptno, max(hiredate)
  5                from scott.emp t
  6               where hiredate < sysdate
  7               group by deptno)
  8   order by deptno
  9  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30



either of those accomplish the job.


Rating

  (5 ratings)

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

Comments

rank for max

A reader, June 05, 2008 - 4:15 pm UTC

Good idea

Another approach

Andrey, June 05, 2008 - 4:43 pm UTC

What about this?

select a.* from
tbl a
left join tbl b
on a.institution = b.institution and a.subject = b.subject and b.effdt > a.effdt
where b.institution is null;

I find that this is actually a lot more efficient in many scenarios, especially if you have another condition in the where clause on "a" that utilizes an index, in that case the left join to "b" will usually turn into an index range scan.
Tom Kyte
June 05, 2008 - 7:38 pm UTC

really - you have found this more efficient??? A non-equi join? (basically an anti join ultimately) for all of the rows in a table??


I found this to be "slower than", well, most things...



ps$tkyte%ORA10GR2> create table t as select * from all_objects where rownum <= 5000;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA10GR2> select owner, created, object_id, object_name from (select t.*, rank() over (partition by owner order by created DESC) rn from t ) where rn = 1;

OWNER                          CREATED    OBJECT_ID OBJECT_NAME
------------------------------ --------- ---------- ------------------------------
OUTLN                          30-JUN-05        452 OL$
OUTLN                          30-JUN-05        453 OL$HINTS
OUTLN                          30-JUN-05        456 OL$NODES
OUTLN                          30-JUN-05        457 OL$NAME
OUTLN                          30-JUN-05        459 OL$HNT_NUM
OUTLN                          30-JUN-05        458 OL$SIGNATURE
PUBLIC                         04-AUG-06       2317 ALL_ENCRYPTED_COLUMNS
PUBLIC                         04-AUG-06       2319 USER_ENCRYPTED_COLUMNS
SYS                            04-AUG-06       4935 DEF$_DEFAULTDEST
SYSTEM                         30-JUN-05       4902 DEF$_LOB_PRIMARY
SYSTEM                         30-JUN-05       4917 DEF$_PUSHED_TRANSACTIONS
SYSTEM                         30-JUN-05       4918 DEF$_PUSHED_TRAN_PRIMARY
SYSTEM                         30-JUN-05       4916 DEF$_ORIGIN
SYSTEM                         30-JUN-05       4903 DEF$_LOB_N1
SYSTEM                         30-JUN-05       4912 DEF$_PROPAGATOR
SYSTEM                         30-JUN-05       4913 DEF$_PROPAGATOR_PRIMARY
SYSTEM                         30-JUN-05       4914 DEF$_PROPAGATOR_TRIG
SYSTEM                         30-JUN-05       4904 DEF$_TEMP$LOB

18 rows selected.

ops$tkyte%ORA10GR2> select owner, created, object_id, object_name from t where (owner,created) in ( select owner, max(created) from t group by owner );

OWNER                          CREATED    OBJECT_ID OBJECT_NAME
------------------------------ --------- ---------- ------------------------------
OUTLN                          30-JUN-05        452 OL$
OUTLN                          30-JUN-05        453 OL$HINTS
OUTLN                          30-JUN-05        456 OL$NODES
OUTLN                          30-JUN-05        457 OL$NAME
OUTLN                          30-JUN-05        458 OL$SIGNATURE
OUTLN                          30-JUN-05        459 OL$HNT_NUM
PUBLIC                         04-AUG-06       2317 ALL_ENCRYPTED_COLUMNS
PUBLIC                         04-AUG-06       2319 USER_ENCRYPTED_COLUMNS
SYSTEM                         30-JUN-05       4902 DEF$_LOB_PRIMARY
SYSTEM                         30-JUN-05       4903 DEF$_LOB_N1
SYSTEM                         30-JUN-05       4904 DEF$_TEMP$LOB
SYSTEM                         30-JUN-05       4912 DEF$_PROPAGATOR
SYSTEM                         30-JUN-05       4913 DEF$_PROPAGATOR_PRIMARY
SYSTEM                         30-JUN-05       4914 DEF$_PROPAGATOR_TRIG
SYSTEM                         30-JUN-05       4916 DEF$_ORIGIN
SYSTEM                         30-JUN-05       4917 DEF$_PUSHED_TRANSACTIONS
SYSTEM                         30-JUN-05       4918 DEF$_PUSHED_TRAN_PRIMARY
SYS                            04-AUG-06       4935 DEF$_DEFAULTDEST

18 rows selected.

ops$tkyte%ORA10GR2> select t1.owner, t1.created, t1.object_id, t1.object_name from t t1 left join t t2 on ( t1.owner = t2.owner  and t2.created > t1.created) where t2.owner is null;

OWNER                          CREATED    OBJECT_ID OBJECT_NAME
------------------------------ --------- ---------- ------------------------------
OUTLN                          30-JUN-05        452 OL$
OUTLN                          30-JUN-05        453 OL$HINTS
OUTLN                          30-JUN-05        456 OL$NODES
OUTLN                          30-JUN-05        457 OL$NAME
OUTLN                          30-JUN-05        458 OL$SIGNATURE
OUTLN                          30-JUN-05        459 OL$HNT_NUM
PUBLIC                         04-AUG-06       2317 ALL_ENCRYPTED_COLUMNS
PUBLIC                         04-AUG-06       2319 USER_ENCRYPTED_COLUMNS
SYSTEM                         30-JUN-05       4902 DEF$_LOB_PRIMARY
SYSTEM                         30-JUN-05       4903 DEF$_LOB_N1
SYSTEM                         30-JUN-05       4904 DEF$_TEMP$LOB
SYSTEM                         30-JUN-05       4912 DEF$_PROPAGATOR
SYSTEM                         30-JUN-05       4913 DEF$_PROPAGATOR_PRIMARY
SYSTEM                         30-JUN-05       4914 DEF$_PROPAGATOR_TRIG
SYSTEM                         30-JUN-05       4916 DEF$_ORIGIN
SYSTEM                         30-JUN-05       4917 DEF$_PUSHED_TRANSACTIONS
SYSTEM                         30-JUN-05       4918 DEF$_PUSHED_TRAN_PRIMARY
SYS                            04-AUG-06       4935 DEF$_DEFAULTDEST

18 rows selected.


select owner, created, object_id, object_name
from (select t.*, rank() over (partition by owner order by created DESC) rn from
  t ) where rn = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.01       0.01          0         64          0          18
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.02       0.02          0         65          0          18

Rows     Row Source Operation
-------  ---------------------------------------------------
     18  VIEW  (cr=64 pr=0 pw=0 time=10869 us)
   5000   WINDOW SORT PUSHED RANK (cr=64 pr=0 pw=0 time=20852 us)
   5000    TABLE ACCESS FULL T (cr=64 pr=0 pw=0 time=5058 us)
********************************************************************************
select owner, created, object_id, object_name
from t where (owner,created) in ( select owner, max(created) from t group by
  owner )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0         10          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.01       0.01          0        130          0          18
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.02       0.02          0        140          0          18

Rows     Row Source Operation
-------  ---------------------------------------------------
     18  HASH JOIN RIGHT SEMI (cr=130 pr=0 pw=0 time=7163 us)
      4   VIEW  VW_NSO_1 (cr=64 pr=0 pw=0 time=5916 us)
      4    HASH GROUP BY (cr=64 pr=0 pw=0 time=5895 us)
   5000     TABLE ACCESS FULL T (cr=64 pr=0 pw=0 time=5069 us)
   5000   TABLE ACCESS FULL T (cr=66 pr=0 pw=0 time=5090 us)
********************************************************************************
select t1.owner, t1.created, t1.object_id, t1.object_name
from t t1 left join t t2 on ( t1.owner = t2.owner  and t2.created > t1.created)
  where t2.owner is null


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      2.51       2.46          0        130          0          18
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      2.52       2.47          0        132          0          18

Rows     Row Source Operation
-------  ---------------------------------------------------
     18  FILTER  (cr=130 pr=0 pw=0 time=395313 us)
6834641   HASH JOIN RIGHT OUTER (cr=130 pr=0 pw=0 time=6838828 us)
   5000    TABLE ACCESS FULL T (cr=64 pr=0 pw=0 time=5059 us)
   5000    TABLE ACCESS FULL T (cr=66 pr=0 pw=0 time=5171 us)



it would be greatly affected by the number of records created in the anti join (how many records are in each "key" - owner being my key here...). The almost cartesian product by key would get quite large.

Depends on the data

Andrey, June 06, 2008 - 11:25 am UTC

Hi Tom,

I agree, it depends very much on how much data you have to left join. If it's a small set of data, then it usually chooses "index range scan" rather than a join to actually perform the query. This is why I said that my approach is useful only if you have an additional condition in the where clause that limits the number of records to join.

I'll have to experiment with these ideas some more.

Thanks


Double Check needed

Jmv, June 06, 2008 - 11:55 am UTC

Tom,

You might want to double check the "Followup June 5, 2008 - 7pm US/Eastern:" for the review " rank for max June 5, 2008 - 4pm US/Eastern Bookmark | Bottom | TopReviewer: A reader " as your follow up might be intended for another thread.

Seems you are talking about "clustering factors" and "bitmap indexes" in the follow up to the "Good idea" comment.


Tom Kyte
June 09, 2008 - 10:26 am UTC

indeed, cut and paste error, I've fixed it. thanks!

Loz, June 10, 2008 - 4:35 am UTC

Tom,
Although I no longer use Oracle, I still come back here to get some tips on logical thinking and general best practice to apply over other situations. Thanks very much for all your help & insights.

Now a question. A few years ago I seem to remember reading about a feature where you can configure your database to provide this 'as-at' functionality. I think it worked by creating views onto versioned tables (rather than using undo) and you specify the 'as at' date in some kind of context variable. I since had a look for it on google and can't find it. Does such a thing exist or do you think it more likely my poor confused brain got mixed up with the flashback functionality (which I think is more used for recovery than at the application level)?

Thanks.
Tom Kyte
June 10, 2008 - 8:00 am UTC

it is "as of"

we call it flashback query.



ops$tkyte%ORA11GR1> update scott.emp set sal = sal *1.1;

14 rows updated.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select o.ename, o.sal prior_sal, n.sal current_sal
  2    from scott.emp n, scott.emp as of timestamp sysdate-5 o
  3   where o.empno = n.empno
  4  /

ENAME       PRIOR_SAL CURRENT_SAL
---------- ---------- -----------
SMITH             800         880
ALLEN            1600        1760
WARD             1250        1375
JONES            2975      3272.5
MARTIN           1250        1375
BLAKE            2850        3135
CLARK            2450        2695
SCOTT            3000        3300
KING             5000        5500
TURNER           1500        1650
ADAMS            1100        1210
JAMES             950        1045
FORD             3000        3300
MILLER           1300        1430

14 rows selected.



we added multiversioning in version 3.0
we added read consistency in version 4.0

we added the ability for you to specify when data should be "as of" in 9iR1.



There is also the ability since 8i to use workspace manager (dbms_wm) to version enable a table and query it as of a past point in time using dbms_wm.gotodate


And in 11g there is the flashback data archive.
http://www.oracle.com/technology/products/database/oracle11g/pdf/flashback-data-archive-whitepaper.pdf
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

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.