- To be perfectly honest in 7.3 I'd give up on the optimiser and go back to
rule.(NB opinion)
- your index hint refers to tables not mentioned in the query (which
selects from views) it will be ignored. use the view name instead of the
table name (or hint the view definition if appropriate).
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"April" <PRIVATEBENJAMIN_at_HUSHMAIL.COM> wrote in message
news:6238c379.0208191005.3e0d0a66_at_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_NAM
E
>
>
> Thanks,
> April
Received on Mon Aug 19 2002 - 13:45:31 CDT
