[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Request an efficient and scalable query for merging big tables

Posted on 2011-02-17
9
Medium Priority
?
353 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
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.

649 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