Solved

Full outer in Oracle 8i

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PAYER_ID has both atributes 4 37
Oracle and DateTime math 6 37
Performance issue with case statement in oracle 11G 7 60
Oracle dataguard 5 28
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

813 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

11 Experts available now in Live!

Get 1:1 Help Now