RE: Index not used

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 18 May 2006 16:24:00 -0400
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2F20360@EXCHANGE.corp.perceptron.com>

>Actually, when filters are added to t1 then FTS on T2 is gone. Why
would that be ?  

Your original message stated otherwise (AND t1.type IN ('A','B','C')):  

SELECT *
> 2 FROM tab1 t1 ,

> 3 tab2 t2 ,
> 4 tab3 t3
> 5 WHERE t1.id <http://t1.id/> = t2.id <http://t2.id/>
> 6 AND t2.id <http://t2.id/> = t3.id <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'
>
> All join cols are indexed on leading cols. There is FTS on t2

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ranko Mosic Sent: Thursday, May 18, 2006 3:29 PM
To: Bobak, Mark
Cc: Wolfgang Breitling; _oracle_L_list
Subject: Re: Index not used

Actually, when filters are added to t1 then FTS on T2 is gone. Why would that be ?    

rm  

On 5/18/06, Ranko Mosic <ranko.mosic_at_gmail.com> wrote:

        t is cross ref table betwen t1 and t3 , so 3rd join can't be added ( t1.id <http://t1.id/> = t3.id <http://t3.id/> ).          

        Well, I guees Mark has the right when saying that at least one table must be FTS as driver table in the

	absence of filters. 
	This is exactly what customer noticed too - by adding additional

filters FTS is completelly gone.          

	Mistery solved I guess. 
	Thanks all, especially Mark. 
	 
	Rgrds, rm.  
	
	 
	
	On 5/18/06, Bobak, Mark <Mark.Bobak_at_il.proquest.com > wrote: 

		Um, ok.  Why would you not expect at least one FTS?

There is no filter predicate, therefore, the driving table MUST do FTS. Depending on statistics, the optimizer may decide that utilizing the join predicate to drive a index access is efficient to join the second table to the driving table, or it may decide to FTS the second table as well.                  

                However, in that query, at least one table MUST be full scanned.                                   

                -Mark                  

		-- 
		Mark J. Bobak 
		Senior Oracle Architect 
		ProQuest Information & Learning 

		For a successful technology, reality must take

precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988                  

---

                

                From: oracle-l-bounce_at_freelists.org [mailto: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of Ranko Mosic                 

		Sent: Thursday, May 18, 2006 2:06 PM
		To: Wolfgang Breitling
		Cc: _oracle_L_list
		
		Subject: Re: Index not used
		

		 
		
		Even the following query does FTS: 
		SELECT *
		>  2   FROM tab1 t1 ,
		>  3        tab2 t2 
		where t1.id <http://t1.id/>  = t2.id <http://t2.id/>  
		 
		 
		rm
		
		 
		On 5/17/06, Wolfgang Breitling < breitliw_at_centrexcc.com

<mailto:breitliw_at_centrexcc.com> > wrote:

                There is no predicate for tab2, the smallest of the three tables, apart from the

                join predicate. Are there usable indexes on tab1.type and tab3.start_date?

                Any changes if you add the transitive closure predicate (the optimizer does not

		do this (yet)):
		t1.id <http://t1.id/>  = t2.id <http://t2.id/>  and

t2.id <http://t2.id/> = t3.id <http://t3.id/> => t1.id <http://t1.id/> = t3.id <http://t3.id/>

                It opens additional access paths.                                  

                Quoting Ranko Mosic < ranko.mosic_at_gmail.com <mailto:ranko.mosic_at_gmail.com> >:                 

		> Hi List,
		>
		
		> SELECT *
		>  2   FROM tab1 t1 ,
		
		>  3        tab2 t2 ,
		>  4        tab3 t3
		>  5  WHERE t1.id <http://t1.id/>  = t2.id
<http://t2.id/> 
		>  6    AND t2.id <http://t2.id/>  = t3.id
<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'
		>
		> 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/ContractSeniorOracleDBARankoMo sicMain.html

                >                                  

		--
		regards
		
		Wolfgang Breitling
		Oracle 7,8,8i,9i OCP DBA
		
		Centrex Consulting Corporation
		www.centrexcc.com <http://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/ContractSeniorOracleDBARankoMo sicMain.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/ContractSeniorOracleDBARankoMo sicMain.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/ContractSeniorOracleDBARankoMo
sicMain.html 


--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 18 2006 - 15:24:00 CDT