Go Premium for a chance to win a PS4. Enter to Win

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

Full outer in Oracle 8i

How do i implement FULL outer join i.e get all rows from both the tables..in Oracle 8i.
i know an option of union to get this...but union slows down the query v.much
0
koolsyed
Asked:
koolsyed
  • 7
  • 3
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
unfortunately, UNION (resp UNION ALL) is the only method in Oracle 8i...
0
 
koolsyedAuthor Commented:
union hitting the performance pretty badly..
not sure..how to handle dis.
0
 
koolsyedAuthor Commented:
I have 2 tables
Table1
A
B
D
Table2
A
B
C

My full outer join shud give me A,B,C,D

Now that 8i doesnt support the full outer without union,js wondering if we have something like temp tables that we use in sql server...

I can just put distinct elements from table1 and table2...into a table and update rest of the cols.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
koolsyedAuthor Commented:
updated the points..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can use TEMPORARY or normal table for intermediate results, but they are not likely to give better results than the UNION query. now, how many rows do you return?
0
 
koolsyedAuthor Commented:
My distinct elements would range somewhere arnd 200-2000.

I did a trail run by just removing extra joins and tested the union piece. it took 15 secs.
I guess its fair enuf as it runs in background as a job.
Any other thoughts wud be helpful.
0
 
sdstuberCommented:
what does your current query look like?
0
 
sdstuberCommented:
I just ran this on two tables with 69000 distinct elements in each and it took 594 milliseconds

SELECT   a, b, MAX(c) c, MAX(d) d
    FROM (SELECT a, b, c, NULL d
            FROM table1
          UNION ALL
          SELECT a, b, NULL c, d
            FROM table2)
GROUP BY a, b
0
 
koolsyedAuthor Commented:
---> Current query
select DISTINCT a,b,c,d
from table1
UNION
select DISTINCT a,b,c,d
from table2

-->Previous one which is with just one outer join...But i am tryin to convert this to full outer...
select a,b,c,d,sum(table1.e),sum(table1.f),sum(table1.g),sum(table1.h),sum(table2.l),sum(table2.m))
from table1,table2
where table1.key=table2.key(+)
group by a,b,c,d

In current query..i am trying to insert the distinct a,b,c,d and then update the cols for the aggregates.
0
 
koolsyedAuthor Commented:
my query returns arnd 200-2000 rows..but the aggregate is performed on 50k rows data set.
0
 
sdstuberCommented:
so, your tables look like this (maybe not correct types)?  

I'm testing with 50K to 70k in each table


CREATE TABLE TABLE1
(
  KEY NUMBER,
  A  NUMBER,
  B  NUMBER,
  C  NUMBER,
  D  NUMBER,
  E  NUMBER,
  F  NUMBER,
  G  NUMBER
)

CREATE TABLE TABLE1
(
  KEY NUMBER,
  A  NUMBER,
  B  NUMBER,
  C  NUMBER,
  D  NUMBER,
  L  NUMBER,
  M  NUMBER
)
0
 
koolsyedAuthor Commented:
i divided the logic into 2 diff procs..and its working quicker..thanks for the ideas..
0

Featured Post

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!

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