keeling schrieb:
> 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.
>
>
>
select count(*)
from dual where exists ( select null from tableX where columnX is null)
will stop to scan tableX as soon as first null is found. However, it may
still be absolutely ineffective, if your table is huge and null values
are rare. I would definitely looking into direction of fbi ( case when
columnX is null then 0 else null end) and avoid fts on this table, if
your maintenance is of recurring character.
Best regards
Maxim
Received on Fri Jun 15 2007 - 13:49:38 CDT
