We have a query which does a sequential scan , inspite of having a index defined on the column which is used in the where clause of the query.
Update statistics high for table <> has been done.
There is a unique index created on the tele_no column of the sub_dets table.
Hi, I've never looked into this but from the size of your tele_no I take it's an INT8 column. Some restrictions apply to the INT8 datatype, perhaps in the case of an index column too.
tele_no is a char(20) not null column and has a unique index on it. The execution path is the same (sequential scan) irrespective of the size of the table . WE have tried with 1000000 records and 100 records in the sub_dets table.
Would really appreciate if there are any suggestions.
Otherwise I can imagine that the server can't use the index because the number from the clause has to be converted to a string first, but I'm absolutely not sure if this applies to indexes.
You can try however to convert the tele_no column to INT8, despite it's previously alledged restrictions, because Informix stores it's index in a B+tree, which is a balanced tree on ordinal values of it's elements. Maybe the long string is too complex for processing ordinal values quickly, what could make the optimiser decide to do a sequential scan. Chances with numerical datatypes are always better.