Hi ,

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.

Below is the query path.

QUERY:
------
select imsi, sub_dets_t, callgroup_t, tpla_code, chk_upd_dt, svc_co_dt, preferred_currency, credit_host, shlf_ex_dt, tele_id_sd, call_cnt,
mt_roam_ind, succ_cnt, svc_prv_t, mo_roam_ind, tele_no, tele_id_ed, tot_cost, home_region, sub_passwd, ann_info, sub_lang, package_t from
sub_dets where tele_no = 9999054773

Estimated Cost: 113337
Estimated # of Rows Returned: 2

1) snadmin.sub_dets: SEQUENTIAL SCAN

Filters: snadmin.sub_dets.tele_no = 9999054773

DB_LOCALE = en_US.819
SESSION COLLATION = en_GB.819


IDS 9.40.HC4 is being used.

Thanks
I

Have you heard of update statistics?

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.

Regards

Update statistics High for table sub_dets has been done already.

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.

Thanks
infxuser

Have you defined the clause like this ?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.

Regards.

Thanks for the response.

By putting quotes around the input value, we are able to make the optimizer choose Index path.

Thanks for the input.

infxuser