So it’s a fair bit bigger – additional blocks have been used to accommodate the row chaining. Gather some stats and have a look after the update, checking for chained rows at the same time. The data looks the same… select filler_default,count(*) from ncha.tab1 group by filler_default OR: update ncha.tab1 set filler_default = 'EXPAND' The outputs are identical regardless of update or making it NULL-able.ĮITHER: alter table ncha.tab1 modify (filler_default null) Oracle shuffles off and updates every column for you. NOTE: You get exactly the same results whether updating the column, or just making the column NULL-able. So what happens if we actually UPDATE the table? The metadata is intercepting the nulls and converting them to the default on the fly, rather than storing them in the blocks. PL/SQL procedure successfully completed.Īnd look at the data: select filler_default,count(*) from ncha.tab1 group by filler_default Gather some stats and have a look after adding the column. Now lets add the column DEFAULT NOT NULL alter table ncha.tab1 add (filler_default char(1000) default 'EXPAND' not null) Select count(*) CHAINED_ROWS from chained_rows TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE AVG_ROW_LENĪnalyze table tab1 list chained rows into chained_rows Select table_name,num_rows,blocks,avg_space,avg_row_len exec dbms_stats.gather_table_stats('NCHA','TAB1',null,100) Gather some stats and have a look after loading the table. Insert 10,000 rows into the table insert into ncha.tab1 select rownum id, sysdate, 'A' from dual connect by level <= 10000 Run utlchain to create CHAINED_ROWS table for analysis created.Ĭreate table and add constraints create table ncha.tab1 (pk number, c2 timestamp, filler char(1000)) pctfree 1Īlter table ncha.tab1 add constraint tab1_pk primary key (pk) It could take a while too… Until Oracle 11g, that is.įrom Oracle 11G, if you: ALTER TABLE table ADD ( column col-type DEFAULT def NOT NULL ) the default isn’t actually added to the data. This could be a pretty nasty side effect, as lengthening every row in the table will inevitably lead to chained rows, and all of the problem that they can cause. This would mean that every row in the table was updated with the default value. In Oracle, if we add a column to a table which is NOT NULL, we are allowed to do it directly, in a single statement, as long as we supply a DEFAULT value to populate any pre-existing rows.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |