Solved

Full outer in Oracle 8i

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

 

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

Industry Leaders: 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!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

735 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