?
Solved

Request an efficient and scalable query for merging big tables

Posted on 2011-02-17
9
Medium Priority
?
352 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 960 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 1040 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

Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

800 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