Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Rebuild Indexes

Re: Rebuild Indexes

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Wed, 25 Feb 2004 13:28:22 -0500
Message-ID: <20040225182822.GA2592@mladen.wangtrading.com>


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_at_jlcomp.demon.co.uk 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_at_SLAC.Stanford.EDU 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_at_SLAC.Stanford.edu
> >
> >
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> 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_at_freelists.org 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
-----------------------------------------------------------------
Received on Wed Feb 25 2004 - 12:25:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US