Re: Index not used

From: Ranko Mosic <ranko.mosic_at_gmail.com>
Date: Tue, 16 May 2006 17:35:49 -0400
Message-ID: <367369f10605161435v55b3d4f1qe3aaaec784dd918@mail.gmail.com>

 Correction, query looks like this:
SELECT *
 2 FROM tab1 t1 ,

 3        tab2 t2 ,
 4        tab3 t3

 5 WHERE t1.id = *t2.id* <http://t2.id/>* *  6 AND *t2.id* <http://t2.id/>*1* = t3.id  7 AND t1.type IN
 8 ('A','B','C')
 9 AND t3.type = 'A'
10* AND t3.start_date = '01-APR-2004'

FTS is probably not the right thing to do - when I put index hint cardinality went down dramatically.
Clustering_factor for index on t2.id is 110582943 and on t2.id1 is 42661968

Traces are coming if I can get them .

Regards, Ranko

On 5/16/06, Ric Van Dyke <ric.van.dyke_at_hotsos.com> wrote:
>
> A full table scan might be the right thing to do. What's the 10046 and
> 10053 traces look for the run of the plan? What's the Cluster Factor on the
> indexes?
>
>
>
> Ric Van Dyke
>
> Hotsos Enterprises
>
> Cell 248-705-0624
>
> -----------------------
>
> Hotsos Symposium March 4-8, 2007. Be there.
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ranko Mosic
> *Sent:* Tuesday, May 16, 2006 4:36 PM
> *To:* _oracle_L_list
> *Subject:* Index not used
>
>
>
> Hi List,
>
>
>
> 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'
>
>
>
> All join cols are indexed on leading cols. There is FTS on t2 . Stats are
> fresh and computed .
>
> Why FTS on t2 ? ( v 9.2 ).
> --
> 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
>
>

-- 
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 Tue May 16 2006 - 16:35:49 CDT