Results 1 to 3 of 3

Thread: tuning help

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: tuning help

    This is my query which gets the count of orders by join of various tables.
    How can I tune this query. Plz help

    select count(*)
    into v_order_count
    from FULFILMENT_REQUESTS fr,
    FULFILMENT_COMN_HISTS fch,
    FULFILMENT_STATUS_HISTS fsh,
    COMN_REASONS cr,
    COMN_REASON_STATUS_HISTORIES crsh
    where fr.cid = i_cid (Input)
    and fr.FULFIL_DT between NVL(i_mbrp_qln_start_dt,fr.FULFIL_DT) and NVL(i_mbrp_qln_end_dt, fr.FULFIL_DT)
    and fr.FULFIL_REQ_ID = fsh.FULFIL_REQ_ID
    and FULFIL_REQ_STS_REASN_CD = 'MA'
    and fsh.FULFIL_REQ_STS_ID = fch.FULFIL_REQ_STS_ID
    and fch.COMN_REASN_ID = cr.COMN_REASN_ID
    and cr.REASN_CAT_ID = 8922
    and cr.COMN_REASN_ID = crsh.COMN_REASN_ID
    and crsh.REASN_STS_END_DT IS NULL
    and crsh.curr_ind = 'Y' ;

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Unfortunately no-one but you knows how big these tables are or how selective the various conditions are.

    What Oracle version do you have?

    Have you used Explain Plan (e.g. xplan.sql from SQL*Plus), and what was the output? (formatted with [CODE] tags please so we can read it )

    Have all the tables been analyzed recently? Are the NUM_ROWS values in USER_TABLES close to the actual counts? Do you know what exact analyze command was used?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    One guideline for writing efficient SQL is that only tables which contribute columns to the SELECT clause should exist in the FROM clause.
    Tables which are only needed for "proper filtering" should be relegated into the WHERE clause.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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