By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,328 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

Deletes way slower on AIX, two questions

P: n/a
Folks

I run a process on both the windows and aix process. The process
consists of many steps most are about the same on both platforms.
However there is one delete statement that is taking forever on aix,
four hours versus 10 minutes. It is like this,

DELETE FROM TPS_REPORTS WHERE
COVER_SHEET not in (SELECT COVER_SHEET FROM TPS_TEMP)

So there really isn't much room for rewriting the query. In fact on
both platforms I get the same plan with very similar weights. Even
when I rewrite it

DELETE FROM TPS_REPORTS as Ini
WHERE EXISTS
(SELECT *
FROM TPS_TEMP AS tech
WHERE Ini.COVER_SHEET = tech.COVER_SHEET );

The plan correctly, I believe, does a full scan as there two million
rows and I expect 99.9 percent to be deleted.

Is there some tuning I should be looking at and what values should
they be given
two millon rows in a table with 5 small var chars and 10 numeric
values?

Would

ALTER TABLE TPS_REPORTS ACTIVATE NOT LOGGED INITIALLY;

help even though this doesn't seem to be needed on windows?
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Bo*******@excite.com (Bob Sparks) wrote in message news:<9b**************************@posting.google. com>...
Folks

I run a process on both the windows and aix process. The process
consists of many steps most are about the same on both platforms.
However there is one delete statement that is taking forever on aix,
four hours versus 10 minutes. It is like this,

DELETE FROM TPS_REPORTS WHERE
COVER_SHEET not in (SELECT COVER_SHEET FROM TPS_TEMP)

So there really isn't much room for rewriting the query. In fact on
both platforms I get the same plan with very similar weights. Even
when I rewrite it

DELETE FROM TPS_REPORTS as Ini
WHERE EXISTS
(SELECT *
FROM TPS_TEMP AS tech
WHERE Ini.COVER_SHEET = tech.COVER_SHEET );

The plan correctly, I believe, does a full scan as there two million
rows and I expect 99.9 percent to be deleted.

Is there some tuning I should be looking at and what values should
they be given
two millon rows in a table with 5 small var chars and 10 numeric
values?

Would

ALTER TABLE TPS_REPORTS ACTIVATE NOT LOGGED INITIALLY;

help even though this doesn't seem to be needed on windows?


Check your system temporary tablespace. Consider making it SMS on a
volume with lots of freespace. If you want DMS, make sure it is at
least the size of the largest table, and perferably several times as
large.
Nov 12 '05 #2

P: n/a
Bo*******@excite.com (Bob Sparks) wrote in message news:<9b**************************@posting.google. com>...
Folks

I run a process on both the windows and aix process. The process
consists of many steps most are about the same on both platforms.
However there is one delete statement that is taking forever on aix,
four hours versus 10 minutes. It is like this,

DELETE FROM TPS_REPORTS WHERE
COVER_SHEET not in (SELECT COVER_SHEET FROM TPS_TEMP)

So there really isn't much room for rewriting the query. In fact on
both platforms I get the same plan with very similar weights. Even
when I rewrite it

DELETE FROM TPS_REPORTS as Ini
WHERE EXISTS
(SELECT *
FROM TPS_TEMP AS tech
WHERE Ini.COVER_SHEET = tech.COVER_SHEET );

The plan correctly, I believe, does a full scan as there two million
rows and I expect 99.9 percent to be deleted.

Is there some tuning I should be looking at and what values should
they be given
two millon rows in a table with 5 small var chars and 10 numeric
values?

Would

ALTER TABLE TPS_REPORTS ACTIVATE NOT LOGGED INITIALLY;

help even though this doesn't seem to be needed on windows?


Check your system temporary tablespace. Consider making it SMS on a
volume with lots of freespace. If you want DMS, make sure it is at
least the size of the largest table, and perferably several times as
large.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.