Modifying Columns Of Table

User_OCZ1T

Hi We are using version 11.2.0.4 of oracle. We have a requirement in which we have to make ~12 columns from NULL to NOT NULL in one existing transaction table which is daily range partitioned. Its having ~500Gb in size having ~29million rows and ~31 columns in it. We had faced issue during altering a big table in past as that took very long time. So wanted expert suggestion, what will be the best way to achieve this?

Jonathan Lewis

If you're not worried about the table being locked exclusively for a long time then you could use

ALTER TABLE tab1 MODIFY ( c1 NOT NULL, c2 not null, c3 not null);

But Oracle will hold a mode 6 (exclusive) lock on the table while doing a table scan for each of the columns declared.

Going through the "enable novalidate" then "validate" route means that the exclusive lock will be very short and you may be able to run concurrent processes to do all the validations simultaneously.

Regards

Jonathan Lewis

UPDATE - I see that Andrew Sayer has shown an example in another thread of Oracle running the constraint checking SQL in parallel because the table was declared as parallel enabled: DDL-Tuning - How to speed up a DDL - Creating a NOT NULL Constraint

Hans Steijntjes

You could create an explicit NOT NULL constraint with the ENABLE NOVALIDATE option.

ALTER TABLE yourtable ADD CONSTRAINT constraint_name CHECK (your_column_name IS NOT NULL) ENABLE NOVALIDATE;

or

ALTER TABLE your_table MODIFY (your_column_name NUMBER CONSTRAINT constraint_name NOT NULL ENABLE NOVALIDATE);

This command will enforce that for FUTURE statements the column cannot contain NULL values, but it does not check existing values (that is your responsibility).

User_OCZ1T

Thank you so much. If we want to validate and apply the NOT NULL constraints on the existing data too i.e. without NOVALIDATE option, is it advisable to do it one column at a time rather doing all 12 at one shot? And if we can measure the expected DB time for this operation?

Hans Steijntjes

If Oracle has to do a full table scan to verify that a column is not null, it is better to validate all 12 in one shot (no need in reading the full table 12 times).

But if Oracle can use an index to verify that a column contains no NULL's, that you would most likely be better of to validate that column separately. Note that if there is a index on only the column that to be validate, that index will not be used as NULL values are not recorded when all columns in the index are NULL.

Jonathan Lewis

The problem with modifying a column to NOT NULL is that Oracle has to scan every row to check for rows where the column is null. This may mean a full tablescan, though if there is an index that contains the target column and another column that is declared as NOT NULL then if could use an index fast full scan.  While this is going on the table will be locked in share mode (mode 4).  If you try to modify 12 columns in a single alter table statement then Oracle will do a tablescan (or index FFS) for each one separately. You may decide that a sufficient strategy is to modify the 12 columns one at a time (e.g. one per night for 12 nights) to minimise the impact. (See: https://jonathanlewis.wordpress.com/2012/03/02/add-constraint/  )

If the problem is the amount of time the table stays locked then Hans Steijntjes' solution is relevant - for each column add a CHECK constraint enabled but not validated. This will lock the table very briefly as the constraint is added then protect you from future nulls; then enable the constraint and this will check existing data WITHOUT locking the table.

There is a limitation to this strategy - the optimizer had a few clever features that can be used to optimize a query if it sees columns declared as NOT NULL, but these features do not apply when the "not null" requirement is imposed as a constraint rather than a column definition. In your version (and I haven't tested beyond 12.1.0.2 yet) I haven't found a way of adding a NOT NULL definition to a column without seeing Oracle locking the table and checking every row.

Regards

Jonathan Lewis

Jonathan Lewis

Hans,

I don't think there's any way to get around 12 tablescans for 12 NOT NULL constraints.

Declaring 12 columns not null does the 12 scans through internal code, and your strategy for (enable novalidate) followed by (validate) will allow you to add 12 constraints, but there doesn't seem to be any syntax for modifying them to (validate) in one command.

Regards

Jonathan Lewis

Hans Steijntjes

Jonathan,

Thank you for your update.

I did not know this internal working. Assumed the database was 'smart enough' (which it often is).

Regards,

Hans

User_OCZ1T

Thank You Jonathan.

Hans suggested below two ways to add NOT NULL constraints to the column without validating existing data. When you said, having CHECK constraint has the limitation of missing few features of optimizer. Is it the first option below where CHECK constraint is created? And if we will create using the second option(which is not exactly a CHECK constraint) then it will not impose any restriction to optimizer and is advisable then?

Option-1

ALTER TABLE TAB1 ADD CONSTRAINT c_ck CHECK (c1 IS NOT NULL) ENABLE NOVALIDATE;

or

option-2

ALTER TABLE tab1 MODIFY (c1 NUMBER CONSTRAINT c_ck NOT NULL ENABLE NOVALIDATE);

User_OCZ1T

And another question i had , if we will execute the syntax for modifying all ~12 columns in one shot(without NOVALIDATE ENABLE), those FTS on the base table will happen in parallel rather serially, correct?

Actually our requirement is, in our case we are making data sync between two database and to make it fast, we had dropped the constraints so now the data is synchronized already. and now we want to enable the constraint back on those data set in quicker time.

Unknown

Actually our requirement is, in our case we are making data sync between two database and to make it fast, we had dropped the constraints so now the data is synchronized already. and now we want to enable the constraint back on those data set in quicker time.

Please open a NEW thread for that question - it has NOTHING to do with the question you ask in this thread.

You can't 'enable the constraint back' if the constraint didn't exist to begin with. So in your new thread you need to post the DDL for the tables involved.

User_OCZ1T

As Jonathan mentioned , if we are trying to get rid of locking or want to make the alter happen in quick time, so i mentioned the exact scenario here in this post. Its a scenario in which we are adding back the constraints into the newly replicated/synchronized database, in which we have removed the constraints initially to make the replication faster for the first time. So basically locking is okay, and we want to make it happen in quick time.

Jonathan Lewis

933257,

I've learned something new today.

Hans' "option 2" starts by creating only a "NOT NULL" check constraint on the table (if you do a describe of the table the column will not be flagged as NOT NULL), and I didn't think you could get from this check constraint to setting the "NOT NULL" flag on the table without locking the table while validating the constraint.  But if you do something like the following (assumed t1 is created by "select * from all_objects"):

alter table t1 modify (

        timestamp varchar2(19) CONSTRAINT c_ck_timestamp NOT NULL ENABLE NOVALIDATE,

        generated varchar2(1)  CONSTRAINT c_ck_generated NOT NULL ENABLE NOVALIDATE,

        temporary varchar2(1)  CONSTRAINT c_ck_temporary NOT NULL ENABLE NOVALIDATE

)

;

alter table t1 modify CONSTRAINT c_ck_timestamp VALIDATE;

alter table t1 modify CONSTRAINT c_ck_generated VALIDATE;

alter table t1 modify CONSTRAINT c_ck_temporary VALIDATE;

Then the first command will take a brief mode TM mode 6 (exclusive) lock on the table to add the constraints, and the three following commands will take a mode 2 TM lock (and a mode 4 OD lock while doing a tablescan (or lower cost path, if available) to validate the constraint.

You will then end up with "describe t1" showing the three columns as NOT NULL.  I didn't think it was possible to get to this state without holding a mode 4 TM lock for the duration of the scan. Note that I haven't found a way of restricting the operation to a single scan for all 12 of your columns - but adding the constraint enabled but not validated can be done in a single command taking (virtually) no time.

Regards

Jonathan Lewis

P.S.  I don't know whether you'll be able to run up 12 separate sessions each validating a constraint because they will each want to take a mode 4 OD lock - which is a reasonably aggressive level of locking; but since mode 4 is the SHARE lock and you're only reading the data it may be that you'll be able to do it.

It's also possible that for check constraint validation you could declare the table "parallel (degree N)" and find that the tablescan will run in parallel - in principle Oracle is simple executing a query that causes validation to fail if it returns a row - there's no obvious reason why the optimizer shouldn't allow that query to run parallel.

Jonathan Lewis

933257,

If the only constraints we're talking about are NOT NULL constraints (or other very simple check constraints) I don't think that leaving them enabled while copy the data would make any significant difference to performance.

Regards

Jonathan Lewis

Unknown

Its a scenario in which we are adding back the constraints into the newly replicated/synchronized database, in which we have removed the constraints initially to make the replication faster for the first time.

That is NOT what you first said.

You said 'enable' - you did NOT say you dropped the constraints.

And you can't 'add back' a constraint that never existed to begin with.

I suggest that your first step should be to provide values for all of your columns that you plan to make NOT NULL.

Although using ENABLE NOVALIDATE is valid that doesn't mean it is desireable.

For a typical index null values are NOT indexed.

But when you do a query on a 'NOT NULL' column you expect, and your users would expect, to get a value for EVERY ROW.

When you don't get that value it can affect reports that you do - especially for numeric columns. Aggregate values will compute differently if nulls are involved.

Jonathan Lewis

Jonathan Lewis wrote:I've learned something new today.

I found that I couldn't leave it there - I was so sure that in the past I'd been unable to introduce a NOT NULL declaration to a table without seeing severe table locking problems that I decided to run my latest test script backwards a few versions.

On 11.1.0.7 the step to validation the NOT NULL constraint required a share (mode 4) TM lock, which would interfere with any DML attempted against the table.

Original note - with latest update: https://jonathanlewis.wordpress.com/2010/09/05/not-null/

Regards

Jonathan Lewis

jgarry

rp0428 wrote:For a typical index null values are NOT indexed.

What's a typical index?

User_OCZ1T

Thank you Jonathan for the valuable information.

Got your point. So we can make a constraint enable immediately using ENABLE NOVALIDATE, and can make it VALIDATE too afterwards, which will do the FTS in the table. But again if we will see logically the total time consumption in both cases, it will be same as the normal ALTER table modify statement as below. No difference should be there, so wondering there is no benefit, with the option of making  "ENABLE NOVALIDATE" and then VALIDATE it afterwards.

"ALTER TABLE tab1 MODIFY ( c1 NOT NULL);"  VS "alter table tab1 modify CONSTRAINT c_c1 VALIDATE;"

And as you said, i hope both can be done in parallel through multiple sessions triggering ALTER for different columns simultaneously, need to check though. And i was also trying to know if we will execute something as below, oracle will still perform the FTS for all these three columns in three Parallel threads or in serial one after another?

ALTER TABLE tab1 MODIFY ( c1 NOT NULL, c2 not null, c3 not null);

Unknown

What's a typical index?

An index on a single column rather than a composite index which would allow a column value to be null as long as all column values were not null.

Unknown

And as you said, i hope both can be done in parallel through multiple sessions triggering ALTER for different columns simultaneously, need to check though.

As often the case the Oracle docs explain the requirements.

https://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF01001

TABLE LOCK Oracle Database permits DDL operations on a table only if the table can be locked during the operation. Such table locks are not required during DML operations.

Once one session locks the table for DDL other sessions can't lock it.

The key piece of info you haven't provided is whether there is an offline window to do this operation and, if so, how large that window is.

You are NOT going to avoid scanning the data in order to validate the constraint.

What I suggest that you do is further analysis and review to determine ALL of the requirements.

1. 12 columns COULD CONTAIN nulls - do they? how many nulls do each contain? A large number? Or only a few?

2. Do you need/plan to fully populate those not null columns with some value? If so, and they are all entirely null that could result in a LOT of data movement since the current null values only take up one byte in the block each right now. But after you populate you are changing the length of those rows.

3. How big are the rows now? How big will the rows be if you populate 12 columns with values? Big difference between populating a null NUMBER column with a value of zero and populating a null VARCHAR2(100) with 100 bytes/characters of data.

When you increase the size of the row Oracle usually has to move that row to a new location to accomodate the new data. If you do that 12 times then it could move that row 12 times. Now to you see the significance of what Jonathan was saying about 12 full table scans?

4. Have you considered whether the partitions/tablespaces could take advantage of SHRINK or COALESCE? What about compression for partitions with older data?

5. Have you considered whether some of the older data can be archived or moved to a different/history table?

Besides the multiple scan issue already mentioned another KEY FACTOR is whether it might be better to MOVE the partition somewhere else as part of this operation.

For example, by moving a partition to a new tablespace you can incorporate SHRINK/COALESCE/COMPRESSION and populate the NOT NULL values all at the same time with only ONE PASS through the data.

If you have to MOVE the data you would likely be better off moving it to a NEW table already defined as NOT NULL and get all of those benefits at the same time as well as a backup of the current data. No one mentioned it yet but you really should NOT do your planned operation without having a backup of the current table data.

Which brings up the opportunity to:

1. create a new table with the proper data definitions and DEFAULT values for columns that might be null now

2. export the table data

3. import the data into the new table

Those steps can accomplish ALL of the things I mentioned above without making multiple full scans.

Jonathan Lewis

If you're not worried about the table being locked exclusively for a long time then you could use

ALTER TABLE tab1 MODIFY ( c1 NOT NULL, c2 not null, c3 not null);

But Oracle will hold a mode 6 (exclusive) lock on the table while doing a table scan for each of the columns declared.

Going through the "enable novalidate" then "validate" route means that the exclusive lock will be very short and you may be able to run concurrent processes to do all the validations simultaneously.

Regards

Jonathan Lewis

UPDATE - I see that Andrew Sayer has shown an example in another thread of Oracle running the constraint checking SQL in parallel because the table was declared as parallel enabled: DDL-Tuning - How to speed up a DDL - Creating a NOT NULL Constraint

jgarry

rp0428 wrote:What's a typical index?An index on a single column rather than a composite index which would allow a column value to be null as long as all column values were not null.

Hmmm...

TTST> select count(*) from user_indexes;

  COUNT(*)

----------

1299

TTST> select count(*) from (select unique index_name from user_ind_columns group by index_name having count(*) > 1);

  COUNT(*)

----------

1130

I think I did that right, so I have 169 non-composite indices, which compared to 1130 composite indices doesn't seem typical on my schema.  Single-column index might be a better name, especially when one is talking about transaction tables in a schema rather than a DW.

Unknown

YMMV