![]() STAT #3 id=1 cnt=1 pid=0 pos=1 obj=66986 op='TABLE ACCESS FULL T_DEFAULT (cr=7 pr=0 pw=0 time=0 us cost=3 size=5 card=1)' The part in the raw trace related to the first row shows that just after my query there are some queries from the data dictionary, like the one from ecol$ just below my query. PL/SQL procedure successfully session set events '10046 trace name context forever, level 12' Ģ have just queried the row which was inserted before the add column operation (whose new column is not updated but stored in the dictionary as metadata) and after that the second row which is inserted after the add column operation (whose new column is updated in the table block). Let's do a simple query and see the trace dbms_stats.gather_table_stats(user,'T_DEFAULT') Now we know that the rows at the time of the add column operation are not updated, how does it return the value for that column? ![]() So, what happens when we query the table after adding a not null column with a default value? This means the metadata updates are only used when adding the column, after that updates and inserts modify the block for that column also. But for subsequent inserts it inserts the default value for the column if no value is supplied. So Oracle does not update the rows when adding the column. Now the trace file shows the second column also. If we insert a new row using the default value into t_default values(2,DEFAULT) ġ row dbms_rowid.rowid_block_number(rowid) from t_default where system dump datafile 4 block 176 ![]() It did not update the blocks with the new column's default value. The trace file shows the first column but not the one we added after. Now let's have a look at what is in the table's blocks for this one dbms_rowid.rowid_block_number(rowid) from t_default where system dump datafile 4 block 172 Let's add a not null column with a default table t_default add(col_default number default 0 not null) Now we have a table with one column which has one row in it. To answer some questions let's see it in table t_default (col1 number) How does this work? How does it return the data if it does not store in the table's blocks? ![]() So adding a new column becomes just a few dictionary operations. Now it does not have to update all the rows, it just keeps the information as meta-data in the dictionary. In 11G when you want to add a not null column with a default value, the operation completes in the blink of an eye (if there are no locks on the table of course). ![]() Oracle had to update all the rows with the default value when adding the column.ġ1G introduced a fast=true feature for adding columns with default values. Before 11G adding new columns with default values to tables with millions of rows was a very time consuming and annoying task. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |