Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17

    Arrow Unanswered: How To Use Informix Optimizer Directives

    Hello All,

    Please let me know how to use informix directives on the following sql
    I want the directives should hit the ORDER BY CLAUSE in-order to avoid creation of temporary Tables. With out Informix Directives Temporary table is created causing the query very slow.

    QUERY:
    ------
    SELECT
    device_requests.trace_audit_no,
    device_requests.trans_date,
    device_requests.trans_time,
    device_requests.device_type,
    dt.device_type_desc,
    device_requests.device_id,
    device_requests.request_no,
    device_requests.card_acceptor_code,
    m.merchant_name,
    device_requests.comp_code
    ,fcc.comp_desc,
    device_requests.trans_local_dtime,
    device_requests.trans_status,
    device_requests.amount,
    device_requests.currency_code,
    cc.currency_desc,
    device_requests.contra_account,
    device_requests.description,
    device_requests.consumer_entry,
    device_requests.business_date,
    device_requests.commission_amount,
    device_requests.retrieval_ref_no,
    device_requests.auth_code,
    fac.auth_desc,
    device_requests.discounted_price,
    products.product_name,
    device_requests.clerk_id,
    device_requests.description,
    device_requests.shift_no,
    device_requests.iso_reconcil_date
    FROM
    device_requests,
    merchants m,
    currency_codes cc,
    fc_auth_codes fac,
    device_types dt,
    fc_comp_codes fcc ,
    products ,
    trans_charges t
    WHERE
    device_requests.card_acceptor_code=m.merchant_id
    AND device_requests.currency_code=cc.currency_code
    AND device_requests.device_type=dt.device_type
    AND device_requests.auth_code=fac.fc_auth_code
    AND device_requests.comp_code=fcc.fc_comp_code
    AND t.trace_audit_no=device_requests.trace_audit_no
    And device_requests.consumer_entry=products.product_id
    AND t.receiver_code=='2090701001 '
    And t.level >0
    order by 1 ASC

    Thaks in advance.
    Hope to hear from you very soon.
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

  2. #2
    Join Date
    Jul 2002
    Location
    HOSPET, INDIA
    Posts
    1
    What are the indexes available on the tables you have used in where clause? Indexes play a very important role in reading records. Make the query according to the index available & then check. Let me know.

    KKS

  3. #3
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17
    Hello,
    I appricate your answere but here is what I actually want.

    I want to avoid the creation of Temporary tables which are created when ever I use Order by clause. Although the columns being used in order by clause are indexed, but the informix optimizer does't use indexes on order by clause and start creating temporary tables for processing(sorting) data.

    Then the things come in mind is to force the Informix optimizer to use indexes of order by cluase by embeding the Informix directives in db query(SQL) . How to use those directive is the problem. I am getting syntax errors in using informix optimizer directives.

    I think it would clear the situation well.

    Please help me out of this problem.
    Thanks in advance.
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

Posting Permissions

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