Re: Indexes ignored

From: Jim Poe <jpoe_at_fulcrumit.com>
Date: Mon, 15 Jul 2002 08:23:55 -0700
Message-ID: <MPG.179c88e6602b1bb2989683@news.callatg.com>

In article <EzxY8.35452$Hj3.107052_at_newsfeeds.bigpond.com>, richard.foote_at_bigpond.com says...
> Hi Joe,
>
> Also, could you post the definition of the OFFICE_PHN_SRCH view (assuming it
> is a view).
>
> If the NBR column is unique, then in theory it should only be returning one
> row. However, if the view is joining a whole bunch of tables together (which
> I should have picked up earlier) then well anything could be happening. You
> could be using a complex view but only be selecting columns from the one
> table (seen that before). Not good. Need to see the query inside the view to
> get the complete picture.

create or replace view office_phn_srch as select /*+ no merge (decodeindex) */

       o.office_id,
       o.name,
       decode( n1,1,ofp.nbr,2,orp.nbr ) nbr,
       decode( n1,1,ofp.ext,2,orp.ext ) ext
from office o,
     office_srch_office_phn ofp,
     office_srch_role_phn orp,

( select n1 from (

    select 1 n1 from dual
    union all
    select 2 n1 from dual )) decodeindex where ofp.office_id(+) = o.office_id and

      orp.office_id(+) = o.office_id

create or replace view office_srch_office_phn as select op.office_id,

       phn.nbr, 
       op.ext
from office_phn op, 
     phn 

where phn.phn_id=op.phn_id

create or replace view office_srch_role_phn as select ofr.office_id,

       phn.nbr, 
       rp.ext
from office_role ofr,
     role_phn rp, 
     phn 
where rp.role_id=ofr.role_id and
      phn.phn_id=rp.phn_id

I think the problem may be that OFFICE_PHN_SRCH contains only outer joins. Could that be the problem?

--
Jim Poe
Fulcrum InteTech, Inc.
<jpoe_at_fulcrumit.com>

Received on Mon Jul 15 2002 - 10:23:55 CDT