Joining on secondary indices

[Terafic]

I am trying to join two tables, 3 million and 21 million rows on the secondary indices of both tables. I keep running out of spool space.
Looking at the EXAPLIN, I see both tables go into spool before the join. Is there any way to force the smaller table into spool (and redistribute) while not doing so to the other?

I tried making the smaller table into a drived-tables, but still had no luck.

thanks

 

[scarletAni]

Since the distribution of datais on the Primary Index, the join on Sec Index will cause the spool to be created.
May be you can create global temp table with the sec Index columns are the Primary Index and then try to join.

 

[dnoeth]

Teradata is not using Secondary Indexes for joins in most cases (unless it's a Nested Join).
Do you have statitics on both SIs?
The optimizer might choose a different plan with statistics, if not ask your dba for more spool ;-)

Dieter