Re: Indices

Path: emory!wupost!uunet!cpqhou!thomasr From: thomasr@cpqhou.uucp (Thomas Rush) Newsgroups: comp.databases.informix Message-ID: <1991Oct16.131818.29957@cpqhou.uucp> Date: 16 Oct 91 13:18:18 GMT References: <4814@tamsun.TAMU.EDU> Reply-To: thomasr@cpqhou.UUCP (Thomas Rush) Organization: Compaq Computer Corporation In article <4814@tamsun.TAMU.EDU> rjsparks@lstar31.tamu.edu (Robert Sparks) writes: >I have a table (540,000 records) with 4 columns : timestamp,site,channel,value > >The table structure has timestamp indexed. > >I used SQL to add an index on (site,channel) > >Here is the output from SET EXPLAIN ON for three queries: >************************************************************** >QUERY: >------ >select site,channel from data order by site,channel;> >Estimated Cost: 4 >Estimated # of Rows Returned: 10 > >1) rjsparks.data: INDEX PATH > > (1) Index Keys: site channel > > >QUERY: >------ >select distinct site,channel from data order by site,channel;> >Estimated Cost: 7 >Estimated # of Rows Returned: 1 >Temporary Files Required For: Order By > >1) rjsparks.data: SEQUENTIAL SCAN > > >QUERY: >------ >select distinct site,channel from data;> >Estimated Cost: 2 >Estimated # of Rows Returned: 1 > >1) rjsparks.data: SEQUENTIAL SCAN > >*************************************************************** > >the first query returned almost instantaniously. >I interrupted the second two after letting them run for .5 hour or so. > >Why is informix not using the indexes. Do i need to do something to tell it >about the created index? As someone else answered, the engine performs a sequential scan because b+ trees have no efficient facility for finding "next value." Or maybe because the optimizer (I think you are using SE, not OnLine?) is less than maximally efficient. One reason why the optimizer may be guessing wrong is that it looks like the table hasn't had an UPDATE STATISTICS command run in quite a while. A sequential scan of ten rows will likely run much faster than one using an index. Another trick that should be of great help in this case and any case where you will want to perform sequential searches (all ordered LastNames beginning with "R", or addresses ordered by ZipCode) is to create that particular index (only works on one index per table) as a cluster index -- this causes the engine to lay out the data in physical order according to that index. Likewise, you may at any time (well, maybe any not-busy time) ALTER INDEX foo TO CLUSTER, and acheive the same results. >Second question : >After playing around, I dropped the created index. The idx file was touched, >but did not shrink. (It has gotten quite large.) Is there a way to tell >the engine to relinquish some disk space? Since the process of requesting and releasing disk space from and to the OS is costly, SE keeps freed index blocks around for future use (If you've got C-ISAM, the manual talks about how it does this -- in fact, the C-ISAM manual is worth having for reference, even if you never use the product itself, just because of how it explains low-level workings and data structures of the Informix-SE products). It is possible that an ALTER INDEX TO CLUSTER will free up the now-unused space in the .idx. Even if it doesn't, I hope that you will issue an UPDATE STATISTICS command and test your second and third queries above with a clustered index on site,channel. thomas rush uunet!cpqhou!thomasr compaq computer corporation their employee, deep in the hearth of texas not their opinions.