Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index not used

Re: Index not used

From: Ranko Mosic <ranko.mosic_at_gmail.com>
Date: Fri, 19 May 2006 16:05:39 -0400
Message-ID: <367369f10605191305h1a9e9c04p6f79f884236a8916@mail.gmail.com>


Sorry Wolfgang and others - as I said I have to rewrite the query, that's why confustion happened.

rm

On 5/19/06, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
>
> Transitive closure doesn't work not because t2 is a xref table but because
> you
> originally misstated the query:
>
> SELECT *
> 2 FROM tab1 t1 ,
> 3 tab2 t2 ,
> 4 tab3 t3
> 5 WHERE t1.id = t2.id
> 6 AND t2.id = t3.id
> 7 AND t1.type IN
> 8 ('A','B','C')
> 9 AND t3.type = 'A'
> 10* AND t3.start_date = '01-APR-2004'
>
> That was quite a bit different from what you claim now. In that form
> transitive
> closure follows from logic rules.
> In the new form there is of course no transitive closure because the join
> predicates are different.
>
> Quoting Ranko Mosic <ranko.mosic_at_gmail.com>:
>
> > Correction, query looks like this ( have to rewrite the original, so
> little
> > bit of confusion here )
> > SELECT *
> > 2 FROM tab1 t1 ,
> > 3 tab2 t2 ,
> > 4 tab3 t3
> > 5 WHERE t1.t1_id <http://t1.id/> = t2.t1_id <http://t2.id/>
> > 6 AND t2.t3_id <http://t2.id/> = t3._t3id <http://t3.id/>
> > 7 AND t1.type IN
> > 8 ('A','B','C')
> > 9 AND t3.type = 'A'
> > 10* AND t3.start_date = '01-APR-2004'
> > Again, T2 is xref table. That's why transition can't work, Wolfgang.
> > For Igor, I played with stripped down version of query without filters
> to
> > see what happes.
> > The original query has filters.
> >
>
> --
> regards
>
> Wolfgang Breitling
> Oracle 7,8,8i,9i OCP DBA
> Centrex Consulting Corporation
> www.centrexcc.com
>
>

-- 
Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html

--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 19 2006 - 15:05:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US