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.CREATED > (SELECT MAX(PERIOD_TO) FROM DWC_CONTROL WHERE
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.
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.
to start with upper( ) will force a full table scan
Originally posted by samahit
(SELECT MAX(PERIOD_TO) FROM DWC_CONTROL WHERE
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.
Click Here to Expand Forum to Full Width