index not used [message #253499] |
Tue, 24 July 2007 02:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pradns
Messages: 4 Registered: July 2007
|
Junior Member |
|
|
Hi all,
The following query does not use my index, but the execution plan shows that it will be used.I have tested this on my test instance & it's working fine.Please help..
SELECT * trip_performance_view WHERE (SUBSTR(TRIP_CLOSE_DATE,1,10) BETWEEN='2007-07-04')
Thanks
Pradeepan
|
|
|
|
|
|
Re: index not used [message #253548 is a reply to message #253536] |
Tue, 24 July 2007 03:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pradns
Messages: 4 Registered: July 2007
|
Junior Member |
|
|
Dear Michel,
I agree that it's not urgent for others. In fact , it is not urgent for me too. I said i was bit urgent in typing all details since i had to attend a meeting.I have been using this forum for years , but i never post a help since i was able to find the solution reading trhough extint posts.I could not find anything related to this particular problem.I have attached the tkprof for your information.
Thanks
Pradeepan
|
|
|
Re: index not used [message #253553 is a reply to message #253548] |
Tue, 24 July 2007 04:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
use cursor_sharing=exact.
Oracle does not use your index because it does not know that you are using 1 and 10 in substr due to your current cursor_sharing parameter it replaces them with variables.
Regards
Michel
[Edit: typo]
[Updated on: Tue, 24 July 2007 06:37] Report message to a moderator
|
|
|
|