Solved

Request an efficient and scalable query for merging big tables

Posted on 2011-02-17
9
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 77

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 41

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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 41

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
 
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 77

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 77

Expert Comment

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

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

724 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