Re: Indexes ignored

From: Jim Poe <jpoe_at_fulcrumit.com>
Date: Mon, 15 Jul 2002 13:10:37 -0700
Message-ID: <MPG.179cc05b9a95dbb9989684@news.callatg.com>

 

Hi,

I finally found a solution. I now get .4 secs result with Upper and .3 secs without upper.

Basically, I was using the wrong driving table. I now start in the PHN table and then join the result to the other tables.

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

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

( select n1 from (

    select 1 n1 from dual
    union all
    select 2 n1 from dual )) decodeindex where orp.phn_id(+) = phn.phn_id and

      ofp.phn_id(+) = phn.phn_id and
      o.office_id = decode( n1,1,ofp.office_id,2,orp.office_id )

/

create or replace view office_srch_office_phn as select op.office_id,

       phn.phn_id,
       op.ext
from phn,
     office_phn op

where phn.phn_id=op.phn_id
/

create or replace view office_srch_role_phn as select ofr.office_id,

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

/

Thanks for the help

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

Received on Mon Jul 15 2002 - 15:10:37 CDT