Oracle表中含有255列以上时需要注意的(r12笔记第77天)

今天看JL(Jonathan Lewis)的一篇文章,真是费了不少的脑细胞,玩Oracle几十年的老司机,看问题的角度和深度果然不一样,当时看他的大作《Oracle核心技术》就花了我不少时间,还没有看完,一本薄薄的书能够蕴含如此的能量,做技术到了这个火候,让人深深佩服。

他的一篇博文,标题很简单,就是“255 again”看来是一个很经典的问题,我就简单抓取一些测试的思路和脚本来说说。

原文在链接 https://jonathanlewis.wordpress.com/2017/05/23/255-again/ 如果你的某张表列数超过255个,你就需要注意了,会有一些特别的问题出现,而对于这个问题的模拟,JL提供了一个脚本,会创建320个字段,然后对这个表插入一行数据,更新一行数据,然后根据block的dump来做一个分析和说明,脚本如下:

rem
rem     Script: wide_table_4.sql
rem     Author: Jonathan Lewis
rem     Dated:  May 2017
rem
rem     Last tested
rem             12.2.0.1
rem             12.1.0.2
rem             11.2.0,4
rem
 
set pagesize 0
set feedback off 
spool temp.sql 
prompt create table t1(
 
select
        'col' || to_char(rownum,'fm0000') || '  varchar2(10),'
from
        all_objects
where   rownum <= 320
; 
prompt col0321 varchar2(10)
prompt )
prompt / 
spool off
 
@temp
 
set pagesize 40
set feedback on
 
insert into t1 (col0010, col0280) values ('0010','0280');
commit;
 
update t1 set col0320 ='0320';
commit;
 
column file_no  new_value m_file_no
column block_no new_value m_block_no
 
select
        dbms_rowid.rowid_relative_fno(rowid)    file_no,
        dbms_rowid.rowid_block_number(rowid)    block_no,
        dbms_rowid.rowid_row_number(rowid)      row_no
from
        t1
;
 
alter system flush buffer_cache;

脚本执行后,会创建一个含有320个字段的表,然后对标所在的dump做一个trace。

ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1e54
avsp=0x1e3e
tosp=0x1f13
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1e7a
0x14:pri[1]     offs=0x1e54
block_row_dump:
tab 0, row 0, @0x1e7a
tl: 49 fb: -------- lb: 0x2  cc: 40
nrid:  0x014000a7.0
col  0: *NULL*
col  1: *NULL*
col  2: *NULL*
...
col 37: *NULL*
col 38: *NULL*
col 39: *NULL*
tab 0, row 1, @0x1e54
tl: 38 fb: --H-F--- lb: 0x2  cc: 25
nrid:  0x014000a3.0
col  0: *NULL*
col  1: *NULL*
col  2: *NULL*
...
col 22: *NULL*
col 23: *NULL*
col 24: *NULL*
end_of_block_dump

有几个点需要注意,这个块被分成了两部分,"row 1"所在的那部分这个块的起点(可以通过fb中的标记H,意思就是header),可以从末尾的cc看出涉及的列有25个,行的下一部分可以通过nrid来看,就是nrid: 0x014000a3.0转换过来就是5号数据文件,163号数据块,这个和row 1所在的数据库相同。

如果我们看row 0的时候,会根据基本信息得到,它涉及的列数有40个,40个列都是空的,这一行指向的下行地址是nrid: 0x014000a7.0这个地址转换过来是在5号数据文件的167号块,根据这一行中间的标注(为空),感觉是一个可有可无的部分。我们就继续对5号数据文件的167号块做一个dump,得到的信息如下:

fsbo=0x14
fseo=0x1e76
avsp=0x1e62
tosp=0x1e62
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1e76
block_row_dump:
tab 0, row 0, @0x1e76
tl: 266 fb: -----L-- lb: 0x1  cc: 255
col  0: *NULL*
col  1: *NULL*
col  2: *NULL*
...
col 251: *NULL*
col 252: *NULL*
col 253: *NULL*
col 254: [ 4]  30 33 32 30
end_of_block_dump   

通过上面的信息可以发现,这是行数据的最后一部分,可以通过哪个L得到,(即last).

所以一个初步结论如下:

  1. 一般的insert语句会把使用到的280个列分成两部分(25,255),这个280列可以通过Insert语句看到。

insert into t1 (col0010, col0280) values ('0010','0280'); 2.在updae的场景中,我们把使用到的列从280升到了320

update t1 set col0320 ='0320';所以说在update的场景中,我们可以把列的使用情况从280改进到了320个列,这40个列在orale中会跟255为分界来处理,这样就是(40,295),然后把40列放在原来的数据块中,剩下的把255个列迁移到一个新的块中,所以这样一来,原来列的的分布就很有特点了,分配到了两个块中。后面还有对于ASSM在这个过程中的分析,限于篇幅和脑细胞数量,就暂时告一段落吧。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-05-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏软件测试经验与教训

测试工程师SQL面试题

测试人员工作在工作中会用到SQL来辅助测试,求职时也常常会在笔试环节遇到各种各样的sql设计题目,张老师整理了一些工作中常用的sql知识点,希望对大家有所帮助。

2.2K30
来自专栏杨建荣的学习笔记

一个简单的sql审核案例 (r8笔记第90天)

今天开发的同学发来一封邮件,希望我帮忙对一个sql语句做一个评估。他们也着急要用,但是为了稳妥起见,还是希望我来审核一下,这是一个好的习惯。 打开邮件,看到的语...

31560
来自专栏枕边书

见招拆招-PostgreSQL中文全文索引效率优化

前言 上文 使用PostgreSQL进行中文全文检索 中我使用 PostgreSQL 搭建完成了一套中文全文检索系统,对数据库配置和分词都进行了优化,基本的查询...

62480
来自专栏数据分析

SQL Server 性能优化之——系统化方法提高性能

1. 概述 在比较大的范围内找出能够大幅提高性能的区域,并且专注于分析这个区域,这是最有效的优化SQL Server性能的方式。否则,大量的时间和精力可能被浪费...

53260
来自专栏杨建荣的学习笔记

Oracle和MySQL中短小精悍的SQL

如果让你写一个简单牛叉的SQL,数据库类型不限,你会写出什么样的SQL语句。 Oracle 如果是Oracle,我就写个drop table dual; ...

36650
来自专栏数据和云

【云和恩墨大讲堂】Oracle线上嘉年华第二讲

编辑手记:Oracle线上嘉年华,正在持续分享中。本次的主题是系统割接中的SQL解析问题和结合业务的SQL优化改写技巧。 1 嘉宾介绍 小鱼(邓秋爽) 云和恩...

30660
来自专栏Spark学习技巧

Hive性能优化(全面)

面对这些问题,我们能有哪些有效的优化手段呢?下面列出一些在工作有效可行的优化手段:

1.1K40
来自专栏数据和云

你真的会用索引吗?来看看COUNT(*)到底能有多快

作者简介 ? 案例说明 一个大表的COUNT,究竟能有多快?除类似物化视图的做法,我们所能做到的极限能有多快?这不是一个真实的案例,而是根据笔者在网上发的一篇帖...

40260
来自专栏杨建荣的学习笔记

持续近7个小时的索引扫描的查询优化分析 (r5笔记第44天)

昨天客户的DBA反映有一个数据抽取的任务持续了很长时间最后超时退出了,让我看看有什么地方可以调优一下。 找到了对应的日志,发现在一个大表抽取的时候,抽取持续了将...

42450
来自专栏乐沙弥的世界

Oracle 重建索引的必要性

      索引重建是一个争论不休被不断热烈讨论的议题。当然Oracle官方也有自己的观点,我们很多DBA也是遵循这一准则来重建索引,那就是Oracle建议对于...

14810

扫码关注云+社区

领取腾讯云代金券