[20121025]1条记录会有多少row pieces.txt

[20121025]1条记录会有多少row pieces.txt
jonathan lewis出了一道题目Quiz,参考这个链接:
http://jonathanlewis.wordpress.com/2012/03/30/quiz-night-17/
create table t1 (
        col000  number(1),
        col001  number(1),
        col002  number(1),
...
        col997  number(1),
        col998  number(1),
        col999  number(1),
        constraint t1_pk primary key (col000)
)
;
How many row pieces might that row consist of ?
自己做一些测试:
1.环境如下:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


2.建表方法:


spool a.sql


select 'create table t1 (' from dual 


union all


select 'col'||lpad(rownum-1,3,'0')||' number(1),' from dual connect by level


union all 


select 'constraint t1_pk primary key (col000));' from dual ;


spool b.sql


select 'INSERT into t1 VALUES (' from dual


union all


select '1,' from dual connect by level


union all


select ' 1);' from dual ;


SQL> select rowid from t1;


ROWID


------------------


AAAcRpAAEAAAAIPAAD


SQL> @ lookup_rowid AAAcRpAAEAAAAIPAAD


    OBJECT       FILE      BLOCK        ROW


---------- ---------- ---------- ----------


    115817          4        527          3




SQL> alter system checkpoint;


System altered.


BBED> set dba 4,527


        DBA             0x0100020f (16777743 4,527)


BBED> map /v


 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)


 Block: 527                                   Dba:0x0100020f


------------------------------------------------------------


 KTB Data Block (Table/Cluster)


.....


 sb2 kdbr[4]                                @166


 ub1 freespace[4984]                        @174


 ub1 rowdata[3030]                          @5158


 ub4 tailchk                                @8188


--我仅仅插入一条记录,可以发现sb2 kdbr[4] @166,有4个目录.


BBED> p *kdbr[0]


rowdata[2262]


-------------


ub1 rowdata[2262]                           @7420     0x04


BBED> x /rn


rowdata[2262]                               @7420


-------------


flag@7420: 0x04 (KDRHFL)


lock@7421: 0x01


cols@7422:  255


col    0[2] @7423: 1


col    1[2] @7426: 1


........


col  253[2] @8182: 1


col  254[2] @8185: 1


--一个行目录有255个字段.


2.重新测试(如果插入几个字段,在修改其他呢?):


SQL> drop table t1 purge;


Table dropped.


SQL> @a.sql


Table created.


SQL> insert into t1(col000) values(1);


SQL> commit;


SQL> select rowid from t1;


ROWID


------------------


AAAcS5AAEAAAAIPAAA


SQL> @ lookup_rowid AAAcS5AAEAAAAIPAAA


    OBJECT       FILE      BLOCK        ROW


---------- ---------- ---------- ----------


    115897          4        527          0




现在执行如下:


begin


for i in 255..999 loop


 execute immediate 'update t1 set col'||i||'=0';


 end loop;


commit;


end;


/


SQL> alter system checkpoint;


System altered.


BBED> set dba 4,527


        DBA             0x0100020f (16777743 4,527)


BBED> map /v


 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)


 Block: 527                                   Dba:0x0100020f


------------------------------------------------------------


 KTB Data Block (Table/Cluster)


....


 sb2 kdbr[1]                                @118


 ub1 freespace[8050]                        @120


 ub1 rowdata[18]                            @8170


 ub4 tailchk                                @8188


BBED> p *kdbr[0]


rowdata[0]


----------


ub1 rowdata[0]                              @8170     0x28


BBED> x /rn


rowdata[0]                                  @8170


----------


flag@8170: 0x28 (KDRHFF, KDRHFH)


lock@8171: 0x02


cols@8172:    1


nrid@8173:0x0100020b.0


col    0[2] @8179: 1


--nrid表示其他信息在0x0100020b.0,在dba=0x0100020b,row=0的位置.


BBED>  set dba 0x0100020b


        DBA             0x0100020b (16777739 4,523)


BBED> map /v


 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)


 Block: 523                                   Dba:0x0100020b


------------------------------------------------------------


 KTB Data Block (Table/Cluster)


...


 sb2 kdbr[1]                                @142


 ub1 freespace[7785]                        @144


 ub1 rowdata[259]                           @7929


 ub4 tailchk                                @8188


--仅仅一个行目录.


BBED> x /rn


rowdata[0]                                  @7929


----------


flag@7929: 0x00 (NONE)


lock@7930: 0x01


cols@7931:    1


nrid@7932:0x0100020c.0


col    0[0] @7938: *NULL*


--注意仅仅一个字段,内容为空.也就是表的col001字段.


SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 1');


PL/SQL procedure successfully completed.


SQL> column segment_name format a20


SQL> select segment_name,segment_subtype,bytes,blocks,extents,initial_extent,next_extent from dba_segments where wner=user and segment_name='T1';


SEGMENT_NAME         SEGMENT_SU      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT


-------------------- ---------- ---------- ---------- ---------- -------------- -----------


T1                   ASSM          6291456        768         21          65536     1048576


--可以发现占用768块,extents=21.


SQL> select segment_name,segment_type,extent_id,file_id,block_id,bytes,blocks,relative_fno from dba_extents where wner=user and segment_name='T1';


SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO


-------------------- ------------------ ---------- ---------- ---------- ---------- ---------- ------------


T1                   TABLE                       0          4        520      65536          8            4


T1                   TABLE                       1          4        552      65536          8            4


T1                   TABLE                       2          4        568      65536          8            4


T1                   TABLE                       3          4        584      65536          8            4


T1                   TABLE                       4          4        608      65536          8            4


T1                   TABLE                       5          4        624      65536          8            4


T1                   TABLE                       6          4        632      65536          8            4


T1                   TABLE                       7          4       1792      65536          8            4


T1                   TABLE                       8          4       1800      65536          8            4


T1                   TABLE                       9          4       1808      65536          8            4


T1                   TABLE                      10          4       1816      65536          8            4


T1                   TABLE                      11          4       1824      65536          8            4


T1                   TABLE                      12          4       1832      65536          8            4


T1                   TABLE                      13          4       1840      65536          8            4


T1                   TABLE                      14          4       1848      65536          8            4


T1                   TABLE                      15          4       1856      65536          8            4


T1                   TABLE                      16          4        640    1048576        128            4


T1                   TABLE                      17          4        768    1048576        128            4


T1                   TABLE                      18          4        896    1048576        128            4


T1                   TABLE                      19          4       1024    1048576        128            4


T1                   TABLE                      20          4       1152    1048576        128            4


21 rows selected.


--仅仅1条记录占用这么多块.


SQL> set autotrace traceonly


--注意我使用hint,通过索引访问


SQL> select /*+ index(t1) */ col999 from t1 where col000=1;


Execution Plan


----------------------------------------------------------


Plan hash value: 2347959165


-------------------------------------------------------------------------------------


| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |


-------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT            |       |     1 |     5 |     1   (0)| 00:00:01 |


|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     5 |     1   (0)| 00:00:01 |


|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |


-------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   2 - access("COL000"=1)


Statistics


----------------------------------------------------------


          1  recursive calls


          0  db block gets


        747  consistent gets


          0  physical reads


          0  redo size


        523  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


          1  rows processed


--访问col999,consistent gets高达747.




SQL> select /*+ index(t1) */ col001 from t1 where col000=1;


Execution Plan


----------------------------------------------------------


Plan hash value: 2347959165


-------------------------------------------------------------------------------------


| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |


-------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00:01 |


|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    16 |     1   (0)| 00:00:01 |


|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |


-------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   2 - access("COL000"=1)




Statistics


----------------------------------------------------------


          0  recursive calls


          0  db block gets


          3  consistent gets


          0  physical reads


          0  redo size


        522  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


          1  rows processed


SQL> select /*+ index(t1) */ col002 from t1 where col000=1;




Execution Plan


----------------------------------------------------------


Plan hash value: 2347959165


-------------------------------------------------------------------------------------


| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |


-------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00:01 |


|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    16 |     1   (0)| 00:00:01 |


|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |


-------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   2 - access("COL000"=1)




Statistics


----------------------------------------------------------


          0  recursive calls


          0  db block gets


          4  consistent gets


          0  physical reads


          0  redo size


        522  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


          1  rows processed


--col002需要4个consistent gets,col001需要3个consistent gets.说明存在行链接情况.


alter system flush buffer_cache; --刷新buffer cache.


alter session set events '10046 trace name context forever, level 12';


select /*+ index(t1) */ col255 from t1 where col000=1;


alter session set events '10046 trace name context off';


$ grep 'db file sequential read' /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_7578.trc| grep obj#=115897| tail -10


WAIT #2: nam='db file sequential read' ela= 25 file#=4 block#=859 blocks=1 obj#=115897 tim=1351136716474142


WAIT #2: nam='db file sequential read' ela= 20 file#=4 block#=863 blocks=1 obj#=115897 tim=1351136716474222


WAIT #2: nam='db file sequential read' ela= 22 file#=4 block#=867 blocks=1 obj#=115897 tim=1351136716474344


WAIT #2: nam='db file sequential read' ela= 19 file#=4 block#=871 blocks=1 obj#=115897 tim=1351136716474422


WAIT #2: nam='db file sequential read' ela= 19 file#=4 block#=875 blocks=1 obj#=115897 tim=1351136716474503


WAIT #2: nam='db file sequential read' ela= 19 file#=4 block#=879 blocks=1 obj#=115897 tim=1351136716474567


WAIT #2: nam='db file sequential read' ela= 16 file#=4 block#=883 blocks=1 obj#=115897 tim=1351136716474625


WAIT #2: nam='db file sequential read' ela= 16 file#=4 block#=887 blocks=1 obj#=115897 tim=1351136716474681


WAIT #2: nam='db file sequential read' ela= 15 file#=4 block#=891 blocks=1 obj#=115897 tim=1351136716474736


WAIT #2: nam='db file sequential read' ela= 15 file#=4 block#=895 blocks=1 obj#=115897 tim=1351136716474795


$ grep 'db file sequential read' /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_7578.trc| grep obj#=115897| wc


    256    3328   27716


--读取了256次表T1.另外可以看到assm管理模式插入到后面时数据块的分配情况,相隔4块.(859->863->867...).


BBED> set dba 4,895


        DBA             0x0100037f (16778111 4,895)


BBED> p *kdbr[0]


rowdata[0]


----------


ub1 rowdata[0]                              @7675     0x00


BBED> x /rnnnn


rowdata[0]                                  @7675


----------


flag@7675: 0x00 (NONE)


lock@7676: 0x01


cols@7677:    1


nrid@7678:0x01000340.0


col    0[1] @7684: 0


--正好对应col255的值.


3.一点疑问:


前面我们执行select /*+ index(t1) */ col999 from t1 where col000=1;仅仅747逻辑读,按照前面的介绍,都是1块保存1个字段,按照道理


逻辑读应该在1000以上.在做如下测试看看.


alter system flush buffer_cache; --刷新buffer cache.


alter session set events '10046 trace name context forever, level 12';


select /*+ index(t1) */ col255 from t1 where col000=1;


alter session set events '10046 trace name context off';


$ grep 'db file sequential read' /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_17897.trc| grep obj#=115897|wc


    746    9698   80910


--确实读取746次.这样讲有些块应该保存几个字段.


--取出块


$ grep 'db file sequential read' test_ora_17897.trc| grep obj#=115897|cut -d"=" -f5 | cut -d' ' -f1 > /tmp/aa1.txt


--使用vi编辑aa1.txt,^M相当于回车,使用ctrl-v+ctrl+M输入.


:%s/^/set dba 4,/


:%s+$+^Mp *kdbr[0]^Mx /rnnnnn+


开头加入


set count 8192


set width 210


结尾加入


quit


参数文件里面如下:


$ cat bbedreadonly.par


blocksize=8192


listfile=/home/oracle11g/bbed/filelist.txt


mode=browse


PASSWORD=blockedit


spool=yes


$ /u01/app/oracle11g/product/11.2.0/db_1/bin/bbed parfile=bbedreadonly.par cmdfile=/tmp/aa1.txt


--检查log.bbd文件.


BBED> set dba 4,1171


    DBA             0x01000493 (16778387 4,1171)


BBED> p *kdbr[0]


rowdata[0]


----------


ub1 rowdata[0]                              @7675     0x04


BBED> x /rnnnnn


rowdata[0]                                  @7675


----------


flag@7675: 0x04 (KDRHFL)


lock@7676: 0x01


cols@7677:  255


col    0[1] @7678: 0


col    1[1] @7680: 0


col    2[1] @7682: 0


...


col  244[1] @8166: 0


col  245[1] @8168: 0


col  246[1] @8170: 0


col  247[1] @8172: 0


col  248[1] @8174: 0


col  249[1] @8176: 0


col  250[1] @8178: 0


col  251[1] @8180: 0


col  252[1] @8182: 0


col  253[1] @8184: 0


col  254[1] @8186: 0


--可以发现读取的最后1块,dba=4,1171,保存最后255个字段. 999-255+1=745,这么讲col745~colcol999在一个块中(dba=4,1171)


SQL> set autotrace traceonly


SQL> select /*+ index(t1) */ col745 from t1 where col000=1;


Execution Plan


----------------------------------------------------------


Plan hash value: 2347959165


-------------------------------------------------------------------------------------


| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |


-------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT            |       |     1 |     5 |     1   (0)| 00:00:01 |


|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     5 |     1   (0)| 00:00:01 |


|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |


-------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   2 - access("COL000"=1)




Statistics


----------------------------------------------------------


          0  recursive calls


          0  db block gets


        747  consistent gets


          0  physical reads


          0  redo size


        523  bytes sent via SQL*Net to client


        520  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


          1  rows processed


--逻辑读与执行select /*+ index(t1) */ col999 from t1 where col000=1;一样.


总结:


1.建立表字段太多不好,如果太多超过255,可能的话可以根据业务需要拆分多个表.


2.如果一个表的字段很多,操作模式先insert一些字段,然后update后面字段多次,并且是分许多次的情况下,可能出出现这种情况.


我管的系统没有这么多的字段.按照链接的介绍,这种情况出现在像Siebel应用很常见.




				


				
时间： 2024-08-19 09:31:16

		
		


		


	

	
	

		


		
[20121025]1条记录会有多少row pieces.txt的相关文章


								

		

			

                sql-SQL 表A根据表C的记录新增数据，表C有多少条数据，表A的每条记录就新增多少条不重复的记录
			

		

		

									

				问题描述 SQL 表A根据表C的记录新增数据,表C有多少条数据,表A的每条记录就新增多少条不重复的记录 declare @a table ( id int, [no] varchar(8), name varchar(8) ) declare @c table ( [no] varchar(8) ) insert into @a(id,[no],name)values(1,'A','张三') insert into @a(id,[no],name)values(2,'A','李四') inser			

		

	

				

		

			

                asp下sql和access数据库随机取10条记录的代码newid()_应用技巧
			

		

		

									

				MSSQL:select top 10 * from [table] order by newid() ACCESS:  复制代码 代码如下: '以利用rs.move嘛   '如随机取10条   n = 10   '先要判断总记录数是否少于10,若小于10,则有多少取多少 if n>10 rs.recordCount then n=rs.recordCount   dim ranNum   for i = 1 to n   Randomize()   ranNum = int(rs.record			

		

	

				

		

			

                smartforms强制分页时，每分页一次就少显示一条记录
			

		

		

									

				问题描述 smartforms强制分页时,每分页一次就少显示一条记录我是在table中最后一个文本下面建的programlines和commandprogram中输入gv_pagelines"一页显示的记录数输出gv_modgv_count"循环到第几条记录gv_count=gv_count+1.gv_mod=gv_countmodgv_pagelines.command中分页的条件为:gv_mod=0这样处理所分的页数是正确的,但是分页的时候,每分一次页就会多一条记录显示不出来比喻一			

		

	

				

		

			

                mysql 多个事务更新同一条记录产生死锁
			

		

		

									

				问题描述 mysql 多个事务更新同一条记录产生死锁 以下是用show innodb status 查看的死锁信息,都是通过主键索引userId去更新记录,没有其他索引的影响,不应该会产生死锁啊,请大神帮忙分析下原因. 表索引如下 (.... PRIMARY KEY (userId), UNIQUE KEY userId_UNIQUE (userId), UNIQUE KEY userName_UNIQUE USING BTREE (userName) ) ENGINE=InnoDB AUTO_			

		

	

				

		

			

                jqgrid 分页查询-jqGrid 从后台能获取xml数据，但是前台grid数据就是显示不出来！页面有三条记录
			

		

		

									

				问题描述 jqGrid 从后台能获取xml数据,但是前台grid数据就是显示不出来!页面有三条记录 5C 后台打印出来的数据:<Response> <transResult> <resultCode>1</resultCode> <resultInfoDesc>交易处理成功</resultInfoDesc> </transResult> <object class=""PageGrid"			

		

	

				

		

			

                SQL数据库大表有9000条记录，为什么读取需要5秒时间，WinForm？
			

		

		

									

				问题描述 publicstaticDataTableDt(stringsql){try{sql=sql.Replace("rn","");DataTabledt=newDataTable();cmd=newSqlCommand(sql,GetConn());using(sdr=cmd.ExecuteReader(CommandBehavior.CloseConnection)){dt.Load(sdr);}returndt;}catch(ExceptionEx){r			

		

	

				

		

			

                关于dataGridView中选中一行记录 并把这条记录内的每个字段的数据传到不同的textBox内
			

		

		

									

				问题描述 我现在已经查出数据并将其显示于dataGridView内现在我选中一条记录比如这条记录内有学号姓名成绩这三个字段我在dataGridView旁边放了三个TextBox我想实现当我选中后在那个三TextBox内分别显示我选中那条记录的三个字段学号姓名成绩的值intindex=this.dataGridView1.SelectedRows[0].Index;DataTabletable=(DataTable)this.dataGridView1.DataSource;if(index>=0			

		

	

				

		

			

                定位代码，有点缺陷，就是到了最后一条记录的时候，再点击下一条就会报错，回不了开始的记录
			

		

		

									

				问题描述 //查询定位privatevoidbutton7_Click(objectsender,EventArgse){{introw=dataGridView1.Rows.Count;//得到总行数intcell=dataGridView1.Rows[1].Cells.Count;//得到总列数stringstrTxt=textBox4.Text;//得到输入的字符串,并付值给变量Regexr=newRegex(strTxt);//定义一个Regex对象实例for(inti=0;i<row;			

		

	

				

		

			

                一张表有且只有一条记录(续) - 支持插入，并且查询、更新、删除只作用在最后一条记录上
			

		

		

									

				标签 PostgreSQL , 有且只有一条记录 背景 之前写过一篇文档,介绍如何控制某张表有且只有一条记录. <如何实现一张表有且只有一条记录 implement PostgreSQL table have one and only one row> 接下来这个需求与之类似,一张表好像有且只有一条记录,要求这样: 1.支持插入.更新.删除.查询操作, 2.有一个时间字段用来区分这条记录是什么时候插入.更新的. 3.更新只作用在最后一条记录(时间最大的那条)上, 4.查询只返回时间最大的一条记