Home » SQL & PL/SQL » SQL & PL/SQL » cartesian product?????
cartesian product????? [message #246092] Tue, 19 June 2007 21:47 Go to next message
lorco
Messages: 10
Registered: October 2005
Junior Member
Hello
I have done one request to resolve a small problem but I'm sure the performance will be horrible with a lot of data, can you help me

CREATE TABLE TOTO (  KEY  NUMBER);
insert into TOTO (KEY) values (1);
insert into TOTO (KEY) values (2);
insert into TOTO (KEY) values (3);
    

CREATE TABLE TOTO_LINKEDTO(
  KEY              NUMBER,
  TOTO_FK          NUMBER,
  TOTO_RELATED_FK  NUMBER
);
insert into TOTO_LINKEDTO (KEY,TOTO_FK, TOTO_RELATED_FK) 
	values (1, 1, 2);
insert into TOTO_LINKEDTO (KEY,TOTO_FK, TOTO_RELATED_FK) 
	values (2, 2, 3); 
    
commit;
    
    
    
    
select * from 
 (select a.KEY as father, a2.KEY as child
   from toto a, toto a2) t1, 
 (select * from TOTO_LINKEDTO ar)t2
where t1.father = t2.toto_fk (+)
    and t1.father = 1
    and t1.child = t2.toto_related_fk(+


So I will have the next results

FATHER  CHILD KEY TOTO_FK TOTO_RELATED_FK
  1       1                   
  1       2    1      1        2 
  1       3                   


Can some help me??

Thanks a lot


Quote:
Updated the query after rleishman comment Embarassed

[Updated on: Tue, 19 June 2007 22:07]

Report message to a moderator

Re: cartesian product????? [message #246096 is a reply to message #246092] Tue, 19 June 2007 21:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are selecting the entire TOTO table to get every possible row as FATHER, but then restricting it to a single row with t1.father = 3 (I think you meant "1"). Cartesian joining a single row to another table is not expensive. Oracle will push the WHERE condition into the nested view to ensure that only a single row is selected.

This would also work:

select * from 
 (select a.KEY as father, a2.KEY as child
   from (select 1 as key from dual) a, toto a2) t1, 
 (select * from TOTO_LINKEDTO ar)t2
where t1.father = t2.toto_fk (+)
    and t1.child = t2.toto_related_fk(+)


Ross Leishman
Re: cartesian product????? [message #246099 is a reply to message #246096] Tue, 19 June 2007 22:16 Go to previous message
lorco
Messages: 10
Registered: October 2005
Junior Member
Yes, thank you!!!!!!!
Previous Topic: How indexes improve performace?
Next Topic: how to directly pass xml value to clob variable
Goto Forum:
  


Current Time: Tue Dec 06 04:42:53 CST 2016

Total time taken to generate the page: 0.10036 seconds