Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Tauseef.

Asked: February 10, 2016 - 7:05 am UTC

Last updated: February 10, 2016 - 12:13 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

There is a table t1 with primary key t1_pk.
There is another table t2 which has multiple rows for each row of t1. There can be a scenario where t2 might not have any row for a row in t1.
Now we need to fetch only one row from t2 by the below condition.

select t2.col1 from t2, t1
where
t2.foreign_key = t1.t1_pk
order by date1 desc, date2 desc, date3 desc.

I have to take the topmost row in the above query.

Now factor this - this has to be done for around 10 million rows of t1.

If I write a small function/stored procedure for each of the row of t1 then it will take hell lot of time. IS there any other quicker method to achieve the same.


and Chris said...

You're right, making a function to return the t2 values for each row of t1 will take a long time. You should write a single statement that fetches all the values at once.

You can do this by:

1. Joining the two tables
2. Assigning a number starting at 1 for each t2 row in each t1 set
3. Filter so it only returns the rows where n = 1 for the step above.

The analytic row_number() will do step 2 for you. Place the t1 pk in the partition by so it starts at 1 for each of these values. The the order by from your original query and place after this in the over clause:

create table t1 (x primary key) as
  select rownum x from dual connect by level <= 100;
  
create table t2 as
  with rws as (
    select rownum r from dual connect by level <= 5
  )
  select x, r, sysdate d1, sysdate-x d2, sysdate-r d3 from t1, rws
  where  x <= 50;

alter table t2 add constraint pk primary key (x, r);

select * from (
  select t2.*, 
         row_number() over (partition by t1.x order by d1, d2, d3) rn
  from   t1
  join   t2
  on     t1.x = t2.x
)
where  rn = 1;

         X          R D1          D2          D3                  RN
---------- ---------- ----------- ----------- ----------- ----------
         1          5 10-FEB-2016 09-FEB-2016 05-FEB-2016          1
         2          5 10-FEB-2016 08-FEB-2016 05-FEB-2016          1
... <snip> ...
        49          5 10-FEB-2016 23-DEC-2015 05-FEB-2016          1
        50          5 10-FEB-2016 22-DEC-2015 05-FEB-2016          1

 50 rows selected 

Depending on your data there may be more efficient ways to write this. 10 million rows is a lot though, so it's unlikely this query will be the fastest whichever method you use.

Rating

  (3 ratings)

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

Comments

Awesome solution as usual

Tauseef, February 10, 2016 - 9:36 am UTC

Thanks for your solution. I will try this and hope that it is faster than the normal function/stored procedure approach.

Also i will try to add index on t2 with the fields d1,d2,d3 so that it is faster.
Chris Saxon
February 10, 2016 - 11:18 am UTC

Thanks. You'll need to include t1_pk in your index on t2 for it to help.

Update the results in a table

Tauseef, February 10, 2016 - 11:01 am UTC

After getting the results now i need to update it in t1 using 1 update statement. IS it possible.

I get the below error for the update statements.

SQL Error: ORA-01733: virtual column not allowed here
01733. 00000 - "virtual column not allowed here"

update table1 t1
set t1.name = (select cm_al.newvalue from (
select t2.newvalue, t1.name, t2.uniqueid,row_number() over (partition by t2.uniqueid order by t2.effdate desc, t2.trandate desc, t2.trantime desc) rn
from table2 t2
where t1.custinfo_uniqueid=t2.uniqueid
and t2.FLDNAME='NAME'
) cm_al
where rn = 1);

update (
select cm_al.newvalue ne,cm_al.name ol,cm_al.uniqueid from (
select t2.newvalue, t1.name, t2.uniqueid,row_number() over (partition by t1.custinfo_uniqueid order by t2.effdate desc, t2.trandate desc, t2.trantime desc) rn
from table2 t2, table1 t1
where t1.custinfo_uniqueid=t2.uniqueid
and t2.FLDNAME='NAME'
) cm_al
where rn = 1)
set ol=ne;
Chris Saxon
February 10, 2016 - 11:38 am UTC

Yep, you can do this in a single update. In 11g Oracle can't reference t1.custinfo_uniqueid inside the subquery. You could join t1 there, or, as you don't appear to need t1 values for row_number(), join outside the subquery.

Note - if there's no matching t2 row, you'll set t1.name to null. I'm guessing this isn't what you want. If not, you'll need to add an existence check.

create table t1 (x primary key, y ) as
  select rownum x, 'oldstuff' y from dual connect by level <= 100;
  
create table t2 as
  with rws as (
    select rownum r from dual connect by level <= 5
  )
  select x, r, sysdate d1, sysdate-x d2, sysdate-r d3, 'newstuff' nw from t1, rws
  where  x <= 50;

alter table t2 add constraint pk primary key (x, r);

update t1
set    y = (
  select nw from  (
    select t2.*, 
           row_number() over (partition by t2.x order by d1, d2, d3) rn 
    from   t2 
  ) t2
  where  rn = 1
  and    t2.x = t1.x
)
where  exists (
  select * from t2
  where  t2.x = t1.x
);

50 rows updated.

merge !

Rajeshwaran, Jeyabal, February 10, 2016 - 11:50 am UTC

or perhaps a merge.

rajesh@ORA10G> merge into t1 using
  2     (       select x,nw
  3             from (
  4             select t2.*,
  5                     row_number() over (partition by t2.x order by d1, d2, d3) rn
  6                     from t2
  7                       )
  8             where rn = 1 ) t2
  9  on (t1.x = t2.x)
 10  when matched then
 11      update set t1.y = t2.nw
 12  /

50 rows merged.

rajesh@ORA10G> 

Connor McDonald
February 10, 2016 - 12:13 pm UTC

Just an addenda for Tauseef.

When you need to update 'n' rows, its always worth seeing what percentage of the entire table that 'n' is.

If 'n' is a tiny fraction of the table... then go ahead and run that update statement.

If 'n' is almost *all* of the table... then its probably going to be a lot faster to simply create a *new* table with the results you want.

Cheers,
Connor

More to Explore

Analytics

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