Re: Using Hints for Views

April wrote:

> Hi,
>
> I am trying to use the cbo in Oracle 7.3. Running this query below
> with the cbo takes 13 secs while it takes 500 msec with the rbo. The
> rbo uses the indexes I have stated in the index hint, however the cbo
> does full table scans.
>
> Is my index hint worded correctly? The cbo ignores this hint.
> The tables in the from clause are actually views. Does this have
> anything to do with the fact that the hint is being ignored?
>
> SELECT /*+ INDEX(D TBLINVESTMENT_TABLE_ME,B TBLINVESTMENTS_NEW_ME,C
> TBLINVESTMENT_DESCRIPTION_ME,E TBLEMPLOYEES_ME) */
> d.INV_DRIVER#,
> c.INVESTMENT#,
> b.year,
> F.DEPARTMENT_NAME,
> E.FIRST_NAME,
> E.LAST_NAME,
> SUM(b.contract + b.NMCost + b.INTEREST + b.Overhead +
> b.CASHFLOW_OVERHEAD + B.CTI - b.removal - b.removal_overhead -
> b.recoverable) Total,
> Sum(b.CONTRACT) CONTRACT,
> SUM(b.NMCOST) NMCOST,
> SUM(b.INTEREST) INTEREST,
> SUM(B.REMOVAL) REMOVAL,
> SUM(b.REMOVAL_OVERHEAD) REMOVAL_OVERHEAD,
> SUM(b.CASHFLOW_OVERHEAD) CASHFLOW_OVERHEAD,
> SUM(b.OVERHEAD) OVERHEAD,
> SUM(b.RECOVERABLE) RECOVERABLE,
> SUM(b.CTI) CTI
> FROM
> vw_me_investments_new b,
> vw_me_investment_description c,
> vw_me_Investment_table d,
> VW_ME_EMPLOYEE E,
> VW_RPT_COMMITTINGDEPARTMENTID F
> WHERE
> b.INVESTMENT# = c.INVESTMENT# and
> c.Investment# = d.Investment# and
> b.Investment# = d.Investment# AND
> C.EMPLOYEE# = E.EMPLOYEE# AND
> C.COMMITTING_DEP_ID = F.ID
> GROUP BY d.INV_DRIVER#,c.INVESTMENT#,b.year,F.DEPARTMENT_NAME,E.FIRST_NAME,E.LAST_NAME
>
> Thanks,
> April

CBO in Oracle 7?

What are you trying to accomplish? Dereased performance?

Daniel Morgan Received on Mon Aug 19 2002 - 14:13:27 CDT