SEO

In Oracle, the most efficient way to delete a row (if using a cursor
in SQL PL) is by rowid. Rowid is an internal unique identifier that
Oracle assigns to every row in a database. So you would open your
cursor by selecting rowid for the rows that you want to delete, then
do the delete by rowid.

Is there an equivalent to rowid in UDB for LUW? If not, what's the
most efficient way to delete a row? We're looking at multi-million
row purges, so I want to make sure I get this right.

Thanks.

JudyK wrote:
> In Oracle, the most efficient way to delete a row (if using a cursor
> in SQL PL) is by rowid. Rowid is an internal unique identifier that
> Oracle assigns to every row in a database. So you would open your
> cursor by selecting rowid for the rows that you want to delete, then
> do the delete by rowid.
Uhm.. if you already have the cursor open I trust Oracle supports
DELETE FROM T WHERE CURRENT OF <cursor>
DB2 of course supports this as well.

> Is there an equivalent to rowid in UDB for LUW? If not, what's the
> most efficient way to delete a row? We're looking at multi-million
> row purges, so I want to make sure I get this right.
DB2 9.5 supports a RID() and a RID_BIT() functions which is equivalent
to the ROWID pseudocolumn.

IF you do multi million row purges who may also want to take a look into
defining your table in a way that supports this. E.g. MDC (block
deletes) or range partitioning (detach).

Typically when doing mass deletes the issue is not so much ho wfats it
can be done, but how to control log space (i.e. do you want to do
commits every x rows or not)

If you check out "SQL on Fire" on google you will find an example for
mass deletes which may work for you.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Hello:

i'm not sure if this could help you but what i'm doing when doing an
mass-delete is to think what i have to leave not what I have to
delete,create an MQT with the appropiate select,refresh it and then
load the data with cursor and replace option into the desired table.

Cheers,
Felix

Serge Rielau wrote:
> JudyK wrote:
> > In Oracle, the most efficient way to delete a row (if using a cursor
> > in SQL PL) is by rowid. Rowid is an internal unique identifier that
> > Oracle assigns to every row in a database. So you would open your
> > cursor by selecting rowid for the rows that you want to delete, then
> > do the delete by rowid.
> Uhm.. if you already have the cursor open I trust Oracle supports
> DELETE FROM T WHERE CURRENT OF <cursor>
> DB2 of course supports this as well.
>
> > Is there an equivalent to rowid in UDB for LUW? If not, what's the
> > most efficient way to delete a row? We're looking at multi-million
> > row purges, so I want to make sure I get this right.
> DB2 9.5 supports a RID() and a RID_BIT() functions which is equivalent
> to the ROWID pseudocolumn.
>
> IF you do multi million row purges who may also want to take a look into
> defining your table in a way that supports this. E.g. MDC (block
> deletes) or range partitioning (detach).
>
> Typically when doing mass deletes the issue is not so much ho wfats it
> can be done, but how to control log space (i.e. do you want to do
> commits every x rows or not)
>
> If you check out "SQL on Fire" on google you will find an example for
> mass deletes which may work for you.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

On Dec 13, 3:12 am, FM <felix_manr...@yahoo.com> wrote:
> Hello:
>
> i'm not sure if this could help you but what i'm doing when doing an
> mass-delete is to think what i have to leave not what I have to
> delete,create an MQT with the appropiate select,refresh it and then
> load the data with cursor and replace option into the desired table.
>
> Cheers,
> Felix
>
> Serge Rielau wrote:
> > JudyK wrote:
> > > In Oracle, the most efficient way to delete a row (if using a cursor
> > > in SQL PL) is by rowid. Rowid is an internal unique identifier that
> > > Oracle assigns to every row in a database. So you would open your
> > > cursor by selecting rowid for the rows that you want to delete, then
> > > do the delete by rowid.
> > Uhm.. if you already have the cursor open I trust Oracle supports
> > DELETE FROM T WHERE CURRENT OF <cursor>
> > DB2 of course supports this as well.
>
> > > Is there an equivalent to rowid in UDB for LUW? If not, what's the
> > > most efficient way to delete a row? We're looking at multi-million
> > > row purges, so I want to make sure I get this right.
> > DB2 9.5 supports a RID() and a RID_BIT() functions which is equivalent
> > to the ROWID pseudocolumn.
>
> > IF you do multi million row purges who may also want to take a look into
> > defining your table in a way that supports this. E.g. MDC (block
> > deletes) or range partitioning (detach).
>
> > Typically when doing mass deletes the issue is not so much ho wfats it
> > can be done, but how to control log space (i.e. do you want to do
> > commits every x rows or not)
>
> > If you check out "SQL on Fire" on google you will find an example for
> > mass deletes which may work for you.
>
> > Cheers
> > Serge
> > --
> > Serge Rielau
> > DB2 Solutions Development
> > IBM Toronto Lab


Hi, Judy.

The technique that Serge describes in the presentation he mentions has
worked wonderfully for me on many occasions, so do have a look at it.
I also agree with Felix's point: in some cases, you end up deleting
more rows than you keep, so sometimes it's worth copying/exporting the
rows you want to preserve, "truncating" (uisng one of the two well-
known DB2 ways of truncating) the whole table, and putting the
preserved rows back.

I'd also recommend that you have a look at doing your delete via the
MERGE statement. I've had great success with that as well (i.e., much
smaller query costs). Here's the basic idea:

MERGE INTO X USING (SELECT C1 FROM Y) Y1 ON Y1.C1 = X.C WHEN MATCHED
THEN DELETE;

You can combine the MERGE with Serge's technique by replacing where I
have X, above, with a query that looks like:

(SELECT C FROM X FETCH FIRST 200000 ROWS ONLY)

--Jeff

--Jeff

Here's another approach that I have used:

Let's say you want to delete rows from a very large table based on a
sysdate column, but the number of rows per sysdate is not consistent.
You *don't* want to:
-guess at a WHERE clause based on the sysdate and possibly overfill the
transaction logs
-type SELECT COUNT(*) FROM all afternoon for certain date ranges,
figuring out how much you can delete at a swipe
-UNLOAD the table, selecting only the rows you want, then recreate it

The answer: Figure out how many rows you can delete w/ your logs, then
use row_number(). Let's say you can safely delete up to 200000 rows,
and you only want to delete those rows before 5/1/2005:

lock table assoc_procs in exclusive mode ;

delete
FROM (SELECT sysdate , row_number() OVER
(ORDER BY sysdate)
AS rn FROM big_honker_table)
AS tr WHERE rn BETWEEN 1 and 200000 and sysdate < '5/1/2005' ;

COMMIT ;

Stick this in a loop ala shell script, and let 'er chug away....

HTH

aj

jefftyzzer wrote:
> On Dec 13, 3:12 am, FM <felix_manr...@yahoo.com> wrote:
>> Hello:
>>
>> i'm not sure if this could help you but what i'm doing when doing an
>> mass-delete is to think what i have to leave not what I have to
>> delete,create an MQT with the appropiate select,refresh it and then
>> load the data with cursor and replace option into the desired table.
>>
>> Cheers,
>> Felix
>>
>> Serge Rielau wrote:
>>> JudyK wrote:
>>>> In Oracle, the most efficient way to delete a row (if using a cursor
>>>> in SQL PL) is by rowid. Rowid is an internal unique identifier that
>>>> Oracle assigns to every row in a database. So you would open your
>>>> cursor by selecting rowid for the rows that you want to delete, then
>>>> do the delete by rowid.
>>> Uhm.. if you already have the cursor open I trust Oracle supports
>>> DELETE FROM T WHERE CURRENT OF <cursor>
>>> DB2 of course supports this as well.
>>>> Is there an equivalent to rowid in UDB for LUW? If not, what's the
>>>> most efficient way to delete a row? We're looking at multi-million
>>>> row purges, so I want to make sure I get this right.
>>> DB2 9.5 supports a RID() and a RID_BIT() functions which is equivalent
>>> to the ROWID pseudocolumn.
>>> IF you do multi million row purges who may also want to take a look into
>>> defining your table in a way that supports this. E.g. MDC (block
>>> deletes) or range partitioning (detach).
>>> Typically when doing mass deletes the issue is not so much ho wfats it
>>> can be done, but how to control log space (i.e. do you want to do
>>> commits every x rows or not)
>>> If you check out "SQL on Fire" on google you will find an example for
>>> mass deletes which may work for you.
>>> Cheers
>>> Serge
>>> --
>>> Serge Rielau
>>> DB2 Solutions Development
>>> IBM Toronto Lab
>
>
> Hi, Judy.
>
> The technique that Serge describes in the presentation he mentions has
> worked wonderfully for me on many occasions, so do have a look at it.
> I also agree with Felix's point: in some cases, you end up deleting
> more rows than you keep, so sometimes it's worth copying/exporting the
> rows you want to preserve, "truncating" (uisng one of the two well-
> known DB2 ways of truncating) the whole table, and putting the
> preserved rows back.
>
> I'd also recommend that you have a look at doing your delete via the
> MERGE statement. I've had great success with that as well (i.e., much
> smaller query costs). Here's the basic idea:
>
> MERGE INTO X USING (SELECT C1 FROM Y) Y1 ON Y1.C1 = X.C WHEN MATCHED
> THEN DELETE;
>
> You can combine the MERGE with Serge's technique by replacing where I
> have X, above, with a query that looks like:
>
> (SELECT C FROM X FETCH FIRST 200000 ROWS ONLY)
>
> --Jeff
>
> --Jeff