Re: Index question

Claus Samuelsen said: > Dirk Moolman wrote: >> We have a new query, with a where-clause: >> >> where a < b (a & b are date fields) >> and c = 0 (c can have values from 0 to 270) >> and d = "2" (d can have 4 different values) >> >> >> What is the best way to write this where clause, and which are the best >> indexes to use ? >> >> The table has a total of 32 million records. >> > > The 'a < b' equation can only be solved by a sequential scan. Now we do Functional index? > have methods for reducing the amount of sequential scan. One way would > be as you already are looking at: > > > where d = "2" (get rid of most of the records) > > and c = 0 (reduce the above subset to about 25%) > > If this reduces the number of rows (or tuples), you could do two queries > with the first creating a temporary table (into temp). > Then run the 'a < b' on the temp table. > > 'd' can have 4 values ("0", "1", "2", "3") > > You could partion the table using > ... fragment by expression > when d = "0" then dbs0 > when d = "1" then dbs1 > when d = "2" then dbs2 > when d = "3" then dbs3 > > A query with >> where a < b (a & b are date fields) >> and c = 0 (c can have values from 0 to 270) >> and d = "2" (d can have 4 different values) > would then eliminate 3 of the fragments and only scan the 'dbs2' > partition. > > If you want to have many (small) partitions you could use column c, fx > ... fragment by expression > when and c < 10 and c >= 0 then dbs0 > when and c < 20 and c >= 10 then dbs1 > when and c < 30 and c >= 20 then dbs2 > [snip] > when c >= 260 then dbs26 > > Another approach would be to run update statistics high on table(c, d) > and the examine the distribution values to find out which of the two > columns are most significant. If the 'c'-values are evenly distributed, > then a composite index "c, d" will be very efficient. > -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche A smile is a gift that is free to the giver and precious to the recipient. But giving someone the finger is free too, and I find it more personal and sincere. sending to informix-list