Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> How to tune this sql (RULE-Based) ?

How to tune this sql (RULE-Based) ?

From: April <PrivateBenjamin_at_hushmail.com>
Date: 30 Sep 2002 13:50:46 -0700
Message-ID: <54df0379.0209301250.8363cb9@posting.google.com> 





This view takes about 18 seconds to run when rule-based and 3 seconds
when cost-based on Oracle 7.3.



So you say, why don't you just use cost-based...
well most of the other queries in the database were set up for
rule-based and don't work so well when table statistics exist.



So how would I go about making this query work faster in the
rule-based environment?




Thanks,


April




SELECT



  DISTINCT tblSub_Investments.INVESTMENT#,
  tblSub_Investments.SUB_INVESTMENT#,


  tblrelease_status_dates.release_status_code,

  tblrelease_code_table.STATUS_DESCRIPTION,
  tblinvestment_drivers.INV_DRIVER#,
  tblinvestment_drivers.INV_DRIVER_DESCRIPTION,


  tblinvestment_table.INVESTMENT_DESCRIPTION,
  tblSub_Investments.SUB_INVESTMENT_DESCRIPTION,
  tblRELEASE_TABLE.RELEASE_DATE,

  tblEmployee.First_Name,
  tblEmployee.Last_Name,
  tblEmployee.Phone_Number,




  (CashFlow_Contract_yr1+CASHFLOW_Interest_Yr1+CASHFLOW_Overhead_yr1+NMCostsYr1)
+ (CashFlow_Contract_yr2+CASHFLOW_Interest_Yr2+CASHFLOW_Overhead_yr2+NMCostsYr2)

     	    + (CashFlow_Contract_yr3+CASHFLOW_Interest_Yr3+CASHFLOW_Overhead_yr3+NMCostsYr3)


          		+ (CashFlow_Contract_yr4+CASHFLOW_Interest_Yr4+CASHFLOW_Overhead_yr4+NMCostsYr4)

          		+ (CashFlow_Contract_yr5+CASHFLOW_Interest_Yr5+CASHFLOW_Overhead_yr5+NMCostsYr5)

          		+ (CashFlow_Contract_yr6+CASHFLOW_Interest_Yr6+CASHFLOW_Overhead_yr6+NMCostsYr6)

          		+ (CashFlow_Contract_yr7+CASHFLOW_Interest_Yr7+CASHFLOW_Overhead_yr7+NMCostsYr7)

           		+ (CashFlow_Contract_yr8+CASHFLOW_Interest_Yr8+CASHFLOW_Overhead_yr8+NMCostsYr8)
           		+ (CashFlow_Contract_yr9+CASHFLOW_Interest_Yr9+CASHFLOW_Overhead_yr9+NMCostsYr9)
           		+ (CashFlow_Contract_yr10+CASHFLOW_Interest_Yr10+CASHFLOW_Overhead_yr10+NMCostsYr10)


AS Amount,


  Cashflow_recoverable_yr1+Cashflow_recoverable_yr2+Cashflow_recoverable_yr3+Cashflow_recoverable_yr4+Cashflow_recoverable_yr5+Cashflow_recoverable_yr6+Cashflow_recoverable_yr7+Cashflow_recoverable_yr8+Cashflow_recoverable_yr9+Cashflow_recoverable_yr10
AS Recoverable,


  Cashflow_removal_yr1+Cashflow_removal_yr2+Cashflow_removal_yr3+Cashflow_removal_yr4+Cashflow_removal_yr5+Cashflow_removal_yr6+Cashflow_removal_yr7+Cashflow_removal_yr8+Cashflow_removal_yr9+Cashflow_removal_yr10
AS Removal


FROM



  tblSub_Investment_Descript,


  tblSub_Investments,


  tblrelease_status_dates,


  TBLINVESTMENTS,



  tblInvestment_Description,


  tblEmployee,


  tblRELEASE_TABLE,


  tblinvestment_table,


  tblrelease_code_table,


  tblinvestment_drivers,


  tblrelease_status_dates statuscodecheck
WHERE



  tblsub_investments.investment# = tblinvestments.INVESTMENT#        	
and


  tblsub_investments.sub_investment# = tblinvestments.SUB_INVESTMENT# 

            	 and
  tblrelease_table.INVESTMENT# = tblinvestments.investment#      	 and
  tblrelease_table.SUB_INVESTMENT# = tblinvestments.sub_investment#   
        	 and
  tblSub_Investment_Descript.Investment# = tblinvestments.investment# 
    	 and
  tblSub_Investment_Descript.Sub_Investment# =
tblinvestments.sub_investment#      	 and
  tblInvestment_Description.Investment# = tblinvestments.investment#  
   	 and
  tblinvestment_table.INVESTMENT# = tblinvestments.investment#        
        and
  tblinvestment_drivers.inv_driver# = tblinvestment_table.inv_driver#	
            	 and
  tblEmployee.Employee#  = tblInvestment_Description.Employee#       	


and


  tblrelease_code_table.status_code =

tblrelease_status_dates.release_status_code   	 and
  tblrelease_status_dates.release# = tblrelease_Table.release#   	 and
  tblrelease_status_dates.investment# = tblrelease_Table.investment#  
	 and
  tblrelease_status_dates.sub_investment# =
tblrelease_Table.sub_investment#      	 and
  statuscodecheck.release# = tblrelease_Table.release#   	 and
  statuscodecheck.investment# = tblrelease_Table.investment#   	 and
  statuscodecheck.sub_investment# = tblrelease_Table.sub_investment#  
     and




  statuscodecheck.release_status_code = 3 and
  tblrelease_code_table.effectivedate = (select max(a.EffectiveDate)
from tblRelease_code_table a where a.Status_Code =
tblrelease_code_table.status_Code and

  a.cancelled = tblrelease_code_table.cancelled)      	 and
  (tblRELEASE_TABLE.STATUS_CODE > 2)      	 and


  tblInvestment_Description.EffectiveDate = (select
max(a.EffectiveDate) from tblInvestment_Description a where
a.Investment# = tblInvestment_Description.Investment# and
  a.cancelled = tblInvestment_Description.cancelled)      	 and
  tblInvestment_table.EffectiveDate = (select max(a.EffectiveDate)
from tblInvestment_table a where a.Investment# =
tblInvestment_table.Investment# and


  a.cancelled = tblInvestment_table.cancelled)      	 and
  tblInvestment_drivers.EffectiveDate = (select max(a.EffectiveDate)
from tblInvestment_drivers a where a.inv_driver# =
tblInvestment_drivers.inv_driver# and


  a.cancelled = tblInvestment_drivers.cancelled)      	 and
  tblSub_Investments.EffectiveDate = (select max(a.EffectiveDate) from
tblSub_Investments  a where a.Investment# =
tblSub_Investments.Investment# and


  a.Sub_Investment# = tblSub_Investments.Sub_Investment#  and
  a.cancelled = tblSub_Investments.cancelled)      	 and
  tblSub_Investment_Descript.EffectiveDate = (select
max(a.EffectiveDate) from tblSub_Investment_Descript  a where
a.Investment# = tblSub_Investment_Descript.Investment# and
  a.Sub_Investment# = tblSub_Investment_Descript.Sub_Investment#   and
  a.cancelled = tblSub_Investment_Descript.cancelled)      	 and
  tblEmployee.EffectiveDate = (select max(a.EffectiveDate) from
tblEmployee a where a.Employee# = tblEmployee.Employee#)      	 and
  tblRELEASE_status_dates.EffectiveDate = (select max(a.EffectiveDate)
from tblRELEASE_status_dates  a where a.release# =
tblRELEASE_status_dates.release# and

  a.Investment# = tblRELEASE_status_dates.Investment# and
  a.Sub_Investment# = tblRELEASE_status_dates.Sub_Investment# and
  a.release_status_code = tblrelease_status_dates.release_status_code)
  	 and




  tblRELEASE_TABLE.EffectiveDate = (select max(a.EffectiveDate) from
tblRELEASE_TABLE  a where a.release# = tblrelease_table.release# and
  a.Investment# = tblRELEASE_TABLE.Investment# and
  a.Sub_Investment# = tblRELEASE_TABLE.Sub_Investment# )      	 and
  TBLINVESTMENTS.EffectiveDate = (select max(a.EffectiveDate) from
TBLINVESTMENTS a where a.Investment# = TBLINVESTMENTS.Investment# and

  a.sub_investment# = tblinvestments.sub_investment# and
  a.cancelled = tblinvestments.cancelled and
  a.effectivedate <= tblrelease_status_dates.effectivedate)   	 and
  tblinvestments.cancelled = 0      	 and
  tblrelease_table.cancelled = 0      	 and
  tblemployee.cancelled = 0      	 and
  tblsub_investment_descript.cancelled = 0      	 and
  tblsub_investments.cancelled = 0      	 and
  tblinvestment_drivers.cancelled = 0      	 and
  tblinvestment_table.cancelled = 0      	 and
  tblinvestment_description.cancelled = 0      	 and
  tblrelease_code_table.cancelled = 0      	 and


  tblinvestment_drivers.INV_DRIVER# in


('TC108','TC109','TC119','TC201','TC202','TC203','TC211','TC212');



EXPLAIN PLAN


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



SELECT STATEMENT Hint=CHOOSE		  	 	 	 	 	 	 
  SORT UNIQUE		  	 	 	 	 	 	 



    FILTER		  	 	 	 	 	 	 


      NESTED LOOPS		  	 	 	 	 	 	 
        NESTED LOOPS		  	 	 	 	 	 	 
          NESTED LOOPS		  	 	 	 	 	 	 
            NESTED LOOPS		  	 	 	 	 	 	 
              NESTED LOOPS		  	 	 	 	 	 	 
                NESTED LOOPS		  	 	 	 	 	 	 
                  NESTED LOOPS		  	 	 	 	 	 	 
                    NESTED LOOPS		  	 	 	 	 	 	 
                      NESTED LOOPS		  	 	 	 	 	 	 
                        NESTED LOOPS		  	 	 	 	 	 	 
                          TABLE ACCESS BY
ROWID	TBLRELEASE_STATUS_DATES
                            INDEX RANGE SCAN	STATUS_CODE5892
                          TABLE ACCESS BY ROWID	TBLRELEASE_TABLE
                            INDEX RANGE SCAN	RELEASE#	  	 	 	 	 	 	 
                        TABLE ACCESS BY ROWID	TBLRELEASE_STATUS_DATES
                          AND-EQUAL		  	 	 	 	 	 	 
                            INDEX RANGE
SCAN	RELEASE_STATUS_DATESRELEAS
                            INDEX RANGE SCAN	SUB_INVESTMENT#
                      TABLE ACCESS BY ROWID	TBLRELEASE_CODE_TABLE
                        INDEX RANGE SCAN	STATUS_CODE_ID	  	 	 	 	 	 	 
                    TABLE ACCESS BY ROWID	TBLINVESTMENTS
                      INDEX RANGE SCAN	INVESTMENTS	  	 	 	 	 	 	 
                  TABLE ACCESS BY ROWID	TBLSUB_INVESTMENTS
                    INDEX RANGE SCAN	TBLSUB_INVESTMENTS_ME
                TABLE ACCESS BY ROWID	TBLSUB_INVESTMENT_DESCRIPT
                  INDEX RANGE SCAN	TBLSUB_INVESTMENT_DESCRIPT_ME
              TABLE ACCESS BY ROWID	TBLINVESTMENT_TABLE	  	 	 	 	 	 	 
                INDEX RANGE SCAN	TBLINVESTMENT_TABLE_ME	  	 	 	 	 	 	 
            TABLE ACCESS BY ROWID	TBLINVESTMENT_DRIVERS	  	 	 	 	 	 	 
              INDEX RANGE SCAN	TBLINVESTMENT_DRIVERS_ME	  	 	 	 	 	 	 
          TABLE ACCESS BY ROWID	TBLINVESTMENT_DESCRIPTION
            INDEX RANGE SCAN	TBLINVESTMENT_DESCRIPTION_ME
        TABLE ACCESS BY ROWID	TBLEMPLOYEE	  	 	 	 	 	 	 
          INDEX RANGE SCAN	EMPLOYEE_ID	  	 	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS BY ROWID	TBLRELEASE_CODE_TABLE	  	 	 	 	 	 	 
          INDEX RANGE SCAN	STATUS_CODE_ID	  	 	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS BY ROWID	TBLINVESTMENT_DESCRIPTION	  	 	 	 	 	 	 
          INDEX RANGE SCAN	TBLINVESTMENT_DESCRIPTION_ME	  	 	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS BY ROWID	TBLINVESTMENT_TABLE	  	 	 	 	 	 	 
          INDEX RANGE SCAN	TBLINVESTMENT_TABLE_ME	  	 	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS BY ROWID	TBLINVESTMENT_DRIVERS	  	 	 	 	 	 	 
          INDEX RANGE SCAN	TBLINVESTMENT_DRIVERS_ME	  	 	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS BY ROWID	TBLSUB_INVESTMENTS	  	 	 	 	 	 	 
          INDEX RANGE SCAN	TBLSUB_INVESTMENTS_ME	  	 	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS BY ROWID	TBLSUB_INVESTMENT_DESCRIPT
          INDEX RANGE SCAN	TBLSUB_INVESTMENT_DESCRIPT_ME
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS BY ROWID	TBLEMPLOYEE	  	 	 	 	 	 	 
          INDEX RANGE SCAN	EMPLOYEE_ID	  	 	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS BY ROWID	TBLRELEASE_STATUS_DATES	  	 	 	 	 	 	 
          INDEX RANGE SCAN	TBLRELEASE_STATUS_DATES_ME2	  	 	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS BY ROWID	TBLRELEASE_TABLE	  	 	 	 	 	 	 
          INDEX RANGE SCAN	RELEASE#	  	 	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        INDEX RANGE SCAN	SYS_C001422


Received on Mon Sep 30 2002 - 15:50:46 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US