Indices

Path: emory!wupost!cs.utexas.edu!tamsun!lstar31!rjsparks From: rjsparks@lstar31.tamu.edu (Robert Sparks) Newsgroups: comp.databases.informix Message-ID: <4814@tamsun.TAMU.EDU> Date: 14 Oct 91 20:53:54 GMT Sender: news@tamsun.TAMU.EDU Organization: Academic Computing Services I am just coming up to speed on Informix and SQL. I'm losing an argument with the computer and I wonder if anyone can shed some light on what's going on. 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? ----------- 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? RjS Robert Sparks rjsparks@loanstar.tamu.edu