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

Assuming you have statistics calculated for the relevant table(s), then optimizer mode is capable of being set at the instance level, the session level AND at the statement level.

You'll want that last one, which means sticking a hint in there.

Something like SELECT /* +ALL_ROWS */ blah blah blah

Or SELECT /* +FIRST_ROWS */ blah blah blah.

Both those hints switch on the cost-based optimizer. One tends to favour full table scans, the other tends to favour index access. You'll probably want to experiment to see which one suits you best.

(By the way, the rule-based optimizer is dead in version 10. You probably knew that.)

Regards
HJR "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+NMCostsYr4)
> +

(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 Mon Sep 30 2002 - 16:01:05 CDT