[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
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 --> 行目录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
delete from t2;
insert into t2 select null from dual connect by level
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 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.
--当然这种极端的情况,在实际上可能很难遇到.或者根本不可能遇到.