Archives of the TeradataForum

Message Posted: Wed, 28 Jan 2009 @ 16:02:00 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This can become a rather involved discussion but to keep it at the same high level you presented it here are some initial suggestions and thoughts:

Have you used the optimizer directive DIAGNOSTIC HELPSTATS ON FOR SESSION and then running an explain of the query (queries) in question to see what additional statistics would help the optimizer choose a less expensive query plan. It is likely going to recommend stats that do not exist on columns participating in joins, where and qualify clauses.

Are you deriving (calculated) columns in the views whose results are later used as qualifications? For example, using a CASE statement to derive a value that is subsequently used to restrict the resultset? The optimizer will have no confidence on the these derived columns. Thus they can't be used earlier in the plan to restrict what may otherwise be an unwieldy spool file and this large spool file may end up being used throughout the query plan.

Have you considered the primary indexes of the tables in the views. Does the physical design of the tables allow moderately sized or larger tables to join on the same primary index (amp local) without having to be redistributed?

After the stats have been collected if the views are still not meeting your SLA for performance are there any additional secondary or join indexes that can be collected? If the tables are sufficiently large enough can you apply a partition primary index?

Hope this helps.

	<Prev	Next>		<<First	<Prev	Next>	Last>>