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,
Solved! Go to Solution.
#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.