SQL query optimization

[arzoocbe]

Hi,

I have 3 tables
Table A with 100 rows,
Table B with 200 rows,
Table C with 100000 rows.I have a query joining these 3 tables.The Statistics collected on these tables are CURRENT STATISTICS.when I run an Explain on the query,The optimizer redistributes the 100000 rows in Table C(Redistribution consumes huge spool space).

Is it a bug in the Optimizer (or)
Can the optimizer Redistribute the table with less number of rows ?. IF YES,what is tobe done..

Thanks in Advance
Arzoo

 

[dnoeth]

You can probably get help if you post the DDL for the tables, the query and the explain...

Dieter

 

[geppetto]

Arzoo,
Could you also provide the SQL that you are using?
geppetto

 

[RanD]

As you can deduce from the responses, depending on your table structure (specifically the indices on the tables and the implicit relations between the tables) and you query, the optimizer develops the most efficient plan.

If you structure the tables properly and the relationship properly the optimizer should elect to redistribute the smaller table of the pair it will join.

Teradata always joins two tables at the time, (with the results going to spool/pseudo table) if more than 2 tables are to be joined.

Based on the results you are getting, there is something you need to do in the indices, the query, or both to get the optimizer to give you the efficient plan.

Regards

Randy Volters
Certified Teradata Master
Class of '01

 

[vijayvg]

try joining A & B (even a cross join would be fine) as a temp table and then join C on index.. (if you do join on index)

sel *
from c,
(sel a.x, b.x
from a, b
group by 1,2) a (x,y)
where a.x = c.x
and a.y = c.y
;
Still Randy's answer applies as to the index selection etc..