| cartesian product????? [message #246092] |
Tue, 19 June 2007 21:47  |
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 
|
[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  |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
