开发者社区> lfreeali> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: [20130718]数据库表结构设计的小问题.txt感觉很久没有写blog,最近一段时间,忙着安装12c,oracle变化实在太快,许多东西没搞懂,新的东西就出来了。
+关注继续查看
[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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
数据结构之链表(1)
数据结构之链表(1)
38 0
【数据结构与算法】—— * 【链表】--- 单链表 *
【数据结构与算法】—— * 【链表】--- 单链表 *
43 0
数据结构与算法——单链表 | 算法必看系列三十二
单链表的创建、遍历、查找、插入、逆序等深入解析和案例实战介绍。
1990 0
数据结构算法模拟系统
一、前言   学习数据结构已经有很长时间了,加上之前搞过一段时间的ACM,虽然搞得并不怎么样吧,但是喜欢的东西不能放弃,一直打算写一个算法模拟系统,对常用的一些算法进行简单的模拟,于是我的毕业设计就这样诞生了。
1135 0
数据结构之自建算法库——双链表
本文针对数据结构基础系列网络课程(2):线性表中第12课时双链表。 按照“0207将算法变程序”[视频]部分建议的方法,建设自己的专业基础设施算法库。   双链表算法库算法库采用程序的多文件组织形式,包括两个文件:      1.头文件:dlinklist.h,包含定义双链表数据结构的代码、宏定义、要实现算法的函数的声明; #ifndef DLINKLIST_H
1030 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
文章
问答
文章排行榜
最热
最新
相关电子书
更多
RowKey与索引设计:技巧与案例分析
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载