OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Delete Rows

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

Delete Rows [message #158526]

Sun, 12 February 2006 23:16

vposina
Messages: 2
Registered: February 2006

Junior Member

I deleted old data (almost about 70% of the records) and was expecting a huge performance gain on quesries based on these tables. I do not see much performance improvement.
This is what we did.
1.Delete Data.
2. Ran the analyze.
3. Measured the response time of the queries. Performance did not improve much. Mad

I am missing something. Please advice. Any help is appreciated.

Report message to a moderator

Re: Delete Rows [message #158529 is a reply to message #158526]

Mon, 13 February 2006 00:07

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Presumably you are not expecting big performance gains on INDEXED queries on the table. If an index scan returns 5 rows, it doesn't matter whether the underlying table contains 5 rows or 5 million rows - performance will be pretty much the same.

So, I'm guessing that you were hoping for improvement in Full Table Scans. This is a common and well documented problem. Do a search on High Water Mark (or HWM) in this forum for a full explanation.

In a nutshell, Oracle is still scanning all those empty blocks. To fix the problem, you need to reclaim the emply space. This can be done most easily with the MOVE clause of the ALTER TABLE statement, by moving it to the same tablespace that it is already in. If it is a partitioned table, move (ie. rebuild) one partition at a time.

_____________
Ross Leishman

Report message to a moderator

Re: Delete Rows [message #158591 is a reply to message #158529]

Mon, 13 February 2006 06:22

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

To add what Ross has said,
Please look here ( and search the board for more).

http://www.orafaq.com/forum/m/110688/42800/?srch=tbs_usage#msg_110688
http://www.orafaq.com/forum/m/135009/0/?srch=HWM#msg_135009

Report message to a moderator

Re: Delete Rows [message #158646 is a reply to message #158526]

Mon, 13 February 2006 14:44

vposina
Messages: 2
Registered: February 2006

Junior Member

Ross and Mahesh,

THANKS A LOT.

Your messages really gave me some direction.
Regards

Report message to a moderator

Previous Topic:	In Vs Like
Next Topic:	Query Takes Lots Of time to execute.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 00:23:59 CDT 2024