RE: Mass updates to production tables (NULL to non-NULL)

It's actually being done for some sort of performance, since NULLs are not indexed. Although now it's suspect, since there are so many NULLs...

Thx,
Rich

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Mirsky, Greg [mailto:gmirsky_at_Estee.com]
> Sent: Monday, November 25, 2002 3:45 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Mass updates to production tables (NULL to non-NULL)
>
>
> What about creating a view with nvl(column_name,chr(32)) for
> the columns
> involved?
>
> Greg
>
> -----Original Message-----
> Sent: Monday, November 25, 2002 4:04 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I've got a real hot project (8.1.7.2 on HP/UX 11.0) that
> needs to have NULLs
> converted to spaces on three different columns. Each is a CHAR, so I
> shouldn't need to worry about chaining, since that column's
> full size has
> already been allocated in the block, right? But the first
> column has 1.2M
> NULLs out of 1.45M rows.
>
> My first test was to just UPDATE mytable SET mycol = ' '
> WHERE mycol IS
> NULL, after removing the index on that column. Seeing as
> there were many
> more rows updated than I had anticipated, I was going to test
> the UPDATE
> using a cursor, and committing at every 10K rows (~120 total
> commits) to
> reduce rollback and locking issues.
>
> Thoughts? Since this table is used for time-and-attendance
> and directly
> affects payroll, downtime isn't possible.
>
> TIA!
>
> Rich
>
>
> Rich Jesse System/Database Administrator

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).