Solved

Request an efficient and scalable query for merging big tables

Posted on 2011-02-17
9
346 Views
Last Modified: 2012-05-11
table T1 (ID number(5), Name varchar2(30), FID number(10), NPK1 char(2), NPK2 systimestep )
table T2 (ID number(5), Name varchar2(30), FID number(10), NPK1 char(2), NPK2 systimestep )
(ID, Name)  are PKs.

T1 contains
ID   Name  FID NPK1    NPK2
10   Bill      2   A         11-FEB-11 08.11.41
30   Jeff    5  AA       11-FEB-11 08.11.43
30   Scott  5   B          11-FEB-11 08.11.31
40   Scott  10 A          10-FEB-11 08.11.41
50  Alice   30  C        10-FEB-11 07.11.50
...
T2 contains
ID   Name  FID NPK1  NPK2
15   Bill      2   A         10-FEB-11 08.11.41
30   Jeff    5  AA       11-FEB-11 08.11.43
30   Scott  5  B          11-FEB-11 08.11.31
40   Scott 10  D         10-FEB-11 08.11.49
50  Alice   20  C        10-FEB-11 07.11.42
...
Would like to get:
ID   Name  FID NPK1  NPK2                              (Does not count --just comments)
10   Bill      2   A         11-FEB-11 08.11.41      --(from T1)
15   Bill      2   A         10-FEB-11 08.11.41      -- ( from T2)
30   Jeff    5  AA       11-FEB-11 08.11.43      --(common)
30   Scott  5  B          11-FEB-11 08.11.31      --(common)
40   Scott 10  D         10-FEB-11 08.11.49      --(taking from T2)
50  Alice   30  C        10-FEB-11 07.11.50       --(taking from T1)

The rules are:
1) if the records are in T1 but not in T2, take them, vice versa.
2) If they have the same composite PK, compare the column NPK2. keep the larger one.
3) if the records from T1 and T2 are the same, keep one
Since the tables are big and there are a lot columns , the efficiency and scability should be taken into consideration.

Greatly appreciate the guru's tips/codes
0
Comment
Question by:jl66
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34922655
Why the related question?  I'm not sure what else we can provide and what you are looking for.

You should take the examples from the other question and run them against your test system to see which ones perform better.

If you have a specific question about how to get it running faster, we will need the plans/tkprof/setup of your environment.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34923116
Are you looking for UNION?

select * from T1 UNION  select * from T2
0
 

Author Comment

by:jl66
ID: 34923222
slightwv: It's related in term of Angellll's solution. ID was used "partition by" in the previous question, which is very powerful, but can it be used here?  May not be.

Sharath_123: Sorry I forgot to mention the same PK record only shows once in the result.
After UNION,
40   Scott 10  D         10-FEB-11 08.11.49 (from T2)
and
40   Scott  10 A          10-FEB-11 08.11.41 (from T1)

they will both show in the result. Actually I only need one:
40   Scott 10  D         10-FEB-11 08.11.49

 
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 240 total points
ID: 34923282
try like this.
SELECT t2.* 
  FROM (SELECT t1.*, 
               ROW_NUMBER() 
                 OVER(PARTITION BY t1.ID ) rn 
          FROM (SELECT * 
                  FROM T1 
                UNION 
                SELECT * 
                  FROM T2) t1) t2 
 WHERE t2.rn = 1

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34923751
hi

i wild approach this with pl/sql using a cursor for update

here is a sample :

create table f (a number, b varchar2(10));

insert into f values (5,'five');
insert into f values (6,'six');
insert into f values (7,'seven');
insert into f values (8,'eight');
insert into f values (9,'nine');

commit;

create or replace procedure wco as
  cursor c_f is
    select a,b from f where length(b) = 5 for update;
    v_a f.a%type;
    v_b f.b%type;
begin
  open c_f;
  loop
    fetch c_f into v_a, v_b;
    exit when c_f%notfound;
    update f set a=v_a*v_a where current of c_f;
  end loop;

  close c_f;
end;
/

exec wco;

select * from f;

drop table f;
drop procedure wco;
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 260 total points
ID: 34925751
>>ID was used "partition by" in the previous

If I unserstand this correctly:  

Just add the NPK2 column to the ORDER BY clause of the analytic function:

since you didn't post what you have now, here's a mix of the SQL from the other question and this question:

select ID, FID
  from ( select sq.*, row_number() over ( partition by ID order by FID desc, NPK2 desc) rn
              from ( select ID, FID from table1 union all select ID, FID from table2 ) sq
        ) q
 where q.RN = 1  
0
 

Author Comment

by:jl66
ID: 34927319
Thanks for the inputs. The issue was solved partly with the tips from most of you.
0
 

Author Closing Comment

by:jl66
ID: 34927358
Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927359
What piece isn't working?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now