Home » SQL & PL/SQL » SQL & PL/SQL » Problem with FULL OUTER JOIN (Oracle 11g R1 64 bit on Oracle EL 5.0 x86_64)
Problem with FULL OUTER JOIN [message #314965] Fri, 18 April 2008 07:10 Go to next message
Mopper
Messages: 11
Registered: April 2008
Location: Belgium
Junior Member
Hi again.

I've ran into a problem that I don't really see a way aroud, so I decided to ask you guys for help.

This is the situation: I have 2 tables that are called covtype_small and selections. Covtype_small is a table that holds data on which certain data mining-algorithms are executed, and has one primary key column (called PKEY). Now, these algorithms are run in several iterations, and for each iteration, the selection of records to use from the data table is random. This is where the 'selections' table comes in: it has 2 columns, the first of which is a foreign key reference to the data table (covtype_small), and the second column holds the number of the iteration for which the records was chosen.
It can look something like this:
-----------------------
| FK_DATA | Iteration |
-----------------------
|  1      |  1        |
|  6      |  1        |
|  20     |  1        |
|  3      |  1        |
|  1      |  1        |
|  965    |  1        |
|  32     |  2        |
|  7      |  2        |
|  3658   |  2        |


It is possible (likely, even) that the same FK_DATA exists more than once for a given Iteration. It is also possible that a certain FK_DATA will exist for one iteration, but not for another one.

Now, what we need is the key of each data record, AND the iteration number for each data record that is NOT selected for a certain iteration number.

This is what I thought would do the trick:
SELECT d.pkey,
       s.fk_iter_key
FROM   covtype_small d
       FULL OUTER JOIN select_new s
         ON d.pkey = s.fk_data_key
WHERE  s.fk_data_key IS NULL ;

But, while it does give the correct keys for the data records, it does not tell me for which iteration that that records was not selected: s.fk_iter_key is always NULL since the record with that particular key doesn't exist.

I would be very grateful if any one of you could show me a way to get all the FK_DATA with the corresponding Iteration number for which it is not selected.

Thanks in advance.

[Updated on: Fri, 18 April 2008 07:14] by Moderator

Report message to a moderator

Re: Problem with FULL OUTER JOIN [message #314973 is a reply to message #314965] Fri, 18 April 2008 07:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Quote:
the key of each data record, AND the iteration number for each data record that is NOT selected for a certain iteration number

In other words, every data record with every iteration number, unless that combination exists in the selections table. Right?

SELECT d.pkey
FROM   covtype_small d
CROSS JOIN (
    SELECT distinct fk_iter_key
    FROM   select_new
) s
WHERE (d.pkey, s.fk_iter_key) NOT IN (
    SELECT fk_data_key, fk_iter_key
    FROM   select_new
)

To ensure that Oracle resolves the NOT IN as an anti-join rather than a filter, you will need NOT NULL constraints on both columns of select_new, and on the pkey column of covtype_small.

Run it through Explain Plan first to make sure. Look for a HASH JOIN (ANTI) step. If you see a FILTER, don't run it cos it won't finish.

Ross Leishman
Re: Problem with FULL OUTER JOIN [message #314979 is a reply to message #314973] Fri, 18 April 2008 08:01 Go to previous messageGo to next message
Mopper
Messages: 11
Registered: April 2008
Location: Belgium
Junior Member
Thanks a lot man, that's exactly what I needed. You were also right about the NOT NULL constraints, it didn't do the anti-join unless the columns you mentioned were not nullable.

Thanks!
Re: Problem with FULL OUTER JOIN [message #314985 is a reply to message #314965] Fri, 18 April 2008 08:05 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I see that Ross answered this while I was working on it, but I'll post mine anyway, because I already did it, and because it shows that there are often different ways to solve a problem.

Output:

MYDBA@orcl > start iterations;

Table created.


Table created.


        ID
----------
        10
        20
        30
        40
        50
        60
        70
        80
        90

9 rows selected.


        ID        RUN
---------- ----------
        10          1
        10          2
        10          3
        10          4
        10          5
        20          1
        20          3
        20          5
        40          2
        40          4
        50          1
        50          1
        60          2
        60          2
        70          4
        70          4
        70          5
        70          5
        80          1
        80          1
        80          2
        80          3

22 rows selected.


Procedure created.

No errors.

PL/SQL procedure successfully completed.


       RUN         ID
---------- ----------
         1         30
         1         40
         1         60
         1         70
         1         90

5 rows selected.


PL/SQL procedure successfully completed.


       RUN         ID
---------- ----------
         2         20
         2         30
         2         50
         2         70
         2         90

5 rows selected.


PL/SQL procedure successfully completed.


       RUN         ID
---------- ----------
         3         30
         3         40
         3         50
         3         60
         3         70
         3         90

6 rows selected.


PL/SQL procedure successfully completed.


       RUN         ID
---------- ----------
         4         20
         4         30
         4         50
         4         60
         4         80
         4         90

6 rows selected.


PL/SQL procedure successfully completed.


       RUN         ID
---------- ----------
         5         30
         5         40
         5         50
         5         60
         5         80
         5         90

6 rows selected.


Procedure dropped.


Table dropped.


Table dropped.


Script:


create table iterations (id number, run number);

set feedback off
insert into iterations values (10, 1);
insert into iterations values (10, 2);
insert into iterations values (10, 3);
insert into iterations values (10, 4);
insert into iterations values (10, 5);
insert into iterations values (20, 1);
insert into iterations values (20, 3);
insert into iterations values (20, 5);
insert into iterations values (40, 2);
insert into iterations values (40, 4);
insert into iterations values (50, 1);
insert into iterations values (50, 1);
insert into iterations values (60, 2);
insert into iterations values (60, 2);
insert into iterations values (70, 4);
insert into iterations values (70, 4);
insert into iterations values (70, 5);
insert into iterations values (70, 5);
insert into iterations values (80, 1);
insert into iterations values (80, 1);
insert into iterations values (80, 2);
insert into iterations values (80, 3);
set feedback on

create table data (id) as select rownum*10 from all_objects where rownum < 10;


select * from data order by id;
select * from iterations order by id, run;

create procedure proc(rc in out sys_refcursor, p_run in number)
is
begin
	open rc for
	select p_run run, id
	from data
	minus
	select distinct run, id
	from iterations;
end;
/
show errors

variable mycur refcursor
exec proc(:mycur, 1);
print mycur
exec proc(:mycur, 2);
print mycur
exec proc(:mycur, 3);
print mycur
exec proc(:mycur, 4);
print mycur
exec proc(:mycur, 5);
print mycur

drop procedure proc;
drop table data;
drop table iterations;

Re: Problem with FULL OUTER JOIN [message #314995 is a reply to message #314965] Fri, 18 April 2008 08:45 Go to previous message
Mopper
Messages: 11
Registered: April 2008
Location: Belgium
Junior Member
Ross did indeed beat you to it, but thank you anyway. All useful input is always appreciated Smile
Previous Topic: How to aviod usage of cursor and improve performance
Next Topic: What function to use to round character?
Goto Forum:
  


Current Time: Sun Dec 11 02:23:41 CST 2016

Total time taken to generate the page: 0.04634 seconds