Index access only

[rob@xxxxxxxxx]

Awhile back we talked of where an sql statement may actually read only the 
index and bypass accessing the table (physical file) altogether if the 
index is built right.  The following was buried in an earlier email and I 
didn't want it overlooked.

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