Results 1 to 7 of 7
  1. #1
    Join Date
    May 2005
    Posts
    12

    Question Unanswered: Index not being used

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    102
    Have you heard of update statistics?

  3. #3
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

  4. #4
    Join Date
    May 2005
    Posts
    12
    Quote Originally Posted by blackguard
    Have you heard of update statistics?
    Update statistics High for table sub_dets has been done already.

  5. #5
    Join Date
    May 2005
    Posts
    12
    Quote Originally Posted by Tyveleyn
    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
    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

  6. #6
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Have you defined the clause like this ?
    Code:
    tele_no = "9999054773"
    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.

  7. #7
    Join Date
    May 2005
    Posts
    12
    Quote Originally Posted by Tyveleyn
    Have you defined the clause like this ?
    Code:
    tele_no = "9999054773"
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •