OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » index not used

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

index not used [message #253499]

Tue, 24 July 2007 02:27

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

Report message to a moderator

Re: index not used [message #253503 is a reply to message #253499]

Tue, 24 July 2007 02:35

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: index not used [message #253527 is a reply to message #253503]

Tue, 24 July 2007 03:21

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 1341 times)

Report message to a moderator

Re: index not used [message #253536 is a reply to message #253527]

Tue, 24 July 2007 03:34

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: index not used [message #253548 is a reply to message #253536]

Tue, 24 July 2007 03:57

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

Attachment: tkprofout.txt
(Size: 1.90KB, Downloaded 1250 times)

Report message to a moderator

Re: index not used [message #253553 is a reply to message #253548]

Tue, 24 July 2007 04:22

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

pradns
Messages: 4
Registered: July 2007

Junior Member

Michel,
Thanks a lot. It did the job.

Brgds
Pradeepan

Report message to a moderator

Previous Topic:	Real Application Testing --> Database Replay
Next Topic:	ARCH wait on SENDREQ

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 07:59:18 CST 2024