Re: Slow performance in SELECT with joins of multiple table

Hi Superboer Thanx for the quick response.Here i m providing the Explain Plan of SELECT STATEMENT CHOOSE 26 12 2028 SORT(UNIQUE) 26 12 2028 HASH JOIN 23 12 2028 "A"."BUSINESS_UNIT"="D"."SETCNTRLVALUE" AND "B"."SETID"="D"."SETID" TABLE ACCESS(BY INDEX ROWID) E881W20.PS_SET_CNTRL_REC ANALYZED 6 148 3996 INDEX(RANGE SCAN) E881W20.PSBSET_CNTRL_REC ANALYZED 3 4232 "D"."RECNAME"='CAL_DEFN_TBL' HASH JOIN 16 659 93578 "C"."CALENDAR_ID"="B"."CALENDAR_ID" "A"."ACCOUNTING_DT">="B"."BEGIN_DT" AND "A"."ACCOUNTING_DT"<="B"."END_DT" HASH JOIN 8 539 58751 "A"."BUSINESS_UNIT"="C"."BUSINESS_UNIT" TABLE ACCESS(FULL) PS_BUS_UNIT_TBL_PC ANALYZED 2 38 342 TABLE ACCESS(FULL) PS_PROJ_RESOURCE ANALYZED 5 539 53900 INDEX(FAST FULL SCAN) PSCCAL_DETP_TBL ANALYZED 6 6851 226083 Superboer wrote: > how about .....UPDATE STATISTICS; > > set explain on;> > <do the select> > > will provide the sql execution plan which can be helpfull in this case > together with > the # of rows for each tables etc. > > Superboer. > > nita schreef: > > > Hi Guys > > Can any one of you help me in improving the performance of the query: > > > > SELECT DISTINCT A.BUSINESS_UNIT ,A.PROJECT_ID ,A.ACTIVITY_ID > > ,A.RESOURCE_ID > > ,A.RESOURCE_ID_FROM ,A.BUSINESS_UNIT_GL ,A.JOURNAL_ID > > ,A.JOURNAL_DATE > > ,A.UNPOST_SEQ ,A.JOURNAL_LINE ,B.FISCAL_YEAR ,B.ACCOUNTING_PERIOD > > ,A.ACCOUNT ,A.ALTACCT , A.DEPTID --,%subrec(CF16_AN_SBR, A) > > ,A.BUS_UNIT_GL_FROM ,A.CURRENCY_CD ,A.STATISTICS_CODE > > ,A.LEDGER_GROUP > > ,A.ANALYSIS_TYPE ,A.RESOURCE_TYPE ,A.RESOURCE_CATEGORY > > ,A.RESOURCE_SUB_CAT > > ,A.RES_USER1 ,A.RES_USER2 ,A.RES_USER3 ,A.RES_USER4 ,A.RES_USER5 > > ,A.TRANS_DT > > ,A.ACCOUNTING_DT ,A.OPRID ,A.DTTM_STAMP ,A.JRNL_LN_REF > > ,A.OPEN_ITEM_STATUS ,A.LINE_DESCR > > ,A.JRNL_LINE_STATUS ,A.JOURNAL_LINE_DATE ,A.FOREIGN_CURRENCY > > ,A.RT_TYPE ,A.FOREIGN_AMOUNT > > ,A.RATE_MULT ,A.RATE_DIV ,A.CUR_EFFDT ,A.PROCESS_INSTANCE > > ,A.PC_DISTRIB_STATUS ,A.GL_DISTRIB_STATUS > > ,A.PROJ_TRANS_TYPE ,A.PROJ_TRANS_CODE ,A.RESOURCE_STATUS ,A.DESCR > > ,A.SYSTEM_SOURCE ,A.UNIT_OF_MEASURE > > ,A.EMPLID ,A.EMPL_RCD ,A.SEQ_NBR ,A.TIME_RPTG_CD ,A.JOBCODE > > ,A.COMPANY ,A.BUSINESS_UNIT_AP ,A.VENDOR_ID > > ,A.VOUCHER_ID ,A.VOUCHER_LINE_NUM ,A.APPL_JRNL_ID ,A.PYMNT_CNT > > ,A.DST_ACCT_TYPE ,A.PO_DISTRIB_STATUS > > ,A.BUSINESS_UNIT_PO ,A.REQ_ID ,A.REQ_LINE_NBR ,A.REQ_SCHED_NBR > > ,A.REQ_DISTRIB_NBR ,A.PO_ID > > ,A.DUE_DATE ,A.LINE_NBR ,A.SCHED_NBR ,A.DISTRIB_LINE_NUM > > ,A.AM_DISTRIB_STATUS ,A.BUSINESS_UNIT_AM > > ,A.ASSET_ID ,A.PROFILE_ID ,A.COST_TYPE ,A.BOOK ,A.INCENTIVE_ID > > ,A.MSTONE_SEQ ,A.CONTRACT_NUM > > ,A.CONTRACT_LINE_NUM ,A.CONTRACT_PPD_SEQ ,A.BI_DISTRIB_STATUS > > ,A.BUSINESS_UNIT_BI ,A.BILLING_DATE > > ,A.INVOICE ,A.REV_DISTRIB_STATUS ,A.BUSINESS_UNIT_AR ,A.CUST_ID > > ,A.ITEM ,A.ITEM_LINE ,A.ITEM_SEQ_NUM ,A.DST_SEQ_NUM > > ,A.BUSINESS_UNIT_IN ,A.SCHED_LINE_NO ,A.DEMAND_LINE_NO > > ,A.INV_ITEM_ID ,A.PAY_END_DT ,A.BUSINESS_UNIT_OM ,A.ORDER_NO > > ,A.ORDER_INT_LINE_NO ,A.EX_DOC_ID ,A.EX_DOC_TYPE > > ,A.RESOURCE_QUANTITY ,A.RESOURCE_AMOUNT ,A.BUDGET_HDR_STATUS > > ,A.KK_AMOUNT_TYPE ,A.KK_TRAN_OVER_FLAG ,A.KK_TRAN_OVER_OPRID > > ,A.KK_TRAN_OVER_DTTM ,A.BUDGET_OVER_ALLOW > > ,A.BUDGET_LINE_STATUS ,A.BUDGET_DT ,A.LEDGER ,A.BD_DISTRIB_STATUS > > ,A.BUSINESS_UNIT_BD ,A.FA_STATUS ,A.TIME_SHEET_ID > > ,A.SHEET_ID ,A.DT_TIMESTAMP ,A.VCHR_DIST_LINE_NUM ,A.PM_REVIEWED > > ,A.PRICED_RATE > > FROM PS_PROJ_RESOURCE A , PS_CAL_DETP_TBL B , PS_BUS_UNIT_TBL_PC C > > , PS_SET_CNTRL_REC D > > WHERE A.ACCOUNTING_DT BETWEEN B.BEGIN_DT AND B.END_DT > > AND A.BUSINESS_UNIT = C.BUSINESS_UNIT > > AND C.CALENDAR_ID = B.CALENDAR_ID > > AND A.BUSINESS_UNIT = D.SETCNTRLVALUE > > AND D.RECNAME = 'CAL_DEFN_TBL' > > AND B.SETID = D.SETID> > > > I tried doing it by putting into subqueries with EXISTS ,but seems to > > be EXISTS makes the performance poor. > > > > Please help me. > > > > Thanks