[20130718]数据库表结构设计的小问题.txt

感觉很久没有写blog，最近一段时间，忙着安装12c，oracle变化实在太快，许多东西没搞懂，新的东西就出来了。

最近在给别人讲解oracle btree索引时提到，oracle的索引不包含NULL值，如果要查询
select count(*) from t;

如果一个字段有索引，但是没有定义not null，oracle在执行以上语句时不会使用索引的，当然解决方法很简单，就是包含一个非NULL的字段，或者建立一个函数索引，我给别人讲解时：

只要建立一个函数索引，就可以解决这个问题。

create index i_t_id on (id,1);

结果别人提示应该使用0,而不是1.实际上这个是一个细节问题，至少我以前没有认真考虑这些小细节。

SQL> select dump(0,16),dump(1,16) from dual ;
DUMP(0,16)      DUMP(1,16)
--------------- -----------------
Typ=2 Len=1: 80 Typ=2 Len=2: c1,2

--可以看到0，仅仅占用1个字节，而1占用2个字节，这样使用0建议的函数索引要比使用1建立的函数索引要小一些。

这样就引申一个数据库表结构设计的问题,在许多项目设计中，存在许多表示status,flag这样的信息，这样字段如果使用number类型，很明显是一个不好的设计，像这样表示status,flag的字段最好的使用varchar2(1)类型。(正好最近在优化的一个项目，里面这些字段使用的都是整形，所以由感而发).

给自己找一个理由,扩展一些探究:

1.测试环境:

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> create table t (id number,name varchar2(10));

Table created.

SQL> insert into t values (1,'AAA');

1 row created.

SQL> commit ;

Commit complete.

SQL> select ora_rowscn ,rowid ,t.* from t;

ORA_ROWSCN ROWID                      ID NAME

---------- ------------------ ---------- --------------------

3239076231 AABDrWAAEAAAACDAAA          1 AAA

SQL> @lookup_rowid AABDrWAAEAAAACDAAA

    OBJECT       FILE      BLOCK        ROW DBA

---------- ---------- ---------- ---------- --------------------

    277206          4        131          0 4,131

2.使用bbed查看，使用前执行alter system checkpoint ;

BBED> set dba  4,131

        DBA             0x01000083 (16777347 4,131)

BBED> p *kdbr[0]

rowdata[0]

----------

ub1 rowdata[0]                              @8178     0x2c

BBED> x /rnc

rowdata[0]                                  @8178

----------

flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8179: 0x01

cols@8180:    2

col    0[2] @8181: 1

col    1[3] @8184: AAA

--  *kdbr[0] 的地址 8178.

3.修改记录看看。

SQL> update t set name='BBB' where id=1;

1 row updated.

SQL> commit ;

Commit complete.

SQL> alter system checkpoint;

System altered.

--使用bbed观察:

BBED> set dba  4,131

        DBA             0x01000083 (16777347 4,131)

BBED> p *kdbr[0]

rowdata[0]

----------

ub1 rowdata[0]                              @8178     0x2c

BBED> x /rnc

rowdata[0]                                  @8178

----------

flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8179: 0x02

cols@8180:    2

col    0[2] @8181: 1

col    1[3] @8184: BBB

-- 可以发现 *kdbr[0] 的地址 8178. 可以发现update仅仅覆盖原来的位置，将原来的'AAA'变成了'BBB'.
-- 补充测试执行如下，结果也一样.update t set id=2, name='CCC'  where id=1;

4.在修改记录，保持记录的长度不变。

--name修改'CCCC',id=0,这样记录的长度保持不变。

SQL> update t set id=0, name='CCCC'  where id=1;

1 row updated.

SQL> commit ;

Commit complete.

SQL> alter system checkpoint;

System altered.

--bbed观察:

BBED> set dba  4,131

        DBA             0x01000083 (16777347 4,131)

BBED> p *kdbr[0]

rowdata[0]

----------

ub1 rowdata[0]                              @8168     0x2c

BBED> x /rnc

rowdata[0]                                  @8168

----------

flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8169: 0x01

cols@8170:    2

col    0[1] @8171: 0

col    1[4] @8173: CCCC

BBED> set offset 8178

        OFFSET          8178

BBED> x /rnc

rowdata[10]                                 @8178

-----------

flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8179: 0x00

cols@8180:    2

col    0[2] @8181: 1

col    1[3] @8184: BBB

--可以发现实际上修改的 *kdbr[0]指向了8168,而原来的地方保持修改前的信息。

5.如果一条记录很长，再修改后如果长度不一样，无法再容下新记录会怎样呢?

SQL> drop table t purge ;

Table dropped.

SQL> create table t (id number,name varchar2(4000));

Table created.

SQL> insert into t values (1,lpad('A',3000,'A'));

1 row created.

SQL> insert into t values (2,lpad('B',3000,'B'));

1 row created.

SQL> commit ;

Commit complete.

SQL> select ora_rowscn ,rowid ,t.id from t;

ORA_ROWSCN ROWID                      ID

---------- ------------------ ----------

3239078823 AABDrbAAEAAAACDAAA          1

3239078823 AABDrbAAEAAAACDAAB          2

SQL> @lookup_rowid AABDrbAAEAAAACDAAA

    OBJECT       FILE      BLOCK        ROW DBA

---------- ---------- ---------- ---------- --------------------

    277211          4        131          0 4,131

--记录一下数据的位置

BBED> set dba  4,131

        DBA             0x01000083 (16777347 4,131)

BBED> p kdbr

sb2 kdbr[0]                                 @118      5079

sb2 kdbr[1]                                 @120      2070

SQL> update t set name=lpad('C',4000,'C')  where id=1;

1 row updated.

SQL> commit ;

Commit complete.

SQL> alter system checkpoint;

System altered.

--bbed观察。数据还能容的下，注意观察对比！

--kdbr[0]= 5079 修改后变成 1070

--kdbr[1]= 2070 修改后变成 5079

--好像做一个"块内重整"(不知道专业术语应该叫什么),并没有出现行链接的情况。

BED> set dba  4,131

        DBA             0x01000083 (16777347 4,131)

BBED> p kdbr

sb2 kdbr[0]                                 @118      1070

sb2 kdbr[1]                                 @120      5079