Index advice requested.

OK, I've been running "top sql" queries, OEM Index Analysis, and explain plans until my eyballs are about to crystalize.

We have one particularly ugly query that is beating us to death. It repeatedly shows up when I run queries on the v$sqlarea to identify the sql with the most disk reads, and is far and away the costliest query out there. I have also run OEM Index Analysis Wizard at several different times of the day, over a several day period. None of its reports and recommendations identified the query we're looking at. I tried some of the recommneded indexes that looked like they might have an impact on the query, but the best I could get was one index making a small incremental improvement in the plan by changing one of 4 full table scans to an index scan with a cost reduction from 7171 to 7115.

So here's the deal . . . below is the query, followed by a plan with the index structure as it exists today, followed definitions of the existing indexes. I'm open to any and all recommendations.

explain plan for
Select Distinct TBL1_REQ_NBR,

<SNIP A BUNCH OF COLUMNS>

FROM 	TABLE_01,      	
	SYN_REMOTE_TABLE A, 
	SYN_REMOTE_TABLE B,  
	TABLE_04, 
	TABLE_02, 
	SYN_REMOTE_TABLE C, 
	TABLE_03 
WHERE ((TBL1_REQ_STA_CDE IN('APPROVED', 'COMPLETE') 
AND 	(TBL2_PO_STA_CDE NOT IN('COMPLETE', 'TOOLING') 
OR 	TBL2_PO_STA_CDE IS NULL))         
OR    	(TBL1_EMER_IND    = 'Y' 
AND 	TBL1_REQ_STA_CDE  <> 'COMPLETE')) 
AND 	TBL1_REQ_EMP_NBR  = A.OLNUSR_USER_CDE 
AND	TBL1_BYR_EMP_NBR  = B.OLNUSR_USER_CDE (+) 
AND 	TBL1_PAEMPNBR     = C.OLNUSR_USER_CDE (+) 
AND 	TBL1_RFQ_NBR      = TBL3_RFQ_NBR (+) 
AND 	TBL1_RFQ_NBR      = TBL4_RFQ_NBR (+) 
AND 	TBL1_PO_NBR       = TBL2_PO_NBR (+)  
AND 	TBL1_PO_AMDT_NBR  = TBL2_PO_AMDT_NBR (+)  
AND 	TBL1_BYR_EMP_NBR  = 'XXXXXXXXXXX' 
ORDER BY TBL1_byr_emp_nbr,     
	 TBL1_reqd_dte



OPERATION                 OPTIONS         OBJECT NAME
ORDER        OPT              


------------------------- ---------------

----------------------------------------------- ------------ ------           

 SELECT STATEMENT                         COST = 7171
0-0-7171     CHOOSE           
  SORT                    UNIQUE
1-0-1                         
   HASH JOIN              OUTER
2-1-1                         
    HASH JOIN             OUTER
3-2-1                         
     HASH JOIN            OUTER
4-3-1                         
      HASH JOIN           OUTER
5-4-1                         
       FILTER
6-5-1                         
        HASH JOIN         OUTER
7-6-1                         
         HASH JOIN
8-7-1                         
          TABLE ACCESS    FULL            TABLE_01
9-8-1        ANALYZ           
          REMOTE
10-8-2                        
         TABLE ACCESS     FULL            TABLE_02
11-7-2       ANALYZ           
       TABLE ACCESS       FULL            TABLE_03
12-5-2       ANALYZ           
      REMOTE
13-4-2                        
     REMOTE
14-3-2                        
    TABLE ACCESS          FULL            TABLE_04
15-2-2       ANALYZ           



TBL                  NDX                       POS COL



-------------------- -------------------- -------- --------------------


TABLE_01             TBL1_IDX1                   1 TBL1_REQ_NBR

                                                 2 TBL1_SPLT_SEQNBR



                     TBL1_IDX2                   1 TBL1_PO_NBR

                                                 2 TBL1_PO_AMDT_NBR

                                                 3 TBL1_REQ_NBR

                                                 4 TBL1_SPLT_SEQNBR

                                                 5 TBL1_RFQ_NBR




TBL                  NDX                       POS COL



-------------------- -------------------- -------- --------------------


TABLE_02             TBL2_IDX1                   1 TBL2_PO_NBR

                                                 2 TBL2_PO_AMDT_NBR



                     TBL2_IDX2                   1 TBL2_PRIM_REQ_NBR

                                                 2 TBL2_PRIM_SEQNBR



                     TBL2_IDX3                   1 TBL2_PO_NBR

                                                 2 TBL2_PO_AMDT_NBR

                                                 3 TBL2_PDF_IND

                                                 4 TBL2_ADJ_TOT_CSTAMT



                     TBL2_IDX4                   1 TBL2_PO_STA_CDE

                                                 2 TBL2_PO_NBR



                     TBL2_IDX5                   1 TBL2_PO_AMDT_NBR

                                                 2 TBL2_PRIM_REQ_NBR

                                                 3 TBL2_PRIM_SEQNBR

                                                 4 TBL2_PO_NBR




TBL                   NDX                       POS COL



--------------------  -------------------- -------- --------------------


TABLE_03              TBL3_IDX1                   1 TBL3_RFQ_NBR



TBL                  NDX                       POS COL



-------------------- -------------------- -------- --------------------


TABLE_04             TBL4_IDX1                   1 TBL4_RFQ_NBR

                                                 2 TBL4_SEQ_NBR

                                                 3 TBL4_QTE_SEQ_NBR

--

Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.) Received on Mon Nov 19 2001 - 12:40:23 CST