I have a query with around 60 joins , the first four joins are inner and the rest all are left outer joins.
Can we improve the performance of the query by changing the order of the left outer joins used in the query.
Is there any rule which we should follow while putting these outer joins like based on the size of the tables or any other reasons.
In the Teradata pdf's, its mentioned that the execution will follow the order of the ON statements ,but also there is a note stating that teradata maynot go with the order of the joins as specified in the query
Hi, I haven't seen any standart steps that always help, but you can try to do following things: 1) Check statistics, migth be you can add something there. Try: diagnostic helpstats on for session; and after that check explain plan of your query and see the tips in the bottom of explain plan; 2) Check primary keys; 3) Use UNION (ALL)/INTERSECT instead of some INNER JOINs (can help veeery much especially with partitioned tables); 4) Use MINUS instead of LEFT JOIN table AS a ON ... WHERE a.ID IS NULL; 5) Use subselects and aggregate functions/DISTINCT to force optimizator to make the joins in other ways, for example: SELECT a.a, a.b, ... FROM tableA a JOIN tableB b ON a.ID = b.ID LEFT JOIN ( SELECT a1.Param1, a1.Param2, a2.Param1, SUM(c.AMT0 FROM .... GROUP BY 1,2,3 ) 6) Use hardcoded values or pass these values as parametres for partitioned tables.
You can reach VERY big differences with query rewrites in some times.