Using Hints for Views

From: April <PRIVATEBENJAMIN_at_HUSHMAIL.COM>
Date: 19 Aug 2002 11:05:08 -0700
Message-ID: <6238c379.0208191005.3e0d0a66@posting.google.com>

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 Received on Mon Aug 19 2002 - 13:05:08 CDT