SELECT DISTINCT template_adj_id,
                pcms_template_adj.wp_adj_id template_wp_adj_id,
                pcms_adj.wp_adj_id curr_wp_adj_id, pcms_template_adj.err_cd,
                pcms_template_adj.mntnc_action_cd, pcms_adj.slsrp_num,
                pcms_usage.curr_wc_adj_rule
           FROM pcms_adj, pcms_template_adj, pcms_usage
          WHERE pcms_template_adj.wp_source_id = wp_cust_num(+)
            AND pcms_template_adj.wp_mtl_num = pcms_adj.wp_mtl_num(+)
            AND pcms_template_adj.source_type IN (1, 2)
            AND pcms_adj.wp_adj_id = pcms_usage.curr_wp_adj_id(+)
            AND 'M' = pcms_usage.curr_wc_adj_rule(+)
            AND (   (    pcms_adj.slsrp_num IS NOT NULL
                     AND pcms_adj.wp_adj_id IS NOT NULL
                    )
                 OR (pcms_adj.slsrp_num IS NULL AND pcms_adj.wp_adj_id IS NULL
                    )
                )
            AND template_id = 1063



DEVELOPMENT

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		41  	 	615  	 	      	             	 
  SORT UNIQUE		41  	4 K	615  	 	      	             	 
    NESTED LOOPS OUTER		1 K	133 K	578  	 	      	             	 
      FILTER		  	 	 	 	      	             	 
        NESTED LOOPS OUTER		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_TEMPLATE_ADJ	1  	86  	1  	 	      	             	 
            INDEX RANGE SCAN	PRCG.PCMS_TEMPLATE_IDX1	42  	 	1  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_ADJ	1 K	26 K	1  	 	      	             	 
            INDEX RANGE SCAN	PRCG.PCMS_ADJ_IDX5	41 K	 	2  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_USAGE	1  	8  	1  	 	      	             	 
        INDEX RANGE SCAN	PRCG.PCMS_USAGE_IDX1	2  	 	2  	 	      	             	 



PRODUCTION

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		4 K	 	36569  	 	      	             	 
  SORT UNIQUE		4 K	324 K	36569  	 	      	             	 
    NESTED LOOPS OUTER		4 K	324 K	36451  	 	      	             	 
      FILTER		  	 	 	 	      	             	 
        MERGE JOIN OUTER		  	 	 	 	      	             	 
          SORT JOIN		4 K	174 K	289  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_TEMPLATE_ADJ	4 K	174 K	213  	 	      	             	 
              INDEX RANGE SCAN	PRCG.PCMS_TEMPLATE_IDX1	8 K	 	43  	 	      	             	 
          SORT JOIN		958 K	27 M	15893  	 	      	             	 
            TABLE ACCESS FULL	PRCG.PCMS_ADJ	958 K	27 M	5256  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	PRCG.PCMS_USAGE	1  	8  	5  	 	      	             	 
        INDEX RANGE SCAN	PRCG.PCMS_USAGE_IDX1	2  	 	2