RE: Rebuild Indexes
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Feb 2004 13:34:21 -0500
LOL. I believe every word in your post.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mladen Gogala
Sent: Wednesday, February 25, 2004 1:28 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Rebuild Indexes
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@xxxxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxx
> >
> >
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
