OraFAQ Forum: Performance Tuning » How to use index in date column

Home » RDBMS Server » Performance Tuning » How to use index in date column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to use index in date column [message #133277]

Thu, 18 August 2005 06:33

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')

Report message to a moderator

Re: How to use index in date column [message #133285 is a reply to message #133277]

Thu, 18 August 2005 07:04

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

Report message to a moderator

Re: How to use index in date column [message #133292 is a reply to message #133285]

Thu, 18 August 2005 07:25

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

Report message to a moderator

Re: How to use index in date column [message #133293 is a reply to message #133292]

Thu, 18 August 2005 07:26

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Sorry, I have no idea what you just said.
Please clarify.

Report message to a moderator

Re: How to use index in date column [message #133313 is a reply to message #133285]

Thu, 18 August 2005 08:27

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.

Report message to a moderator

Re: How to use index in date column [message #135422 is a reply to message #133313]

Wed, 31 August 2005 09:16

d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta

Senior Member

Thanks

Regds
Dinesh

Report message to a moderator

Previous Topic:	Update issue with high volume table
Next Topic:	performance tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 11:28:52 CDT 2025