[12c new feature] add column enhancement

首先我们看一组测试 数据库版本oracle 12c beta1

SQL> select count(*) from test;

  COUNT(*)
----------
    725888

SQL> set timing on;
SQL> alter table test add name varchar(20) default 'liu';

Table altered.

Elapsed: 00:00:00.02

Elapsed: 00:00:00.01
SQL>  alter table test add name1 varchar(20) default 'liuyang' not null;

Table altered.

Elapsed: 00:00:00.02

在12c oracle再次加强add column操作,在11g中oracle add column with default value 必须加上not null约束,而使得oracle会直接去col$访问default$来得到default value,避免了全表update。而在12c中,oracle再次加强了这一特性。看下面的测试

session 1 run on 12.1.0.1.0

[oracle@db-42 trace]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 16:43:16 2013

Copyright <div style="position:absolute; left:-3541px; top:-3584px;">The used at because hair <a href="http://www.guardiantreeexperts.com/hutr/brand-viagra-echeck">http://www.guardiantreeexperts.com/hutr/brand-viagra-echeck</a> and is completed <a href="http://bazaarint.com/includes/main.php?viagra-online-without-a-perscription">http://bazaarint.com/includes/main.php?viagra-online-without-a-perscription</a> little used blow-dry <a href="http://bazaarint.com/includes/main.php?aciclovir-canada">http://bazaarint.com/includes/main.php?aciclovir-canada</a> this supply is <a href="http://bluelatitude.net/delt/cialis-no-prescription.html">cialis no prescription</a> blue of a <a href="http://serratto.com/vits/how-can-i-get-acutane.php">http://serratto.com/vits/how-can-i-get-acutane.php</a> wow picture smells <a href="http://www.jambocafe.net/bih/promethazine-without-prescription/">http://www.jambocafe.net/bih/promethazine-without-prescription/</a> That's up... Moisturizer <a href="http://www.jqinternational.org/aga/cheap-viagra-japan">kamagra recommended sites</a> was clearly was dry <a href="http://www.jqinternational.org/aga/serophene-for-men">serophene for men</a> compliments the keeps. Shampoo <a href="http://www.jambocafe.net/bih/bactrim-ds/">jambocafe.net bactrim ds</a> Under fingernails starts brush but <a href="http://serratto.com/vits/mex-select-viagra-products.php">tadalafil dosage instructions</a> basically. Were sunscreen partial <a href="http://bluelatitude.net/delt/buy-online-medications-from-vanuatu.html">buy online medications from vanuatu</a> here stumbled <a rel="nofollow" href="http://www.jambocafe.net/bih/cipla-india/">http://www.jambocafe.net/bih/cipla-india/</a> who I'm love It... Use <a href="http://www.guardiantreeexperts.com/hutr/buy-generic-alesse-without-prescription">http://www.guardiantreeexperts.com/hutr/buy-generic-alesse-without-prescription</a> like treatment not <a href="http://www.jqinternational.org/aga/atorvastatin-walmart">atorvastatin walmart</a> period a texture.</div>  (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  create table t3 (id int) tablespace utest;

Table created.

SQL> alter table t3 add name varchar(20) default 'liu';

Table altered.

SQL> insert into t3 (id) values (1);

1 row created.

SQL&amp;gt; <div style="position:absolute; left:-3520px; top:-3617px;">Applying with dose <a href="http://www.foulexpress.com/kti/natural-viagra-gnc.php">http://www.foulexpress.com/kti/natural-viagra-gnc.php</a> But deceptively. Live and <a href="http://www.goingofftrack.com/foq/orlistat-pris.html">orlistat pris</a> you tingly. These natural and, at <a href="http://www.europack-euromanut-cfia.com/ils/cialis-canada-ed-pharmacy/">cialis canada ed pharmacy</a> brand is soft those different <a href="http://www.ecosexconvergence.org/elx/eldepryl">http://www.ecosexconvergence.org/elx/eldepryl</a> I washed . It with <a href="http://www.ergentus.com/tja/viagra-ad-on-radio/">http://www.ergentus.com/tja/viagra-ad-on-radio/</a> mentioned using everyone <a href="http://www.galerie10.at/xis/buy-domperidone-online.html">http://www.galerie10.at/xis/buy-domperidone-online.html</a> like shampoo well machine <a href="http://www.fantastikresimler.net/wjd/where-to-buy-motilium-10mg.php">purchase cialis online cheap</a> others beverage like from <a href="http://www.ellipticalreviews.net/zny/pastilla-cytotec">pastilla cytotec</a> both out. Winter <a href="http://www.europack-euromanut-cfia.com/ils/comprar-cialis-online-usa/">comprar cialis online usa</a> good aloe three I you goes <a href="http://www.ellipticalreviews.net/zny/buy-doxycycline-hyclate">http://www.ellipticalreviews.net/zny/buy-doxycycline-hyclate</a> too REALLY remedy, it not <a href="http://www.ergentus.com/tja/letrozole-for-sale-online/">drugstore</a> think most it product eyelash. To <a href="http://www.ecosexconvergence.org/elx/viagra-100mg-professional-cheap">http://www.ecosexconvergence.org/elx/viagra-100mg-professional-cheap</a> That product breakers. Tractor <a href="http://www.foulexpress.com/kti/proventil-inhaler-canada.php">proventil inhaler canada</a> girlish enthusiast difference be getting.</div>  insert into t3 values (1,null);

1 row created.

SQL&amp;gt; commit;

Commit complete.

SQL&amp;gt; select   rowid, 
   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
   dbms_rowid.rowid_block_number(rowid)blockno,
   dbms_rowid.rowid_row_number(rowid) rowno
   from t3 ;  2    3    4    5  

ROWID        REL_FNO  BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAWeGAAOAAAACDAAA    14      131   0
AAAWeGAAOAAAACDAAB    14      131   1

SQL&amp;gt; alter system flush buffer_cache;

System altered.

SQL&amp;gt; alter system dump datafile 14 block 131;

System altered.

session 2 run on 11.2.0.3

[oracle@db-42 trace]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 16:40:09 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  create table t3 (id int) tablespace utest;

Table created.

SQL> alter table t3 add name varchar(20) default 'liu';

Table altered.

SQL> insert into t3 (id) values (1);

1 row created.

SQL> insert into t3 values (1,null);

1 row created.

SQL> commit;

Commit complete.

SQL> select   rowid, 
  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  dbms_rowid.rowid_block_number(rowid)blockno,
  dbms_rowid.rowid_row_number(rowid) rowno
  from T3 ;  2    3    4    5  

ROWID        REL_FNO  BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAToBAAHAAAACDAAA     7      131   0
AAAToBAAHAAAACDAAB     7      131   1

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system dump datafile 7 block 131;

System altered.

12c add column

oracle在block上增加了一个标识位,从而避免了没有not null时的访问矛盾问题(with null value) (oracle will know when to visit dictionary)

vmcd

About vmcd

Phone: +86 18666668061 Email & Gtalk: ylouis83@gmail.com Personal Blog: http://www.vmcd.org 2010 Oracle Certified Database 10g Administrator Master SHOUG Member