Tune the query

[samahit]

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.

[anandkl]

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.

[pando]

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

[samahit]

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.