Tune the query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Tune the query

  1. #1
    Join Date
    Jun 2001
    Posts
    28
    I have got query like this.
    Every table has got almost 10-15 lakhs of records.
    This following statement creates a temp. table.Can somebody suggest me which tuning tech. should I use.
    This gets hanged whenever I use to fire this query.

    SELECT S_ORG_EXT.ROW_ID, S_ORG_EXT.CREATED, S_ORG_EXT.NAME,
    S_ORG_EXT.BASE_CURCY_CD, S_ORG_EXT.DESC_TEXT, S_ORG_EXT.INTEGRATION_ID,
    S_ORG_EXT.LOC, S_ORG_EXT.MAIN_FAX_PH_NUM, S_ORG_EXT.MAIN_PH_NUM,
    S_ORG_EXT.X_AC_CLSFCTN,
    S_ORG_EXT.DOM_ULT_DUNS_NUM,
    S_ORG_EXT.GLBLULT_DUNS_NUM,
    S_ORG_EXT.PAR_DUNS_NUM,
    S_ORG_EXT.X_PR_FRANCHISOR_CONCEPT,
    S_ADDR_ORG.ADDR,
    S_ADDR_ORG.X_SF_APT_COD,
    S_ADDR_ORG.CITY,
    S_ADDR_ORG.STATE,
    S_ADDR_ORG.ZIPCODE,
    S_ADDR_ORG.COUNTY,
    S_ADDR_ORG.COUNTRY,
    S_CONTACT.FST_NAME,
    S_CONTACT.LAST_NAME,
    S_CONTACT.EMAIL_ADDR,
    S_CONTACT.WORK_PH_NUM,
    S_ORG_EXT_X.ATTRIB_40,
    S_ORG_EXT_X.ATTRIB_42,
    S_ORG_EXT_X.ATTRIB_41,
    S_INDUST.SIC,
    S_INDUST.NAME,
    S_ORG_EXT_X.ATTRIB_05,
    S_CHRCTR.NAME,
    S_CHRCTR_VAL.NAME,
    S_ORG_EXT_X.ATTRIB_26,
    S_ORG_EXT_X.ATTRIB_06,
    S_ORG_EXT_X.ATTRIB_35
    FROM
    S_ORG_EXT,
    S_ADDR_ORG,
    S_CONTACT,
    S_ORG_EXT_X,
    S_INDUST,
    S_CHRCTR,
    S_CHRCTR_VAL,
    S_ACCNT_CHRCTR
    WHERE
    S_ORG_EXT.CREATED > (SELECT MAX(PERIOD_TO) FROM DWC_CONTROL WHERE
    UPPER(TABLE_FLAT_FILE_NAME)='DCC_SALES_ACCOUNT') AND
    S_ORG_EXT.PAR_OU_ID = S_ORG_EXT_X.ROW_ID AND
    S_ORG_EXT.PR_ADDR_ID = S_ADDR_ORG.ROW_ID AND
    S_ORG_EXT.PR_SHIP_PER_ID = S_CONTACT.ROW_ID AND
    S_ORG_EXT.X_AC_CON_ID = S_CONTACT.ROW_ID AND
    S_ORG_EXT.X_AC_CH_PILOT_ID = S_CONTACT.ROW_ID AND
    S_ORG_EXT.ROW_ID = S_ACCNT_CHRCTR.OU_EXT_ID AND
    S_ACCNT_CHRCTR.CHRCTR_ID = S_CHRCTR.ROW_ID AND
    S_CHRCTR.ROW_ID = S_CHRCTR_VAL.CHRCTR_ID AND
    S_ORG_EXT.PR_INDUST_ID = S_INDUST.ROW_ID

    Thanks in Advance.

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    tuning sql

    Just make sure the columns used in the where conditions are primary keys and what kind of application r u using whether its oltp or dss.
    anandkl

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by samahit
    (SELECT MAX(PERIOD_TO) FROM DWC_CONTROL WHERE
    UPPER(TABLE_FLAT_FILE_NAME)='DCC_SALES_ACCOUNT')
    to start with upper( ) will force a full table scan

  4. #4
    Join Date
    Jun 2001
    Posts
    28
    The application is a datawarehousing app.I am sorry I didn't mention dwc_control table is having only 4-5 records.
    about primary key,no primary key is there for this tables .but indexes are there.as it is going to return more that 80% of the rows of each table so index will not be used.even I tried with all_rows hint after analyzing the tables.For ur information the rows are properly distributed.

Posting Permissions

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



Click Here to Expand Forum to Full Width