Archives of the TeradataForum

Message Posted: Fri, 27 Feb 2004 @ 22:46:20 GMT


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

Subj:		Re: Derived tables on V2R5

From:		Victor Sokovin

On Fri, 27 Feb 2004 16:25:18 -0500, Meade, Cornelius wrote:

I would have to say that without qualifying the "tool" in question that it depends....Derived tables certainly have had use in the past as a means to guide the RDBMS to what often was a more efficient execution plan in certain situations. Teradata is not unique in that vein and that really is a execution/performance question. Hopefully that type of use will be come a moot issue as the optimizer is made more intelligent per Fred's comments about NCR intentions in that area (and that will truly be a glorious day!). Beyond that however I think of "derived tables" from a SQL syntax/coding standpoint. In that vein, they serve many useful purposes and I think they have entered the SQL lexicon permanently and are here to stay....

Perhaps my comment was unclear. I see two aspects with derived tables:

1. SQL syntax form;

2. performance enhancement tool.

1. It looks like this new syntax form proved to be useful and it has been adopted by ANSI. In Oracle, derived tables are known as "inline views" and have been around for some time. SQL Server supports them too. So, as a syntax form these "unnamed views" are likely to stay. I agree with you here.

2. I think in the future database vendors won't promote derived tables as performance enhancement tools. They will become a normal part of SQL, just as views, subqueries, predicates etc. As with other SQL elements they may or may not improve performance. It will be more up to the optimizers whether to use derived tables internally or just completely rewrite the statement and forget about them.

Victor


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

Attachments

Library

Quick Reference