Deletes way slower on AIX, two questions

[Bob Sparks]

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?

[Mark]

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.

[Mark]

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.