Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Request an efficient and scalable query for merging big tables

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
jl66
Asked:
jl66
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
SharathData EngineerCommented:
Are you looking for UNION?

select * from T1 UNION  select * from T2
0
 
jl66Author Commented:
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
Independent Software Vendors: 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!

 
SharathData EngineerCommented:
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
 
Aaron ShiloChief Database ArchitectCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
jl66Author Commented:
Thanks for the inputs. The issue was solved partly with the tips from most of you.
0
 
jl66Author Commented:
Thanks
0
 
slightwv (䄆 Netminder) Commented:
What piece isn't working?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now