Re: How to tune this sql (RULE-Based) ?

Hi April,

If I had a day or two to spare, I would help.

But without knowing the characteristics of the tables, the available indexes, etc., it's an impossible task.

One thing I would recommend is to look at the execution plan of the CBO which you've shown to be better, compare with the RBO plan you posted, spot the differences and see if you can re-engineer the code to adopt a similar plan.

Cheers

Richard
"April" <PrivateBenjamin_at_hushmail.com> wrote in message news:54df0379.0209301250.8363cb9_at_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+NMCostsYr 1)
> +

(CashFlow_Contract_yr2+CASHFLOW_Interest_Yr2+CASHFLOW_Overhead_yr2+NMCostsYr 2)
> +

(CashFlow_Contract_yr3+CASHFLOW_Interest_Yr3+CASHFLOW_Overhead_yr3+NMCostsYr 3)
> +

(CashFlow_Contract_yr4+CASHFLOW_Interest_Yr4+CASHFLOW_Overhead_yr4+NMCostsYr 4)
> +

(CashFlow_Contract_yr5+CASHFLOW_Interest_Yr5+CASHFLOW_Overhead_yr5+NMCostsYr 5)
> +

(CashFlow_Contract_yr6+CASHFLOW_Interest_Yr6+CASHFLOW_Overhead_yr6+NMCostsYr 6)
> +

(CashFlow_Contract_yr7+CASHFLOW_Interest_Yr7+CASHFLOW_Overhead_yr7+NMCostsYr 7)
> +

(CashFlow_Contract_yr8+CASHFLOW_Interest_Yr8+CASHFLOW_Overhead_yr8+NMCostsYr 8)
> +

(CashFlow_Contract_yr9+CASHFLOW_Interest_Yr9+CASHFLOW_Overhead_yr9+NMCostsYr 9)
> +

(CashFlow_Contract_yr10+CASHFLOW_Interest_Yr10+CASHFLOW_Overhead_yr10+NMCost sYr10)
> AS Amount,
>

Cashflow_recoverable_yr1+Cashflow_recoverable_yr2+Cashflow_recoverable_yr3+C ashflow_recoverable_yr4+Cashflow_recoverable_yr5+Cashflow_recoverable_yr6+Ca shflow_recoverable_yr7+Cashflow_recoverable_yr8+Cashflow_recoverable_yr9+Cas hflow_recoverable_yr10
> AS Recoverable,
>

Cashflow_removal_yr1+Cashflow_removal_yr2+Cashflow_removal_yr3+Cashflow_remo val_yr4+Cashflow_removal_yr5+Cashflow_removal_yr6+Cashflow_removal_yr7+Cashf low_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 Tue Oct 01 2002 - 20:00:41 CDT