Home » RDBMS Server » Performance Tuning » index not used
icon7.gif  index not used [message #253499] Tue, 24 July 2007 02:27 Go to next message
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 #253503 is a reply to message #253499] Tue, 24 July 2007 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any version number.
I don't see any formatting.
I don't see any table description.
I don't see any index.
I don't see any plan.
But I see an invalid statement.

Regards
Michel
Re: index not used [message #253527 is a reply to message #253503] Tue, 24 July 2007 03:21 Go to previous messageGo to next message
pradns
Messages: 4
Registered: July 2007
Junior Member
Hi,
Extremly sorry . This is my first post & i was bit urgent..Here it is.
version 9.2.0.4 64bit on Oracle Enterprise Linux 4

SELECT count(*) FROM trip_performance
WHERE (SUBSTR(TRIP_CLOSE_DATE,1,10) BETWEEN '2007-07-04' AND '2007-07-18')


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=22)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IX_TRIP_PERF_RELEASE_DATE' (NON-U
NIQUE) (Cost=7 Card=1581 Bytes=34782)

Table & index descriptions are in the attachement.
I have gathered table & index stats using
dbms_stats.gather_table_stats('PROD','TRIP_PERFORMANCE',CASCADE=>TRUE)

Thanks
Pradeepan
  • Attachment: tab_ind.txt
    (Size: 9.71KB, Downloaded 222 times)
Re: index not used [message #253536 is a reply to message #253527] Tue, 24 July 2007 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ There is nothing urgent in forum
2/ Even if it is urgent for you, it is not for us
3/ Even if it is urgent for you, you have to read the stickies BEFORE posting.

How do you know that the index is not used?
Activate a sql trace and use tkprof to see the actual plan.

Regards
Michel
Re: index not used [message #253548 is a reply to message #253536] Tue, 24 July 2007 03:57 Go to previous messageGo to next message
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 messageGo to next message
Michel Cadot
Messages: 64151
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

Re: index not used [message #253597 is a reply to message #253499] Tue, 24 July 2007 06:34 Go to previous message
pradns
Messages: 4
Registered: July 2007
Junior Member
Michel,
Thanks a lot. It did the job.

Brgds
Pradeepan
Previous Topic: Real Application Testing --> Database Replay
Next Topic: ARCH wait on SENDREQ
Goto Forum:
  


Current Time: Fri Dec 09 19:13:53 CST 2016

Total time taken to generate the page: 0.09146 seconds