Re: Slooow select

Michael Elizarov wrote: > Hello *! > I have a table of ranges, which looks like > create table range ( > low decimal(19), > hi decimal(19) check (hi > low), ..... > ); > The table size is ~50K recs. Because of not shown part of the table I > can't have a primary key on low or hi. But I have indexes on them. > Given a particular value I need to make a select in range: > select * from range where value between lo and hi > All this runs (pls, do not laugh -- it is a test site) on a 486/66, > 32RAM, SCO 3.2, Ifx WGS 7.20. A select with small values runs > instantly. When the value raises, and gets up to the highest values of > hi and further, select takes 5..10 sec. > I set explain on, and it showed that from some critical value (~ in > the middle of ranges) select is done by sequential scanning, not by > index. Ok, I set unique indexes on lo and hi (the particular data > allowed this), exported OPTCOMPIND=0 (only consider the index path in > a join pair) and run the select again. sqlexplain.out showed that > select was done by index only, BUT THE TIME DID NOT CHANGED... > > The real system will run on a dual-Pentium, with 128 RAM, but with > table size ~250-300K. Can I do something except adding more RAM/CPU? This is a 7.xx classic. You probably have not updated statistics recently and new ranges have been entered since. The current statistics distributions indicate that it will be faster to sequential scan past a certain value because all of the rows are above this value anyway so using the index will only add I/Os not reduce them. You need to update statistics as recommended in the release notes file: $INFORMIXDIR/release/en_us/0333/SERVERS_7.2 at the beginning of section VI. OPTIMIZER: IMPORTANCE OF RUNNING UPDATE STATISTICS. or get my dostats.ec program from the IIUG archives which does this automatically. BTW if sub-sections 2) and 3) have one paragraph each you have an older version of this file with the same instructions as the manual. Email me and I'll send the updated section from the release notes I have. Art S. Kagel