[20130718]資料庫表結構設計的小問題.txt

[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