RE: Index not used

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] 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 = t2.id
 
 
rm

 
On 5/17/06, Wolfgang Breitling <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 = t2.id and t2.id = t3.id => t1.id = 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 = 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/ContractSeniorOracleDBARankoMo
sicMain.html


	>

	
	
	--
	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/ContractSeniorOracleDBARankoMo
sicMain.html 


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