Home » RDBMS Server » Performance Tuning » How to use index in date column
How to use index in date column [message #133277] Thu, 18 August 2005 06:33 Go to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
hi all,
i am having a table emp ( from scott user).
I create an index on hiredate of this table.

then why this query is not using index.

SELECT * FROM EMP T WHERE TO_CHAR(T.HIREDATE,'MM/DD/YYYY')=TO_CHAR(TO_DATE('12/17/1980','MM/DD/YYYY'),'MM/DD/YYYY')
Re: How to use index in date column [message #133285 is a reply to message #133277] Thu, 18 August 2005 07:04 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
The index is on t.hiredate not TO_CHAR(T.HIREDATE,'MM/DD/YYYY')
simply use
SELECT * FROM EMP T WHERE
T.HIREDATE=TO_DATE('12/17/1980','MM/DD/YYYY')

HTH
Jim
Re: How to use index in date column [message #133292 is a reply to message #133285] Thu, 18 August 2005 07:25 Go to previous messageGo to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Thanks,
For optimizer goal choose AND All rows your query is not using index except for rule,first rows goal it is using the index range scan ( on hire date column).
What may be the reason. Means cost based optimizer is using the index or any thing else.

With Regds
Dinesh
Re: How to use index in date column [message #133293 is a reply to message #133292] Thu, 18 August 2005 07:26 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Sorry, I have no idea what you just said.
Please clarify.
Re: How to use index in date column [message #133313 is a reply to message #133285] Thu, 18 August 2005 08:27 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
First, follow JSI2001's advice. Those TO_CHARs are completely unnecessary and will only slow you down.

Second, have you added a large number of rows to SCOTT.EMP? If not, then it would be absolutely silly for Oracle to use an index on a table with fourteen rows in it.

Read this AskTom thread for more information.
Re: How to use index in date column [message #135422 is a reply to message #133313] Wed, 31 August 2005 09:16 Go to previous message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Thanks

Regds
Dinesh
Previous Topic: Update issue with high volume table
Next Topic: performance tuning
Goto Forum:
  


Current Time: Mon Nov 28 06:32:19 CST 2022