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

Home -> Community -> Usenet -> c.d.o.server -> Re: query enhancement

Re: query enhancement

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 15 Jun 2007 12:10:33 -0700
Message-ID: <1181934633.651050.251890@g4g2000hsf.googlegroups.com>


On Jun 15, 10:35 pm, keeling <jkeelin..._at_yahoo.com> wrote:
> On Jun 15, 11:33 am, keeling <jkeelin..._at_yahoo.com> wrote:
>
>
>
> > On Jun 15, 11:24 am, Frank van Bortel <frank.van.bor..._at_gmail.com>
> > wrote:
>
> > > -----BEGIN PGP SIGNED MESSAGE-----
> > > Hash: SHA1
>
> > > keeling wrote:
> > > > index on this column. As a side note, I'm testing for the presence of
> > > > null, because if found, I update all 'null' values with '-1'. I'm
> > > > doing this because I intend to use this column as part of a primary
> > > > key constraint.
>
> > > Totally unnecessary:
> > > update table set column_a =-1 where column_a is null.
>
> > > Fastest method ever.
> > > Still want to know how many? Use sql%rowcount after the update,
> > > it holds the number of changed records
>
> > > - --
> > > Regards,
> > > Frank van Bortel
>
> > > Top-posting is one way to shut me up...
> > > -----BEGIN PGP SIGNATURE-----
> > > Version: GnuPG v1.4.1 (MingW32)
>
> > > iD8DBQFGctlfLw8L4IAs830RAiINAJ9mmJS93H99YAvN6dY9qQ/SQ31sagCeI81d
> > > P/Ean+971Q9M1wsrFzcKJ1E=
> > > =dgv6
> > > -----END PGP SIGNATURE-----
>
> > some of my customer's table row count approaches 100 million. The
> > approach last suggested has been tried and deemed unacceptable due to
> > duration of update. Note, this functionally exists in a script that
> > upgrades a schema to be compliant with the latest version of our
> > software. customers may run this script repeatedly; I don't want them
> > to incur the pain of doing the update unnecessarily.
>
> this is why i'm looking for a query to find any one row whose specific
> column = null. Hope this makes sense.

ALTER TABLE mytable MODIFY (column NOT NULL);

This will either fail on first NULL value or succeed if there are none (in this case it will ensure NULLs can never be introduced afterwards.) Of course, Oracle will lock the table and full scan it in order to validate the constraint.

Since you are going to update all NULLs to non-NULLs anyway, the fastest way to do it is what Frank suggested - single update statement. No procedural code can beat it. Oracle will full scan the target table anyway, there's nothing you can do to speed it up.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Jun 15 2007 - 14:10:33 CDT

Original text of this message

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