ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130718]数据库表结构设计的小问题.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-07-18 17:00:46 0 删除 编辑
[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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-766631/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2455
  • 访问量
    6258420