Re: Interesting problem

From: Mladen Gogala <no_at_email.here.invalid>
Date: Wed, 25 May 2011 18:21:55 +0000 (UTC)
Message-ID: <pan.2011.05.25.18.21.55_at_email.here.invalid>



On Wed, 25 May 2011 19:40:27 +0200, Robert Klemme wrote:

> Just out of curiosity and hoping to learn something: why was this done
> in a stored procedure with cursors? I figure with an FK on
> report_agency_Sessions.report# ON DELETE CASCADE and maybe a bit of
> analytic SQL (for the p_rowlimit) it should be possible to do this with
> a single DELETE. Even if no FK was used and collecting report# to
> delete was expensive a trigger or a global temporary table could be
> used. Am I missing something?

No, you are not missing anything, foreign key was my original suggestion during the design phase, but the "report_agency_sessions" is a table with an explicit purpose of enforcing uniqueness of reprt#,session# pairs. Both columns together make the primary key. Developers felt, at a time, that the foreign key on the first column of the primary key would not be a good idea, that it would be too strange. As for the cursors and bulk operations, that was the result of my benchmark. This was simply faster than the explicit delete. There can be quite a few rows to delete, so it must be done in a piecewise fashion. Deleting batches of rows using bulk operations turned to be faster than using normal "delete" command, but I am not sure why. I do remember testing it, and it was consistently faster option.

Here are the relevant structure descriptions:

SQL> desc REPORT_AGENCY_SESSIONS
 Name Null? Type

  • --------
    REPORT# NOT NULL NUMBER SESSION# NOT NULL NUMBER
SQL> select column_name,position from user_cons_columns   2 where constraint_name='REPORT_AGENCY_SESSION_PK';

COLUMN_NAME POSITION

------------------------------ ----------
REPORT# 				1
SESSION#				2


The foreign key would have to be on the REPORT# column, which is also the leading PK column.

-- 
http://mgogala.byethost5.com
Received on Wed May 25 2011 - 13:21:55 CDT

Original text of this message