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.
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
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
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?
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact