Home » RDBMS Server » Performance Tuning » Handling NULL values in the Database (Oracle 10.2.0.4)
Handling NULL values in the Database [message #542270] Mon, 06 February 2012 03:41 Go to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello

We have database with multiple fields containing NULL values
and in many queries we have NVL function which in turn is suppressing the index usage when in fact it is really essential (selectying very few rows from massive data)

instead of creating lot of Function based indexes (NVL) or composite indexes with (nullable_column, constant) I am thinking of settting a default value for most of the fields
In that regard I have some queries :

Which approach is better - setting default value for the fields or updating the fields with default value and modyfing inserts to take care of future data?
Though altering table and modifing column to set default value looks better considering it will take care of data inserted in the future, it will invalidate the subroutines.
I understand in 10g both statement will generate lot of undo (though in 11g, I heard things changed for setting default value of a column)
How to take care of all the queries which are using the criteria 'where column1 IS NULL' or 'where column1 IS NOT NULL'. It will be really difficult task to manually change each and every occurrence of such condition even using user_source

Finally for numeric values say for ID field which starts from 1 onwards 2,3,4 etc, we can set 0 as sensible default so that the performance is not affected
Is there such precaution for varchar2 field purely from performance point of view?

Regards
OraPratap
Re: Handling NULL values in the Database [message #542273 is a reply to message #542270] Mon, 06 February 2012 03:58 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
orapratap wrote on Mon, 06 February 2012 09:41

Which approach is better - setting default value for the fields or updating the fields with default value and modyfing inserts to take care of future data?

You'll still need to do the update if you set the default. You'll probably need to change some of the inserts if you apply the default, since the default only kicks in if the column isn't referenced in the insert statement. If it is referenced and a null value is supplied you'll get null in the table.

orapratap wrote on Mon, 06 February 2012 09:41

Though altering table and modifing column to set default value looks better considering it will take care of data inserted in the future

Not necessarily, see above. However there are advantages to having the default since having it in the data dictionary makes it clearer to everyone.

orapratap wrote on Mon, 06 February 2012 09:41

, it will invalidate the subroutines.

subroutines?

orapratap wrote on Mon, 06 February 2012 09:41

I understand in 10g both statement will generate lot of undo (though in 11g, I heard things changed for setting default value of a column)

Not sure what you mean there.

orapratap wrote on Mon, 06 February 2012 09:41

How to take care of all the queries which are using the criteria 'where column1 IS NULL' or 'where column1 IS NOT NULL'. It will be really difficult task to manually change each and every occurrence of such condition even using user_source

Manual rewrite is the only option.

orapratap wrote on Mon, 06 February 2012 09:41

Finally for numeric values say for ID field which starts from 1 onwards 2,3,4 etc, we can set 0 as sensible default so that the performance is not affected

Why on earth would you want a default for an ID column?

orapratap wrote on Mon, 06 February 2012 09:41

Is there such precaution for varchar2 field purely from performance point of view?

precaution?
Re: Handling NULL values in the Database [message #542274 is a reply to message #542273] Mon, 06 February 2012 04:19 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello cookiemonster

Many Thanks for your reply

cookiemonster : Quote:
You'll still need to do the update if you set the default


Oh Yes, I missed it

cookiemonster : Quote:
subroutines?


Procedures, fuctions, packages

OraPratap : Quote:
Finally for numeric values say for ID field which starts from 1 onwards 2,3,4 etc, we can set 0 as sensible default so that the performance is not affected
cookiemonster : Why on earth would you want a default for an ID column?


I was just giving an example of numeric column as I understand if the default value is far away than the regular values it impact the data selectivity
like if a column has values 1,2,3,4 etc
And if I set default value for the column as 3000 then thwe selectivity for column1 is impacted
So in ideal case the default value has to be close to actual value being used in the column say 0 in above case

Now do we have such impact in varchar fields also wherein if we set default value way beyond actual values it will impact the column selectivity?

OraPratap : Quote:
I understand in 10g both statement will generate lot of undo (though in 11g, I heard things changed for setting default value of a column)

cookiemonster : Quote:
Not sure what you mean there.


Please refer below

http://www.oracle.com/technetwork/articles/sql/11g-schemamanagement-089869.html
Adding Columns with a Default Value
Although happy with this feature alone, Jill ponders another issue somewhat related to the first one. She wants to add the column TAX_CODE but it has to be NOT NULL. Obviously when she adds a not null column to a non-empty table, she has to also specify a default value, 'XX'. So she writes the following SQL:
alter table sales add tax_code varchar2(20) default 'XX' not null;
But she stops there. The table SALES is huge, about 400 million rows. She knows that when she issues the statement, Oracle will add the column alright but will update the value 'XX' in all rows before returning control back to her. Updating 400 million rows will not only take a very long time, it will also fill up the undo segments, generate a large amount of redo, and create massive performance overhead. So Jill has to ask for a "quiet period"--an outage--to make this change. But is there a better approach in Oracle Database 11g?
There is. The above statement will not issue an update to all the records of the table. Well, that's not a problem for new records where the value of the column will be automatically set to 'XX', but when the user selects this column for an existing record, that will return NULL, right?
Wrong, actually. When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary and returns it to the user. So, you kill two birds with one stone: you can define a new column as not null and with a default value and still not incur any penalty for redo and undo generation. Nice.

Regards
OraPratap
Re: Handling NULL values in the Database [message #542933 is a reply to message #542274] Fri, 10 February 2012 19:56 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If there are a lot of NULLs, then most blocks in the table will be re-written so it will be MUCH faster to rebuild the table from scratch (CRATE TABLE AS SELECT). That will solve your UNDO problem and it will be faster.

Regardless of the data type, any "out of bounds" value for the column will do, but a smaller value will take a little less space - that's the only difference I can think of.

Don't think you can do this without re-inspecting ALL your code. Maybe you think you are saving your employer money by not checking and testing, but what happens when it breaks? Not only will it be YOUR fault, but you will look like a rookie and your employer will still make you check and double-check everything to make sure it doesn't happen elsewhere. Besides, you have to find and fix all of the NVLs as well as all of the INSERTs and UPDATEs.

Ross Leishman
Previous Topic: Options for querying on the skewed data
Next Topic: Help - dropping partitions(2 Merged)
Goto Forum:
  


Current Time: Thu Mar 28 12:19:43 CDT 2024