Cartesian join - Tuning

[ananthkt_sat]

Hi all,
In the following structure i have a query.

select * from (select ...) A,(select...) B

While executing the row sources A & B individually it takes some seconds to execute.
Output of will be around 40 rows & output of B will be around 8000 rows. I want the cartesian of these two & i writen the above mentioned query & it takes 30 minutes to execute.

How to reduce the timing ?

Is there any specific SQL tuning options are available for Cartisian Product.

Please update me.

Thanks & Regards
Ananth K.T

[reydp]

can you post your SQL statement, table description, indexes of 2 tables?

[abhaysk]

can u post the execution plan ..?

[tamilselvan]

HASH join on the smaller table may improve performance.
Try this code:

select /*+ ordered use_hash(A) */ *
from
( select /*+ no_merge */ ...) B,
( select /*+ no_merge */ ...) A ;

Tamil