Archives of the TeradataForum

Message Posted: Wed, 21 Feb 2007 @ 08:59:57 GMT

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

Hi,

Need some help on SQL query.

In a query we have a select from a view.

The view has been defined using UNION ALL from 2 tables one is the current table having data related to current month and another its archive which has all the past data. The tables have been partitioned on date.

The query throws a spool error and never comes back even after allotting lots and lots of spool.

If the view is replaced by its base tables the result is quicker.

In the explain we see rowhash being used when we use the base table but in the explain when we use the view it show synscronised scanning and the time estimate is also much higher.

Another trivia is it shows one unknown table with the tableid (hex) rather than the table name.

My questions:

Does UNION ALL from views spools ou always? What exactly happens in the optimizer?

What is the solution for such problem?

Why does the explain show that single new internal object tableid (hex) rather than the table name?

Please share your views.

With anticipation and tonnes of thanks.

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