Solved

Full outer in Oracle 8i

Posted on 2007-12-06
12
1,371 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 142

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
 

Author Comment

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

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
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.

 
LVL 73

Expert Comment

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

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 73

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Space Delimited Sql File 4 70
Format Number Field 10 39
compre toata in where clue oracle 4 41
EXECUTE IMMEDIATE 5 34
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now