For 11g, I understand that the way that we add columns with default values is different. I'm running 18.104.22.168 on AIX 5.3.
For instance, if I do this:
ALTER TABLE mark_heavily_used_table ADD random_field2 VARCHAR2(10) DEFAULT 'Seven' NOT NULL;
Instead of updating all the rows which already exists in the table, the database instead pings the data dictionary view for the default value for this column.
This is great whenever I come to ADD or MODIFY a column. But wouldn't this increase the time it takes to SELECT from the rows which have columns 'pointing' to the data dictionary view? Instead of a straight SELECT on the table, won't it have to do two operations (go to table, find it's got a default value and then go to data dictionary)?
It has to go to the dictionary anyways, so the only additional time is rewriting the select return results. See January 5, 2012 reply in https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2575782100346318728