What are the Costs of DEFAULT VALUE in a Column?

Database
Enthusiast

What are the Costs of DEFAULT VALUE in a Column?

Hi,

Today I found out that on our production system we have the follwowing statement being executed hundreds of times per day:

UPDATE TheTable SET TheDate=DATE'1900-01-01' WHERE TheDate IS NULL; 

TheDate is not indexed, which causes for each of the executions a full table scan. 

I am planing to get rid of this UPDATE statements, by altering the table "TheTable" before any data is inserted:

ALTER TABLE TheTable ADD TheDate DEFAULT DATE'1900-01-01'

Afterwards I want to insert about 20 Million rows.

The question I have is: From a performance point of view, how costly is this additional DEFAULT VALUE on column TheDate? Will there be a big performance impact compared to a table version which doesn't have a default value for TheDate? How will it compare with the UPDATE statement? I know, you can't give me concrete numbers, but I would like to have a rough feeling. 

If there are costs involved when inserting into a column with DEFAULT VALUE, how are these costs being caused?

Thanks in Advance,
Roland

Roland Wenzlofsky

Accepted Solutions
Teradata Employee

Re: What are the Costs of DEFAULT VALUE in a Column?

Default value will have negligible cost, might be slightly cheaper than providing a constant for every row during loading. And if compress is specified for the default value, it will not take up space either.
1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: What are the Costs of DEFAULT VALUE in a Column?

Default value will have negligible cost, might be slightly cheaper than providing a constant for every row during loading. And if compress is specified for the default value, it will not take up space either.
Senior Apprentice

Re: What are the Costs of DEFAULT VALUE in a Column?

#1: You should be glad the TheDate is not indexed, otherwise the Update needs to maintain the index, too.

#2: When you add the DEFAULT you better add NOT NULL, too, otherwise you can still insert NULL explicitly.