Archives of the TeradataForum

Message Posted: Thu, 29 Jan 2004 @ 12:07:30 GMT

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

Jon,

Regarding:

From what I learned from my last post, a view will NOT use indexes or statistics IF it is set up any differently than just CREATE VIEW AS SELECT * FROM MY_TABLE" Can someone please explain this ?

I believe the confusion lies in the type of view referencing a base table. For instance, if I have a single table view which is performing an aggregation on multiple columns which do not make up the primary index, secondary index, or a multi-column statistic of the underlying base table, then the optimizer will have no confidence about the size of the resulting spool file (much like a derived table via V2R5.) Once this occurs, the possibility exists that the resulting spool file could be mis-handled by the optimizer (due to a lack of statistics about its true size) if the respective spool file is part of a larger, multi-join complex query. This mishandling could result in poor query performance.

I agree with you, the wording of the posting is wrong if taken out of context of the associated thread.

I would be nice; however, if you could elaborate on how the optimizer makes use of stats and indexes for those objects associated with views which perform some sort of aggregation. I'm fuzzy about that.

Thanks,

Clay

Claybourne L. Barrineau
Senior Application DBA
Coca-Cola Enterprises, Inc.

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