RE: DDL Discussion continued

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

SQL index's only purpose in life is to improve query performance.  While
with V5R4's introduction of MTIs (Maintained Temporary Indexes) you could
make a case that query optimizer will build some on the fly for you, truth
is that you can't count on that support alone to make your queries perform.
Only times you would not need to create indexes is if your tables are of
miniscule size so table scans are a better alternative anyway or you simply
don't care about the performance (i.e. it runs in some batch job and it
doesn't really matter when it finishes).
All other times you REALLY NEED indexes, since query optimizer uses them
both as a source of real-time statistics and more importantly for
alternative query optimization.

I am really hoping you meant to say 'do I need SQL views'... but that's a
totally different subject so I'll leave it be for now.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: DDL Discussion continued

   We had our meeting this past week on DDL and I would like to get some
   feedback from all of you....I know that some of the uses for a view is
far
   different than an index but if one is depending on the SQL engine to
   determine the most efficient access path for the data, why even create
   indexes?  Is there any real advantage to creating them?