Home » RDBMS Server » Server Administration » Re: OPTIMIZE QUERY
Re: OPTIMIZE QUERY [message #373579] Tue, 24 April 2001 09:32 Go to next message
adelia
Messages: 45
Registered: April 2001
Member
What is the relation between TRUNC and use or not use the Index ?
Re: OPTIMIZE QUERY [message #373591 is a reply to message #373579] Wed, 25 April 2001 08:53 Go to previous messageGo to next message
Krishnan
Messages: 18
Registered: October 2000
Junior Member
Well in a rule based optimizer it looks for the column queried on and if it finds a column called in a where clause has a index then it includes but when it sees trunc(date_fld) it cannot recogonize it as a column or rather rule based optimizer is not intelligent enough to deciphir the column.

-- Krishnan
Re: OPTIMIZE QUERY [message #373639 is a reply to message #373579] Fri, 27 April 2001 18:12 Go to previous message
Suresh
Messages: 189
Registered: December 1998
Senior Member
When you use trunc() or any other function with the field oracle optimizer (rule or cost based)will not recognize index so it won't use index.

choice 1: remove the trunc
choice 2: create a function based index

thanks
Suresh
Previous Topic: determining last date entered using TO_DATE on VARCHAR(2) datatype
Next Topic: Formula to work out 30% of a given field/cost
Goto Forum:
  


Current Time: Mon Dec 23 09:34:14 CST 2024