Sequence numbers arriving out of order can be easily resolved with:
lock table tabx in exclusive mode;
select seq.nextval into :local_var from dual;
insert into tabx values (:local_var, ...);
That would, actually, be the first thing that some duhvelopers
would come up with. I 've seen things like that with my own two
eyes. Will it suffice to say that a guy attempted to delete
200 million rows from a table, using on-delete key trigger in
Oracle*Forms? He was very surprised when the application was slow.
Of course, commit was executed only when the user explicitly hits
the commit key. User goes to lunch and 200,000,000 are locked
with select for update, automatically done by Oracle*Forms. Things
like that make sure that sequence number are ordered in time, but
they also do wonders for concurrency. To add insult to an injury,
it was a wrong table.
On 02/25/2004 01:08:16 PM, jonathan@(protected) wrote:
>
>
> select seq.nextval into :local_var from dual;
> insert into tabx values (:local_var, ...);
>
> compared to
>
> insert into tabx(seq.nextval, ...)
> returning id_col into :local_var;
>
> The former has a non-zero probability of the
> inserts arriving out of order, and therefore
> wrecking the sequential input.
>
> Regard
> Jonathan Lewis
>
>
>
> ian@(protected) wrote:
> > I 'm curious, how does coding of an application effect index block splits. Does Oracle know because the index is based on a monotonic key that the splits should be 100/0. I wouldn 't call that application coding. Do the 100/0 splits occur unless numbers are lost due to the sequence being kicked out of cache or something else causes values to be skipped?
> >
> > Ian MacGregor
> > Stanford Linear Accelerator Center
> > ian@(protected)
> >
> >
>
>
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe ' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
>
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
