[20121019]8k数据块到底能放多少行记录.txt

[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.
--当然这种极端的情况,在实际上可能很难遇到.或者根本不可能遇到.

				
时间: 2024-10-23 10:27:13

[20121019]8k数据块到底能放多少行记录.txt的相关文章

[20150720]为什么8K数据块Hakan Factor=736

[20150720]为什么8K数据块Hakan Factor=736.txt --前几天被别人问及这个问题,还真不好回答. --仔细思考,我觉得与行迁移有关,行迁移发生时,rowid不会变化.数据信息被移动另外的块,在块内保留一个指针. --也就是讲最小这条记录仅仅包含一个rowid指针. --还是通过例子来说明情况: 1.建立测试环境: SCOTT@test> @&r/ver1 PORT_STRING                    VERSION        BANNER ---

c#对数据表格控件已选行记录的调用

问题描述 c#对数据表格控件已选行记录的调用 public partial class FrmMainfo : Form { public FrmMainfo() { InitializeComponent(); } string connStr = ConfigurationManager.ConnectionStrings[""str""].ConnectionString; private void button2_Click(object sender Ev

【体系结构】Oracle数据块详解

Oracle数据块详解 操作系统块是操作系统读写的最小操作单元,也是操作系统文件的属性之一.当创建一个Oracle数据库时,选择一个基于操作系统块的整数倍大小作为Oracle数据库块的大小.Oracle数据库读写操作则是以Oracle块为最小单位,而非操作系统块. 数据库块也称逻辑块或Oracle块,它对应磁盘上一个或多个物理块,它的大小由初始化参数DB_BLOCK_SIZE决定,可以定义数据块为2K.4K.8K.16K.32K甚至更大,默认Oracle块大小是8K.若一旦设置了Oracle数据

Oracle数据块实现原理深入解读_oracle

下午在学习oracle 10g r2 concepts 在这留一笔. Oracle对数据库数据文件(datafile)中的存储空间进行管理的单位是数据块(data block).数据块是数据库中最小的(逻辑)数据单位.与数据块对应的,所有数据在操作系统级的最小物理存储单位是字节(byte).每种操作系统都有一个被称为块容量(block size)的参数.Oracle每次获取数据时,总是访问整数个(Oracle)数据块,而不是按照操作系统块的容量访问数据. 数据库中标准的数据块(data bloc

HDFS源码分析心跳汇报之数据块汇报

        在<HDFS源码分析心跳汇报之数据块增量汇报>一文中,我们详细介绍了数据块增量汇报的内容,了解到它是时间间隔更长的正常数据块汇报周期内一个smaller的数据块汇报,它负责将DataNode上数据块的变化情况及时汇报给NameNode.那么,时间间隔更长的正常数据块汇报都做了些什么呢?本文,我们将开始研究下时间间隔更长的正常数据块汇报.         首先,看下正常数据块汇报是如何发起的?我们先看下BPServiceActor工作线程的offerService()方法: /*

HDFS源码分析心跳汇报之数据块增量汇报

        在<HDFS源码分析心跳汇报之BPServiceActor工作线程运行流程>一文中,我们详细了解了数据节点DataNode周期性发送心跳给名字节点NameNode的BPServiceActor工作线程,了解了它实现心跳的大体流程:         1.与NameNode握手:               1.1.第一阶段:获取命名空间信息并验证.设置:               1.2.第二阶段:DataNode注册:         2.周期性调用sendHeartBeat

HDFS源码分析数据块复制监控线程ReplicationMonitor(一)

        ReplicationMonitor是HDFS中关于数据块复制的监控线程,它的主要作用就是计算DataNode工作,并将复制请求超时的块重新加入到待调度队列.其定义及作为线程核心的run()方法如下: /** * Periodically calls computeReplicationWork(). * 周期性调用computeReplicationWork()方法 */ private class ReplicationMonitor implements Runnable

C语言 以数据块的形式读写文件详解及实现代码_C 语言

fgets() 有局限性,每次最多只能从文件中读取一行内容,因为 fgets 遇到换行符就结束读取.如果希望读取多行内容,需要使用 fread 函数:相应地写入函数为 fwrite. fread() 函数用来从指定文件中读取块数据.所谓块数据,也就是若干个字节的数据,可以是一个字符,可以是一个字符串,可以是多行数据,并没有什么限制.fread() 的原型为: size_t fread ( void *ptr, size_t size, size_t count, FILE *fp ); fwri

[20161123]oracle数据块类型.txt

[20161123]oracle数据块类型.txt --oracle 数据块有许多类型,自己平时很少关注与记忆,自己做一个归纳总结: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------------------