[20121019]8k資料塊到底能放多少行記錄.txt

lfree發表於2012-10-19
[20121019]8k資料塊到底能放多少行記錄.txt

前一陣子聚會,被問及一個8k資料塊能夠放多少行記錄,我記得以前piner的書提高過,73X條.
實際上表sys.tab$的spare1欄位儲存的Hakan Factor，即該表資料塊的最大行號，各種資料塊的大小不同,
spare1的預設值也不一樣。
_______________________________
塊大小  最大行數每塊（spare1）  
_______________________________
2K      178
4K      364
8k      736
16K     1481
32K     2971
_______________________________

自己做一些簡單探究看看:

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

CREATE TABLESPACE USERS DATAFILE 
  '/u01/app/oracle11g/oradata/test/users01.dbf' SIZE 512M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

表空間users使用本地表空間管理的ASSM方式.

1.建立測試例子:
drop table t1 purge ;
create table t1 (a number) pctfree 0;
insert into t1  select null from dual connect by level<=2400;
commit ;

SELECT tab.spare1, obj.object_id, obj.data_object_id, obj.object_name
  FROM SYS.tab$ tab, dba_objects obj
 WHERE tab.obj# = obj.object_id AND tab.dataobj# = obj.data_object_id AND obj.owner = USER AND obj.object_name = 'T1'

    SPARE1  OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- ---------- -------------- ------------
       736     115049         115049 T1

--spare1=736

select DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,count(*) from t1  
group by DBMS_ROWID.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid);

    RFILE#     BLOCK#   COUNT(*)
---------- ---------- ----------
         4        550        201
         4        548        733
         4        549        733
         4        547        733

--可以發現最大1個塊能插入733條,與736很接近.

2.那一個塊看看裡面的儲存,我使用bbed看,先執行alter system checkpoint,這樣才能看到準確的結果.

BBED> set dba 4,547
        DBA             0x01000223 (16777763 4,547)

BBED> map /v
 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
 Block: 547                                   Dba:0x01000223
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18

 struct ktbbh, 72 bytes                     @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    sb2 ktbbhict                            @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[2], 48 bytes            @44

 struct kdbh, 14 bytes                      @100
    ub1 kdbhflag                            @100
    sb1 kdbhntab                            @101
    sb2 kdbhnrow                            @102
    sb2 kdbhfrre                            @104
    sb2 kdbhfsbo                            @106
    sb2 kdbhfseo                            @108
    sb2 kdbhavsp                            @110
    sb2 kdbhtosp                            @112

 struct kdbt[1], 4 bytes                    @114
    sb2 kdbtoffs                            @114
    sb2 kdbtnrow                            @116

 sb2 kdbr[733]                              @118

 ub1 freespace[4405]                        @1584

 ub1 rowdata[2199]                          @5989

 ub4 tailchk                                @8188

--可以發現ub1 rowdata[2199]  @5989,2199/733=3,每天記錄佔用3個位元組.
--ub1 freespace[4405] @1584,可以發現自由空間還有很大的剩餘.4405,如果使用dump /v看也一樣.
--sb2 kdbr[733] @118 --&gt 行目錄7佔用33條.
--所以理論講應該還能儲存更多的行記錄.

3.使用ALTER TABLE  MINIMIZE RECORDS_PER_BLOCK看看:
首先確定這個命令修改有最大行記錄確定還是每塊的最大的記錄數.再建立一個測試表T2.
drop table t2 purge ;
create table t2 (a number) pctfree 0;
insert into t2  select null from dual connect by level<=10;
delete from t2;
insert into t2  select null from dual connect by level<=10;
commit ;

select dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,t2.* from t2;
SQL> select dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,t2.* from t2;

    RFILE#     BLOCK#       ROW#          A
---------- ---------- ---------- ----------
         4        555         10
         4        555         11
         4        555         12
         4        555         13
         4        555         14
         4        555         15
         4        555         16
         4        555         17
         4        555         18
         4        555         19

10 rows selected.

--行號從10開始,最大19.

alter table t2 minimize records_per_block;

SELECT tab.spare1, obj.object_id, obj.data_object_id, obj.object_name
  FROM SYS.tab$ tab, dba_objects obj
 WHERE tab.obj# = obj.object_id AND tab.dataobj# = obj.data_object_id AND obj.owner = USER AND obj.object_name = 'T1'

    SPARE1  OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- ---------- -------------- ------------
     32787     115050         115050 T2

--SPARE1=32787. 32787-32768=19(不是10),所以執行後儲存的是最大行目錄.這樣設定後每塊最多放20條記錄(行記錄從0開始).

4.這樣這個問題就轉化為一塊能容納多少行目錄.
drop table t2 purge ;
create table t2 (a number) pctfree 0;

declare 
 v_newrowid rowid;
begin
 for i in 1..20000 loop
  if i=2000 then
   insert into t2 values(NULL);
  else
   insert into t2 values(null) returning rowid into v_newrowid;
   delete from t2 where rowid=v_newrowid;
  end if;
 end loop;
end;
/

--在一個事務裡面,插入1條記錄佔用1個行目錄,在刪除記錄後,由於在一個事務裡面,不會重用原來的行目錄,這樣最終可以確定能容納多少行目錄.
--為了確定表T2佔用的塊,我選擇i=2000時,插入1條,而不刪除記錄.
commit;
select DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,count(*) from t2
group by DBMS_ROWID.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid);

    RFILE#     BLOCK#   COUNT(*)
---------- ---------- ----------
         4        555          1

alter system checkpoint;

BBED> set dba 4,555
        DBA             0x0100022b (16777771 4,555)

BBED> map /v
 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
 Block: 555                                   Dba:0x0100022b
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18

 struct ktbbh, 72 bytes                     @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    sb2 ktbbhict                            @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[2], 48 bytes            @44

 struct kdbh, 14 bytes                      @100
    ub1 kdbhflag                            @100
    sb1 kdbhntab                            @101
    sb2 kdbhnrow                            @102
    sb2 kdbhfrre                            @104
    sb2 kdbhfsbo                            @106
    sb2 kdbhfseo                            @108
    sb2 kdbhavsp                            @110
    sb2 kdbhtosp                            @112

 struct kdbt[1], 4 bytes                    @114
    sb2 kdbtoffs                            @114
    sb2 kdbtnrow                            @116

 sb2 kdbr[2014]                             @118

 ub1 freespace[0]                           @4146

 ub1 rowdata[4042]                          @4146

 ub4 tailchk                                @8188

--sb2 kdbr[2014] @118 ,說明至少可以儲存2014行目錄.(補充說明:我的測試最大2015)

alter table t2 minimize records_per_block;

SELECT tab.spare1, obj.object_id, obj.data_object_id, obj.object_name
  FROM SYS.tab$ tab, dba_objects obj
 WHERE tab.obj# = obj.object_id AND tab.dataobj# = obj.data_object_id AND obj.owner = USER AND obj.object_name = 'T2'

    SPARE1  OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- ---------- -------------- -----------
     34767     115130         115130 T2

--spare1=34767,34767-32768=1999,相當於可以每塊可以儲存2000條記錄.實際情況如何呢?

SQL> delete from t2;
1 row deleted.

SQL> commit ;
Commit complete.

SQL> insert into t2  select null from dual connect by level<=2400;
2400 rows created.

SQL> commit;
Commit complete.

select DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,count(*) from t2
group by DBMS_ROWID.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid);

    RFILE#     BLOCK#   COUNT(*)
---------- ---------- ----------
         4        555        451
         4        568        733
         4        573        483
         4        575        733
--可以發現做多也就是733條記錄.不能超過這個限制.

5.點陣圖索引的問題,由於每塊(8K資料塊)不能超出736的限制.如果行目錄大於736,建立點陣圖索引會出現什麼情況呢?
還是那上面的例子:

drop table t2 purge ;
create table t2 (a number) pctfree 0;

declare 
 v_newrowid rowid;
begin
 for i in 1..20000 loop
  if i=2000 then
   insert into t2 values(NULL);
  else
   insert into t2 values(null) returning rowid into v_newrowid;
   delete from t2 where rowid=v_newrowid;
  end if;
 end loop;
end;
/

SQL> create bitmap index i_t2_a on t2(a);
create bitmap index i_t2_a on t2(a)
                              *
ERROR at line 1:
ORA-28604: table too fragmented to build bitmap index (16777771,1999,744)

$ oerr ora 28604
28604, 00000, "table too fragmented to build bitmap index (%s,%s,%s)"
// *Cause:  The table has one or more blocks that exceed the maximum number
//          of rows expected when creating a bitmap index. This is probably
//          due to deleted rows. The values in the message are:
//          (data block address, slot number found, maximum slot allowed)
// *Action: Defragment the table or block(s). Use the values in the message
//          to determine the FIRST block affected. (There may be others).

--這裡744表示maximum slot allowed.

select dbms_utility.data_block_address_file(16777771) rfile#,dbms_utility.data_block_address_block(16777771) block# from dual;
    RFILE#     BLOCK#
---------- ----------
         4        555

--執行如下可以解決.

SQL> alter table t2 minimize records_per_block;
Table altered.

SQL> create bitmap index i_t2_a on t2(a);
Index created.

--當然這種極端的情況,在實際上可能很難遇到.或者根本不可能遇到.


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-746749/，如需轉載，請註明出處，否則將追究法律責任。

相關文章