Home » SQL & PL/SQL » SQL & PL/SQL » cartesian join with correlated queries (db: 9, sqlplus 10)
cartesian join with correlated queries [message #361900] Fri, 28 November 2008 08:40 Go to next message
jablona
Messages: 5
Registered: November 2008
Location: Dublin
Junior Member
Hi everyone,

I have a question about speed of cartesian join with correlated query:

Let's have:
1. A table 'T1' with two columns 'F1' and 'F2'.
2. A select query Q(T1.F2) that fetches one colum of data with 0..* rows. This query takes a T1.F2 field value as a parameter. The query Q(T1.F2) takes a form of union of two subqueries SQ1(T1.F2) and SQ2(T1.F2). So Q(T1.F2) = SQ1(T1.F2) UNION SQ2(T1.F2).
3. A nested table datatype 'table_datatype' which is of one column named 'FIELD'
The task is to construct a query that will result in a two column table being a cartesian product of T1.F1 and Q(T1.F2).

Approach 1:
select T1.F1, TEMP.FIELD FROM T1, table(cast(multiset(SQ1(T1.F2)) as table_datatype)) TEMP
UNION
select T1.F1, TEMP.FIELD FROM T1, table(cast(multiset(SQ2(T1.F2)) as table_datatype)) TEMP

This ends with "no more data to read from socket" error message. I searched through net and couldn't find any remedy for this.

Approach 2:
Differs from Approach 1 only in that the UNION operator is replaced with UNION ALL operator.

This works, works really fast, but the problem is that it may result in duplicates of rows in resulting table (since SQ1(T1.F2) and SQ2(T1.F2) may fetch the same data) which I cannot accept.

Approach 3:
This introduces an sql function which only function is to merge two nested tables of table_datatype type. Unfortunately since the db is version 9 I cannot use blessings of multiset union operator. Let's call the function 'FOO'

select DISTINCT
T1.F1, TEMP.FIELD FROM T1,
table(FOO(cast(multiset(SQ1(T1.F2)) as table_datatype), cast(multiset(SQ2(T1.F2)) as table_datatype))) TEMP

Now this works, brings the expected results, but is really slow, it takes about 5 minutes for the query to execute Sad.

The ammount of data that is processed is not huge at all. Number of rows in the T1 tables is around 3000. The approach 2 version executes really fast and the number of subqueries executions is exacly the same between approach 2 and 3.


So my question
1. Is there anything totally wrong with my approach 3? Is this because of calling sqlfunction that the execution time is that long?
2. If answer for 1 is yes then what is an appropriate approach (Note that approach 1 eventhough it seems too me perfect gives me this strange error I can't deal with)


Thanks everyone who got through my attempt to be as clear as possible Smile.

Artur

[EDITED by LF: removed superfluous blank lines]

[Updated on: Wed, 03 December 2008 15:13] by Moderator

Report message to a moderator

Re: cartesian join with correlated queries [message #361902 is a reply to message #361900] Fri, 28 November 2008 09:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Introducing a funtion will slow things down. Google for 'Context Switching Pl/Sql'

How well does this work:

SELECT distinct F1,FIELD
FROM (select T1.F1, TEMP.FIELD FROM T1, table(cast(multiset(SQ1(T1.F2)) as table_datatype)) TEMP
      UNION ALL
      select T1.F1, TEMP.FIELD FROM T1, table(cast(multiset(SQ2(T1.F2)) as table_datatype)) TEMP)
Re: cartesian join with correlated queries [message #361911 is a reply to message #361902] Fri, 28 November 2008 09:33 Go to previous messageGo to next message
jablona
Messages: 5
Registered: November 2008
Location: Dublin
Junior Member
Hey there, thanks for your answer.

Yeah, I understand the slow down resulting from context switching, I was just wondering if my slow query can be 100% explained by this factor.

Anyways
Thanks for your suggested query. I must admin I didn't try it. It ends for me as my Approach 1. So "no more data to read from socket" this is message when issuing sql through jdbc, and sqlplus shouts "end-of-file on communication channel". So it seems that for some strange reason the connection is dropped. After this I actually have to reconnect to the db to issue any command.
Re: cartesian join with correlated queries [message #362510 is a reply to message #361900] Tue, 02 December 2008 09:06 Go to previous messageGo to next message
jablona
Messages: 5
Registered: November 2008
Location: Dublin
Junior Member
Hi...
I've just come accross "no formatting, no answer" Smile, so this is just an original post with code formattin added

Hi everyone,

I have a question about speed of cartesian join with correlated query:

Let's have:
1. A table 'T1' with two columns 'F1' and 'F2'.
2. A select query Q(T1.F2) that fetches one colum of data with 0..* rows. This query takes a T1.F2 field value as a parameter.
The query Q(T1.F2) takes a form of union of two subqueries SQ1(T1.F2) and SQ2(T1.F2). 
So Q(T1.F2) = SQ1(T1.F2) UNION SQ2(T1.F2).
3. A nested table datatype 'table_datatype' which is of one column named 'FIELD'


The task is to construct a query that will result in a two column table being a cartesian product of T1.F1 and Q(T1.F2).

Approach 1:
select T1.F1, TEMP.FIELD FROM T1, table(cast(multiset(SQ1(T1.F2)) as table_datatype)) TEMP
UNION
select T1.F1, TEMP.FIELD FROM T1, table(cast(multiset(SQ2(T1.F2)) as table_datatype)) TEMP

This ends with "no more data to read from socket" error message. I searched through net and couldn't find any remedy for this.

Approach 2:
Differs from Approach 1 only in that the UNION operator is replaced with UNION ALL operator.

This works, works really fast, but the problem is that it may result in duplicates of rows in resulting table (since SQ1(T1.F2) and SQ2(T1.F2) may fetch the same data) which I cannot accept.

Approach 3:
This introduces an sql function which only function is to merge two nested tables of table_datatype type. Unfortunately since the db is version 9 I cannot use blessings of multiset union operator. Let's call the function 'FOO'

select DISTINCT
T1.F1, TEMP.FIELD FROM T1,
table(FOO(cast(multiset(SQ1(T1.F2)) as table_datatype), cast(multiset(SQ2(T1.F2)) as table_datatype))) TEMP


Now this works, brings the expected results, but is really slow, it takes about 5 minutes for the query to execute Sad.

The ammount of data that is processed is not huge at all. Number of rows in the T1 tables is around 3000. The approach 2 version executes really fast and the number of subqueries executions is exacly the same between approach 2 and 3.


So my question
1. Is there anything totally wrong with my approach 3? Is this because of calling sqlfunction that the execution time is that long?
2. If answer for 1 is yes then what is an appropriate approach (Note that approach 1 eventhough it seems too me perfect gives me this strange error I can't deal with)


Thanks everyone who got through my attempt to be as clear as possible Smile.

Artur


Re: cartesian join with correlated queries [message #362628 is a reply to message #361911] Wed, 03 December 2008 07:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you copy andpaster the error stack that SQL*Plus gives you when you run your version of the query?
Re: cartesian join with correlated queries [message #364419 is a reply to message #362628] Mon, 08 December 2008 05:59 Go to previous messageGo to next message
jablona
Messages: 5
Registered: November 2008
Location: Dublin
Junior Member
Hi
Sorry for late response I had some problems accessing the orafaq page last week.

Yes so this is what sqlplus throws back when using my Approach 1 query as well as the one you suggested in your response.

SELECT
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


This is not returned immediately but after around 3mins. I don't know what this SELECT * is supposed to be as there isn't such part of statement in my query. So this confuses me a bit.
Re: cartesian join with correlated queries [message #364423 is a reply to message #364419] Mon, 08 December 2008 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

Regards
Michel
Re: cartesian join with correlated queries [message #364431 is a reply to message #364423] Mon, 08 December 2008 07:45 Go to previous message
jablona
Messages: 5
Registered: November 2008
Location: Dublin
Junior Member
is this so? Phew ;]
Ok, thanks for your reply. I'll seek support then.
Previous Topic: Problem with query having cast function (merged)
Next Topic: scrambling production data
Goto Forum:
  


Current Time: Wed Dec 07 09:07:18 CST 2016

Total time taken to generate the page: 0.07076 seconds