RE: Index Advisor and MTI's.

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

MTIs are created when query optimizer thinks benefit of creating an index
outweighs the cost of building and maintaining one.  This usually means
'small' tables that are frequently queried with similar/identical statements
(there's more to it but let's not get bogged with details we can't affect in
any way). They are not built over large files or files where update activity
dominates read activity.

You get the benefit of creating an index immediately, you do not have to
wait for an IPL.

System Index Advisor advises not only MTIs but full indexes you should build
over large files as well, including SQE and CQE advice.  So, it really is a
full set of index advice.  Now which of the many recommended indexes will
help your critical processing... you, Mr. DBA, have to make that connection.

As for programmers taking efficiency into account when designing solutions -
of course they should.  But I've seen many times where they can't due to
separation of duties (i.e. they're not allowed on production box), or they
just don't know enough about database design (i.e. an SQL join must have an
index over join columns; if it doesn't you get a Cartesian Product join and
can complete a vacation before your query finishes).  Or they put in an
ORDER BY on the query where they really don't need one.  Or they just don't
know there is a problem with the statement.
This is where part time DBA comes in and AT THE LEAST is able to point out
to the programmer "this statement is performing poorly - fix it".

HTH, Elvis


Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
 
-----Original Message-----
Subject: Index Advisor and MTI's.

Hi All,

      Possibly a stupid question or have miss-read overview of index
advisor?

 

      If MTI's are created, after several indications that an index is
required?

 

      Do you only get the benefit of creating a permanent index after an
IPL?

 

      Trying deduce how much benefit there is taking the advice from index
advisor.

 

      Any constructive input gratefully received.

 

      Believe that programmers should take into account efficiency when
creating solutions.

 

Kind Regards

 

Ian