Re: Index Advisor and MTI's.

[rob@xxxxxxxxx]

Had to lookup what an "MTI" was.  Found a reference to Maintained 
Temporary Indexes here:
http://archive.midrange.com/midrange-l/200703/msg01182.html

I am not sure what spawns their (MTI) creation.  So I can't answer your 
second question.

A permanent index should be used right away and not have to wait for IPL.

Here's your proof:

I created a procedure by running RUNSQLSTM against this source member:
***** Start of source
CREATE PROCEDURE ROB.TEST
  LANGUAGE SQL
  BEGIN

DECLARE COUNTER INT DEFAULT 0;

CREATE TABLE ROB.BISMUTH (MYKEY INT , THENBR DEC (15 , 5));

WHILE COUNTER < 10000 DO
  INSERT INTO ROB.BISMUTH (THENBR) VALUES(rand(1)*100);
  SET COUNTER = COUNTER + 1;
END WHILE;

create table rob.bismuth1 as
(select * from rob.bismuth
 order by mykey)
with data;

create table rob.bismuth2 as
(select * from rob.bismuth
 order by thenbr)
 with data;

CREATE INDEX ROB.bismutha ON ROB.BISMUTH (MYKEY ASC);

CREATE INDEX ROB.bismuthb ON ROB.BISMUTH (thenbr ASC);

create table rob.bismuth3 as
(select * from rob.bismuth
 order by mykey)
with data;

create table rob.bismuth4 as
(select * from rob.bismuth
 order by thenbr)
 with data;

END 
***** End of source

Then I went into Run SQL Scripts and ran it by CALL ROB.TEST;
When it created table 1 and 2 it advised the indexes later created.  When 
it created 3 and 4 it did not call for any indexes.

Granted, it didn't use them, all four tables used full table scans 
(according to VE).

I modified the source to the following:
***** Start of modification
create table rob.bismuth1 as
(select mykey, thenbr from rob.bismuth
 order by mykey)
with data;

create table rob.bismuth2 as
(select mykey, thenbr from rob.bismuth
 order by thenbr)
 with data;

CREATE INDEX ROB.bismutha ON ROB.BISMUTH (MYKEY ASC, THENBR ASC);

CREATE INDEX ROB.bismuthb ON ROB.BISMUTH (thenbr ASC, MYKEY ASC);

create table rob.bismuth3 as
(select mykey, thenbr from rob.bismuth
 order by mykey)
with data;

create table rob.bismuth4 as
(select mykey, thenbr from rob.bismuth
 order by thenbr)
 with data;
***** End of modification

Cut a few milliseconds off.  Table 3 and 4 were built with only an index 
scan, and no table scan.  Why?  Because all columns needed in the select 
were already in the index.  Thus bypass the table completely.


Rob Berendt