Re: Interesting problem
Date: Thu, 26 May 2011 09:24:45 -0700 (PDT)
Message-ID: <ad6de169-77c9-4744-92df-db3128d5f0bb_at_y27g2000prb.googlegroups.com>
On 25 Mai, 20:21, Mladen Gogala <n..._at_email.here.invalid> wrote:
> 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.
It completely eludes me why this should be considered strange. On the contrary, not using foreign keys to indicate relationships between tables is something which should only be done in extremely rare cases where it is a performance killer or such. Even with the procedure purge one could use them without CASCADE - it's just that the deletion order in the procedure needs adjusting.
> 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.
Right. I find it surprising that the schema allows for fractional report and session ids. Usually these things get generated by a sequence and are strictly integer only.
Anyway, I couldn't resist playing a bit around and coming up with a
test script which you can find here:
https://gist.github.com/993424
I needed to take the modified procedure from this thread because it took ages. Turns out a naive implementation is pretty quick as well.
Argument explanation is at the head. Have fun!
Cheers
robert Received on Thu May 26 2011 - 11:24:45 CDT