Solved

Full outer in Oracle 8i

Posted on 2007-12-06
12
1,377 Views
Last Modified: 2013-12-19
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
Comment
Question by:koolsyed
  • 7
  • 3
  • 2
12 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 300 total points
ID: 20418755
unfortunately, UNION (resp UNION ALL) is the only method in Oracle 8i...
0
 

Author Comment

by:koolsyed
ID: 20418773
union hitting the performance pretty badly..
not sure..how to handle dis.
0
 

Author Comment

by:koolsyed
ID: 20418903
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:koolsyed
ID: 20418930
updated the points..
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 20419330
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
 

Author Comment

by:koolsyed
ID: 20419525
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 20419607
what does your current query look like?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 20419677
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
 

Author Comment

by:koolsyed
ID: 20419709
---> 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
 

Author Comment

by:koolsyed
ID: 20419731
my query returns arnd 200-2000 rows..but the aggregate is performed on 50k rows data set.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20419800
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
 

Author Comment

by:koolsyed
ID: 20425907
i divided the logic into 2 diff procs..and its working quicker..thanks for the ideas..
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

838 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