Oracle 列顺序测试

时间：2022-04-22 04:30:39

列顺序测试

大家在做表设计的时候通常对表中列的排列顺序没有过多注意，但是其实越常用的列，它的位置越靠前，则查询速度越快。

因为每个block里面存储了row directory (每行数据在块中的位移地址)而没有存放column的位移
所以只能根据column#通过扫描row header 以及 column data 来获取相应的列值
越靠后的列，需要计算的越多

建立测试表col_test

  1. declare
  2. v_sql varchar2(4000) ;
  3. begin
  4. v_sql := 'create table col_test (' ;
  5. for i in 1 .. 100 loop
  6. v_sql := v_sql || 'id'||i||' number ,' ;
  7. end loop ;
  8. v_sql := substr(v_sql,1,length(v_sql)-1) || ') ' ;
  9. execute immediate v_sql ;
  10. end ;
  11. /
  12. _dexter@DAVID> desc col_test ;
  13. Name                                                  Null?    Type
  14. ----------------------------------------------------- -------- --------------
  15. ID1                                                            NUMBER
  16. ID2                                                            NUMBER
  17. ID3                                                            NUMBER
  18. ID4                                                            NUMBER
  19. ID5                                                            NUMBER
  20. ....
  21. ID99                                                           NUMBER
  22. ID100                                                          NUMBER

复制代码

初始化数据100w条

  1. declare
  2. v_sql varchar2(4000) ;
  3. begin
  4. v_sql := 'insert into  col_test select ' ;
  5. for i in 1 .. 100 loop
  6. v_sql := v_sql || ' 1 ,' ;
  7. end loop ;
  8. v_sql := substr(v_sql,1,length(v_sql)-1) || ' from dual connect by level <= 1000000 ' ;
  9. execute immediate v_sql ;
  10. commit ;
  11. end ;
  12. /

复制代码

测试耗时

  1. declare
  2. n       number ;
  3. begin_t pls_integer ;
  4. end_t   pls_integer ;
  5. v_Sql   varchar2(4000) ;
  6. begin
  7. for i in 1 .. 101 loop
  8. begin_t := dbms_utility.get_time ;
  9. if i = 1 then
  10. v_sql := 'select count(*) from col_test' ;
  11. else
  12. v_sql := 'select count(id'||(i-1)||') from col_test' ;
  13. end if ;
  14. execute immediate v_sql into n ;
  15. end_t   := dbms_utility.get_time ;
  16. dbms_output.put_line('Col'||(i-1)||' : '||(end_t-begin_t)*10);
  17. end loop ;
  18. end ;
  19. /
  20. --下面的单位为毫秒
  21. _dexter@DAVID> /
  22. Col0 : 150
  23. Col1 : 140
  24. Col2 : 110
  25. Col3 : 100
  26. Col4 : 100
  27. Col5 : 110
  28. Col6 : 110
  29. Col7 : 110
  30. Col8 : 110
  31. Col9 : 120
  32. Col10 : 130
  33. Col11 : 120
  34. Col12 : 110
  35. Col13 : 120
  36. Col14 : 120
  37. Col15 : 120
  38. Col16 : 130
  39. Col17 : 120
  40. Col18 : 160
  41. Col19 : 130
  42. Col20 : 140
  43. Col21 : 130
  44. Col22 : 140
  45. Col23 : 140
  46. Col24 : 140
  47. Col25 : 130
  48. Col26 : 160
  49. Col27 : 150
  50. Col28 : 140
  51. Col29 : 150
  52. Col30 : 150
  53. Col31 : 160
  54. Col32 : 180
  55. Col33 : 160
  56. Col34 : 160
  57. Col35 : 160
  58. Col36 : 160
  59. Col37 : 170
  60. Col38 : 180
  61. Col39 : 170
  62. Col40 : 170
  63. Col41 : 180
  64. Col42 : 180
  65. Col43 : 170
  66. Col44 : 210
  67. Col45 : 190
  68. Col46 : 180
  69. Col47 : 180
  70. Col48 : 190
  71. Col49 : 210
  72. Col50 : 190
  73. Col51 : 190
  74. Col52 : 200
  75. Col53 : 200
  76. Col54 : 230
  77. Col55 : 200
  78. Col56 : 210
  79. Col57 : 200
  80. Col58 : 210
  81. Col59 : 220
  82. Col60 : 210
  83. Col61 : 210
  84. Col62 : 220
  85. Col63 : 240
  86. Col64 : 220
  87. Col65 : 220
  88. Col66 : 220
  89. Col67 : 230
  90. Col68 : 270
  91. Col69 : 220
  92. Col70 : 230
  93. Col71 : 230
  94. Col72 : 270
  95. Col73 : 230
  96. Col74 : 240
  97. Col75 : 240
  98. Col76 : 250
  99. Col77 : 240
  100. Col78 : 260
  101. Col79 : 260
  102. Col80 : 280
  103. Col81 : 250
  104. Col82 : 250
  105. Col83 : 250
  106. Col84 : 250
  107. Col85 : 260
  108. Col86 : 260
  109. Col87 : 260
  110. Col88 : 290
  111. Col89 : 260
  112. Col90 : 270
  113. Col91 : 280
  114. Col92 : 280
  115. Col93 : 280
  116. Col94 : 270
  117. Col95 : 310
  118. Col96 : 270
  119. Col97 : 310
  120. Col98 : 290
  121. Col99 : 290
  122. Col100 : 280
  123. PL/SQL procedure successfully completed.

复制代码

可以看到耗时是线性增加的，所以在做表设计的时候，应该注意建表时的列的排列顺序，将常用的列放在靠前的位置

null值测试

关于null值的存储，可以查看我的另外一篇帖子http://www.itpub.net/thread-1718328-1-1.html
因为基于它的存储原理，一个rowpiece中，如果列后面的值都为空，则不再占用存储空间，并且 rowheader 的cc(Column Count) 也不会将其记录到上面，
，但是这一行的rowpiece中第二列不为空，第三、四、五列为空
则cc会记为2
个字节的存储空间
null值在表中的位置除了影响存储空间，还会影响查询，因为在扫描数据的时候
可以根据cc的个数来确定是否需要扫描column data的数据
感兴趣的可以做一下测试
100列为有值，其他都为null

下面是测试内容：

建立测试表null_test

  1. declare
  2. v_sql varchar2(4000) ;
  3. begin
  4. v_sql := 'create table null_test (' ;
  5. for i in 1 .. 100 loop
  6. v_sql := v_sql || 'id'||i||' number ,' ;
  7. end loop ;
  8. v_sql := substr(v_sql,1,length(v_sql)-1) || ') ' ;
  9. execute immediate v_sql ;
  10. end ;
  11. /

复制代码

初始化数据100w条

  1. insert into null_test(id50) select 100 from dual connect by level <= 1000000 ; commit ;

复制代码

测试

  1. declare
  2. n       number ;
  3. begin_t pls_integer ;
  4. end_t   pls_integer ;
  5. v_Sql   varchar2(4000) ;
  6. begin
  7. for i in 1 .. 101 loop
  8. begin_t := dbms_utility.get_time ;
  9. if i = 1 then
  10. v_sql := 'select count(*) from null_test' ;
  11. else
  12. v_sql := 'select count(id'||(i-1)||') from null_test' ;
  13. end if ;
  14. execute immediate v_sql into n ;
  15. end_t   := dbms_utility.get_time ;
  16. dbms_output.put_line('Col'||(i-1)||' : '||(end_t-begin_t)*10);
  17. end loop ;
  18. end ;
  19. /
  20. --测试结果
  21. dexter@FAKE> /
  22. Col0 : 60
  23. Col1 : 80
  24. Col2 : 80
  25. Col3 : 90
  26. Col4 : 90
  27. Col5 : 100
  28. Col6 : 100
  29. Col7 : 100
  30. Col8 : 120
  31. Col9 : 110
  32. Col10 : 120
  33. Col11 : 130
  34. Col12 : 130
  35. Col13 : 140
  36. Col14 : 150
  37. Col15 : 160
  38. Col16 : 160
  39. Col17 : 160
  40. Col18 : 170
  41. Col19 : 170
  42. Col20 : 170
  43. Col21 : 180
  44. Col22 : 180
  45. Col23 : 180
  46. Col24 : 190
  47. Col25 : 190
  48. Col26 : 200
  49. Col27 : 200
  50. Col28 : 200
  51. Col29 : 200
  52. Col30 : 200
  53. Col31 : 210
  54. Col32 : 210
  55. Col33 : 220
  56. Col34 : 220
  57. Col35 : 230
  58. Col36 : 220
  59. Col37 : 220
  60. Col38 : 230
  61. Col39 : 240
  62. Col40 : 240
  63. Col41 : 230
  64. Col42 : 240
  65. Col43 : 250
  66. Col44 : 250
  67. Col45 : 250
  68. Col46 : 260
  69. Col47 : 260
  70. Col48 : 270
  71. Col49 : 260
  72. Col50 : 270
  73. Col51 : 70
  74. Col52 : 80
  75. Col53 : 70
  76. Col54 : 70
  77. Col55 : 80
  78. Col56 : 70
  79. Col57 : 70
  80. Col58 : 80
  81. Col59 : 70
  82. Col60 : 70
  83. Col61 : 70
  84. Col62 : 70
  85. Col63 : 80
  86. Col64 : 80
  87. Col65 : 70
  88. Col66 : 70
  89. Col67 : 70
  90. Col68 : 80
  91. Col69 : 70
  92. Col70 : 70
  93. Col71 : 70
  94. Col72 : 80
  95. Col73 : 70
  96. Col74 : 70
  97. Col75 : 70
  98. Col76 : 80
  99. Col77 : 70
  100. Col78 : 70
  101. Col79 : 70
  102. Col80 : 80
  103. Col81 : 70
  104. Col82 : 70
  105. Col83 : 80
  106. Col84 : 70
  107. Col85 : 70
  108. Col86 : 70
  109. Col87 : 80
  110. Col88 : 70
  111. Col89 : 70
  112. Col90 : 80
  113. Col91 : 70
  114. Col92 : 70
  115. Col93 : 70
  116. Col94 : 80
  117. Col95 : 70
  118. Col96 : 70
  119. Col97 : 70
  120. Col98 : 70
  121. Col99 : 70
  122. Col100 : 80
  123. PL/SQL procedure successfully completed.

复制代码

列开始到最后都维持到一个稳定的值
线性增加是因为Oracle 数据库会不断扫描row header 以及 column data 计算要查找的列的位移值
列开始，则可以直接扫描row header 的 CC 个数即可拿到要查找的列的值（null），所以节省了大量扫描column data部分的时间，也证实了上面提出的观点
所以设计表的时候，尽量把会存储null值的列放在表的末尾

为了加深印象，我们使用bbed抽出一条数据来看一下它在block中到底是怎样存储的，加深一下印象

  1. dexter@FAKE> select dbms_rowid.rowid_relative_fno(t.rowid) as "FNO#",
  2. 2         dbms_rowid.rowid_block_number(t.rowid) as "BLK#",
  3. 3         dbms_rowid.rowid_row_number(t.rowid) as "ROW#"
  4. 4    from dexter.null_test t
  5. 5   where rownum<2 ;
  6. FNO#       BLK#       ROW#
  7. ---------- ---------- ----------
  8. 6        284          0
  9. BBED> set dba 6,284
  10. DBA             0x0180011c (25166108 6,284)
  11. BBED> p *kdbr[0]
  12. rowdata[4675]
  13. -------------
  14. ub1 rowdata[4675]                           @5878     0x2c
  15. BBED> set offset 5878
  16. OFFSET          5878
  17. BBED> dump /v offset 5878 count 58
  18. File: /u01/apps/oracle/oradata/fake/gg_trans201.dbf (6)
  19. Block: 284     Offsets: 5878 to 5935  Dba:0x0180011c
  20. -------------------------------------------------------
  21. 2c0032ff ffffffff ffffffff ffffffff l ,.2.............
  22. ffffffff ffffffff ffffffff ffffffff l ................
  23. ffffffff ffffffff ffffffff ffffffff l ................
  24. ffffffff 02c2022c 0032              l .....Â.,.2
  25. <16 bytes per line>

复制代码

大家一定对count 58 产生疑问，请继续看下去

row header 2c0032
它包括：
2c=flag=00101100=--H-FL--=header+first+last
00=lb itl slot 0x00
32=cc column count = 16x3+2=50
和列值c202

  1. dexter@FAKE> select dump(100,16) from dual ;
  2. DUMP(100,16)
  3. -----------------
  4. Typ=2 Len=2: c2,2

复制代码

so 3+49+1+2=55
个字节是下一行的row header : 2c 0032

LB

ITL

CC

COL#1 value

COL#2-49 value

COL#50 length

COL#50 value

COL#51 value

COL#52-100 value

2C

00

32

FF

FF

02

C202

FF

FF

附录：

rowpiece构成

可以参考官方文档中的说明
图12-7

Oracle 列顺序测试

row piece header的时候），cluster key （cluster table）
个字节（状态、itl槽位、column个数）

cluster table的存储情况会在其他章节中介绍
列的存储

http://www.itpub.net/thread-1719026-1-1.html

LB说明

KCHDFLPN
K=Cluster key                    (用来表示cluster key)
C=Cluster table member    (当表中拥有cluster时使用此标识)
H=Head of row piece         (含有Rowpiece header)
D=Delete row                   (表示已经删除)
F=First data piece             (Rowpiece 的第一个piece)
L=Last data piece              (Rowpiece 的最后一个piece,Rowchain或者列个数大于253时候会出现)
P=First column continues from previous piece  (没有重现过)
N=Last column continues in next piece            (没有重现过)

Oracle 列顺序测试的更多相关文章

  1. ORACLE中查询语句的执行顺及where部分条件执行顺序测试

    Oracle中的一些查询语句及其执行顺序 原文地址:https://www.cnblogs.com/likeju/p/5039115.html 查询条件: 1)LIKE:模糊查询,需要借助两个通配符, ...

  2. oracle调整表中列顺序

    有一个哥们提出一个问题: 有个表,创建时候的列顺序是a,b,c 如何使用select * 的时候,让列的显示顺序是a,c,b 而且任性地必须使用select *来查询,且不能重建表. 假设有个表tes ...

  3. SQL Server 执行计划利用统计信息对数据行的预估原理二（为什么复合索引列顺序会影响到执行计划对数据行的预估）

    本文出处:http://www.cnblogs.com/wy123/p/6008477.html 关于统计信息对数据行数做预估,之前写过对非相关列(单独或者单独的索引列)进行预估时候的算法,参考这里. ...

  4. SQL Server创建复合索引时，复合索引列顺序对查询的性能影响

    说说复合索引 写索引的博客太多了,一直不想动手写,有一下两个原因:一是觉得有炒剩饭的嫌疑,有兄弟曾说:索引吗,只要在查询条件上建索引就行了,真的可以这么暴力吗?二来觉得,索引是个非常大的话题,很难概括 ...

  5. Oracle列自增-12c

    在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性,从而实现了列自增长功能. 一.Identity ...

  6. Oracle列自增实现&lpar;2&rpar;-Identity Columns in Oracle Database 12c Release 1 &lpar;12&period;1&rpar;

    Oracle列自增-Identity Columns in Oracle Database 12c Release 1 (12.1) 在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序 ...

  7. oracle列自增实现&lpar;1&rpar;-Sequence&plus;Trigger实现Oracle列自增

    Sequence+Trigger实现Oracle列自增 序列的语法格式为: CREATE SEQUENCE 序列名 [INCREMENT BY n] [START WITH n] [{MAXVALUE ...

  8. mysql索引之七：组合索引中选择合适的索引列顺序

    组合索引(concatenated index):由多个列构成的索引,如create index idx_emp on emp(col1, col2, col3, ……),则我们称idx_emp索引为 ...

  9. Oracle Flushback 学习测试

    Oracle Flushback 学习测试:三思笔记 Flashback恢复 从9i开始,利用oracle查询的多版本一致的特点,实现从回滚段中读取一定时间内在表中操作的数据,被称为 flashbac ...

随机推荐

  1. 【Java每日一题】20170106

    20170105问题解析请点击今日问题下方的"[Java每日一题]20170106"查看(问题解析在公众号首发,公众号ID:weknow619) package Jan2017; ...

  2. golang 前置补0

    package main import ( "fmt" ) func main() { a := 1 fmt.Println(a) //前置补0 fmt.Printf(" ...

  3. C&num;抓包

    抓包過程中發現頁面<input type="hidden" name="__LASTFOCUS" id="__LASTFOCUS" v ...

  4. shell之任务控制

    http://linux.cn/article-2680-1.html 你的 shell 会留有一张当前作业的表单,称为作业表.当你键入命令时,shell 会给它分配一个 jobID(也称作 JOBS ...

  5. Android 推断当前Activity是不是最后一个Activity 以及 应用或Activity是否存在

    推断当前Activity是最后一个Activity: 在Activity的方法中, 有一个方法isTaskRoot()方法, 这种方法能够推断当前Activity是否是最后一个Activity, 假 ...

  6. 第十三章 部署Java应用程序

    打包 清单文件被命名为MANIFEST.MF,用于描述归档特征. 清单文件被分为多个节, 第一节被称为主节,作用于整个文件; 其他节则必须起始于Name的条目. 节之间空行分开. jar cfm JA ...

  7. iptables简单用法

    iptables -t 表名 <-A/I/D/R> 规则链名 [规则号] <-i/o 网卡名> -p 协议名 <-s 源IP/源子网> --sport 源端口 &l ...

  8. OO第一阶段总（休）结（养）分（生）析（息）

    第一次作业: 这是一次让我认识到ddl面前潜力真的可以无限大的作业. 一直以为OO是一门和数据结构一样先用几周的时间讲讲Java然后写写”Hello World”小程序再开始讲正课的我(我也不知道为什 ...

  9. PAT-1010 Radix

    1010 Radix (25 分) Given a pair of positive integers, for example, 6 and 110, can this equation 6 = 1 ...

  10. redis环境搭建与配置

    通过初始化脚本启动redis 1.将redis源码的utils文件夹下面有的redis_init_script复制到/etc/init.d/redis_端口号下面. 带密码的实例 REQUIRED_P ...

相关文章