Doru Constantin wrote:

> I ask for your help on this join problem!
> Thank you in advance.
>
> TABLE t1
> *--*----*----*
> |pk|col1|col2|
> *--*----*----*
> |1 |a |a...|
> |2 |b |b...|
> |3 |c |c...|
> |4 |d |d...|
> |5 |e |e...|
> |6 |f |f...|
> |.... |
> *--*----*----*
> pk pk
> index col1
>
> TABEL t2
> *--*----*----*-----*
> |pk|col1|col2|col3 |
> *--*----*----*-----*
> |1 |a |b |1,000|
> |2 |c |a |2,000|
> |3 |a |c |3,000|
> |4 |d |e |4,000|
> |5 |b |b |5,000|
> |6 |c |e |6,000|
> |7 |e |f |7,000|
> |.... |
> *--*----*----*-----*
> pk pk
> index col1
> index col2
>
> THE RESULT (should be):
> *-------*-------*------------*------------*
> |t1.col1|t1.col2|sum(t2.col3)|sum(t2.col3)|
> *-------*-------*------------*------------*
> |a |a... | 4,000| 2,000|
> |b |b... | 5,000| 6,000|
> |c |c... | 8,000| 3,000|
> |d |d... | 4,000| 0|
> |e |e... | 7,000| 10,000|
> |f |f... | 0| 7,000|
> *-------*-------*------------*------------*
>
> I've try this:
> SELECT
> x.col1,
> x.col2,
> sum(y1.col3) as col3,
> sum(y2.col3) as col4
> FROM
> t1 x
> LEFT JOIN t2 y1 ON y1.col1 = x.col1
> LEFT JOIN t2 y2 ON y2.col2 = x.col1
> GROUP BY 1,2
>
> but with a real big t1 and t2 tables it take forever...!?
> </doru>

... the plan returned is:
PLAN SORT (JOIN (JOIN (X NATURAL,Y1 INDEX (IDX_T2_COL1)),Y2 INDEX
(IDX_T2_COL3)))