[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.测试环境:
2.使用bbed查看,使用前执行alter system checkpoint ;
-- 可以发现 *kdbr[0] 的地址 8178. 可以发现update仅仅覆盖原来的位置,将原来的'AAA'变成了'BBB'.
-- 补充测试执行如下,结果也一样.update t set id=2, name='CCC' where id=1;
4.在修改记录,保持记录的长度不变。
--name修改'CCCC',id=0,这样记录的长度保持不变。
--可以发现实际上修改的 *kdbr[0]指向了8168,而原来的地方保持修改前的信息。
5.如果一条记录很长,再修改后如果长度不一样,无法再容下新记录会怎样呢?
感觉很久没有写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