| 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')
|
|
|
|
|
|
| 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
|
|
|
|
|
|
| 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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
