Home » RDBMS Server » Performance Tuning » Delete Advice
Delete Advice [message #156554] |
Wed, 25 January 2006 13:23  |
rkl1
Messages: 97 Registered: June 2005
|
Member |
|
|
we need to delete about 2% of rows of a non-partitioned table containing more than 100 million rows. I am just wondering, if the delete would leave the tables with empty blocks randomly and there by lower the performance particulary during the full table scan. Is it worth to move the table to existing tablespace: alter table X move tablesapce Existing. Does it reorganize the rows and space. Of course it would take time and knock out all the indexes which we have to rebuild. What other startegy we could take.
Will aprreciate any info.
Thanks.
|
|
|
|
Re: Delete Advice [message #156605 is a reply to message #156555] |
Thu, 26 January 2006 04:31   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
smartin wrote on Thu, 26 January 2006 06:49 | But it might if the data you are deleting is all neatly packed in continuously.
|
I am prepared to be called a bare-faced-liar, but I'm pretty sure this is false (at least it was last time I tested it, which was a while ago). If the deleted rows are nicely placed so that the blocks in which they reside drop below the PCTUSED threshold, then they will be placed back on the free-list, but that won't help a FTS. FTS reads every block up to the high-water-mark.
This is easily demonstrated with the following script:CREATE TABLE hwmtest AS SELECT * FROM dba_objects;
DELETE FROM hwmtest;
COMMIT;
ALTER SESSION SET SQL_TRACE=true;
SELECT * FROM hwmtest;
ALTER SESSION SET SQL_TRACE=false; Run TK*Prof over the trace file, and it should show 0 rows but several blocks read. (I don't have Oracle at the moment, so cannot include the results).
In 10g, you have the SHRINK clause of ALTER TABLE that will reclaim the blank space - this should be a bit faster than moving it to another tablespace.
smartin wrote on Thu, 26 January 2006 06:49 | Also, if you add rows to that table ever again (assuming normal inserts) then you'll get to reuse that space that was deleted, even if it was scattered around all over the table.
|
Taking my rep into my own hands now, but I'm going to disagree again. Unless the percentage of space used in a block falls below the PCTUSED threshold, it will not go back on the free-list, and the the space will remain unused. This is most likely to happen if the rows are scattered all over the place.
_____________
Ross Leishman
|
|
|
|
|
|
|
|
|
|
|
Re: Delete Advice [message #156756 is a reply to message #156554] |
Fri, 27 January 2006 08:44   |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Jim, yeah it surprised me too and I havn't investigated further, or even looked at the link provided by Mahesh. I don't understand the delete causing more...my test was on 10gR2 if that matters.
Ross, in reading through Jonathan's new CBO book, he does a lot still with manual segment space management. I came into oracle at version 9i, so pretty much skipped right by it and went straight to ASSM, and am inclined to assume ASSM if I'm not careful. But he discusses how ASSM is more likely to scatter data around physically. This is "good" for concurrency issues, but "bad" for clustering issues. Manual may always have a place depending on the situation.
Always more to learn isn't there? Such fun, these are the threads I really like.
|
|
|
Re: Delete Advice [message #156803 is a reply to message #156756] |
Fri, 27 January 2006 22:05  |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
To paraphrase Douglas Adams, I trust AUTOTRACE about as far as I can comfortably spit out a rat.
I wonder if TK*Prof could shed some light on the PIO/LIO issue. Once again, no Oracle DB right now, so I cannot do it myself.
@smartin, thanks for the ref. More reading required...
_____________
Ross Leishman
|
|
|
Goto Forum:
Current Time: Thu May 01 20:50:02 CDT 2025
|