ITPub博客

首页 > 数据库 > Oracle > 写入值顺序和乱序对数据访问的影响

写入值顺序和乱序对数据访问的影响

原创 Oracle 作者:gaolu1234 时间:2017-12-14 01:24:58 0 删除 编辑
数据写入到表时, 写入值顺序和乱序对数据访问的影响
2017-12-13 听了高斌的讲解, 自己做了实验,验证了下面的结论:

当普通 heap表,即堆表, 在写入值时,被索引值的写入顺序会影响访问性能 :
1. 当写入值是顺序的时候, 表记录也是在 block 里按照顺序写入, 即  id =  1,2,3,4,5 记录 会写入 第一个 block ,当第一个block写满后 ,
顺序写入下一个block, 这个顺序指的是磁盘访问顺序 。

2. 当写入值是乱序时, 乱序表记录就会存放在顺序的block里面, 差别很大的 id 值的 几条记录放在同一个块里面 。

3. 当 使用索引的时候, 被索引的值是根据 B* 树来进行从小到大排序的, 
  上述 情况 1的时候, 多个索引块指向 一个数据块 , 
  上述情况2 的时候, 一个索引块里面的值 指向 多个 表 数据块

4. 分区以后, 打破 数据块 热点

5. reverse 键值后 打破了索引热块


drop table testseq ;
drop table testsca ;

create table testseq ( id number(18) , fname varchar(1000) ) ;
create index ind_seq on testseq(id) ;

create table testsca ( id number(18) , fname varchar(1000) ) ;
create index ind_sca on testsca(id) ;


---- 写入1 万行数据,
 

SQL> select * from v$version ;
BANNER
------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> select extent_id , bytes, blocks from user_extents where segment_name='TESTSEQ' ;

no rows selected

SQL> select extent_id , bytes , blocks from user_extents where segment_name='TESTSCA' ;

no rows selected

SQL>


begin

    for i in 1..10000  loop
        insert into testseq ( id , fname ) values ( i , dbms_random.String('a', 1000 ) ) ;
    
    end loop ;
    commit;
    
end;
/

 

Elapsed: 00:00:44.70


begin

    for i in 1..10000  loop
        insert into testsca ( id , fname ) values ( dbms_random.value(1, 10000 ) , dbms_random.String('a', 1000 ) ) ;
    
    end loop ;
    commit;
    
end;
/

Elapsed: 00:00:45.82







 select extent_id , bytes, blocks from user_extents where segment_name='TESTSEQ' ;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8
         4      65536          8
         5      65536          8
         6      65536          8
         7      65536          8
         8      65536          8
         9      65536          8
        10      65536          8

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
        11      65536          8
        12      65536          8
        13      65536          8
        14      65536          8
        15      65536          8
        16    1048576        128
        17    1048576        128
        18    1048576        128
        19    1048576        128
        20    1048576        128
        21    1048576        128

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
        22    1048576        128
        23    1048576        128
        24    1048576        128
        25    1048576        128
        26    1048576        128

27 rows selected.

select sum(blocks) from user_extents where segment_name='TESTSEQ' ;
SUM(BLOCKS)
-----------
       1536

exec dbms_stats.gather_table_stats(user,'TESTSEQ');    
analyze table TESTSEQ compute statistics;        
select num_rows, blocks, empty_blocks, avg_row_len from user_tables where table_name = 'TESTSEQ' ;
  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ---------- ------------ -----------
     10000       1504           32        1010

一共 1536 block 分配给表,      32 是空的 1504 是使用的 ,

1504 + 32 = 1536

select id,  rowid from testseq  where rownum < 50  order by id   ;  
select id, substr(rowid,10,6) blocknumber , substr(rowid,16,3) rownumber     from testseq  where rownum < 100  order by id   ;  
       

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
         1 AAAc7u     AAA
         2 AAAc7u     AAB
         3 AAAc7u     AAC
         4 AAAc7u     AAD
         5 AAAc7u     AAE
         6 AAAc7u     AAF
         7 AAAc7u     AAG
         8 AAAc7v     AAA
         9 AAAc7v     AAB
        10 AAAc7v     AAC
        11 AAAc7v     AAD

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
        12 AAAc7v     AAE
        13 AAAc7v     AAF
        14 AAAc7v     AAG
        15 AAAc7r     AAA
        16 AAAc7r     AAB
        17 AAAc7r     AAC
        18 AAAc7r     AAD
        19 AAAc7r     AAE
        20 AAAc7r     AAF
        21 AAAc7r     AAG
        22 AAAc7s     AAA

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
        23 AAAc7s     AAB
        24 AAAc7s     AAC
        25 AAAc7s     AAD
        26 AAAc7s     AAE
        27 AAAc7s     AAF
        28 AAAc7s     AAG
        29 AAAc7t     AAA
        30 AAAc7t     AAB
        31 AAAc7t     AAC
        32 AAAc7t     AAD
        33 AAAc7t     AAE

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
        34 AAAc7t     AAF
        35 AAAc7t     AAG
        36 AAAc74     AAA
        37 AAAc74     AAB
        38 AAAc74     AAC
        39 AAAc74     AAD
        40 AAAc74     AAE
        41 AAAc74     AAF
        42 AAAc74     AAG
        43 AAAc76     AAA
        44 AAAc76     AAB

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
        45 AAAc76     AAC
        46 AAAc76     AAD
        47 AAAc76     AAE
        48 AAAc76     AAF
        49 AAAc76     AAG
        50 AAAc77     AAA
        51 AAAc77     AAB
        52 AAAc77     AAC
        53 AAAc77     AAD
        54 AAAc77     AAE
        55 AAAc77     AAF

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
        56 AAAc77     AAG
        57 AAAc78     AAA
        58 AAAc78     AAB
        59 AAAc78     AAC
        60 AAAc78     AAD
        61 AAAc78     AAE
        62 AAAc78     AAF
        63 AAAc78     AAG
        64 AAAc79     AAA
        65 AAAc79     AAB
        66 AAAc79     AAC

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
        67 AAAc79     AAD
        68 AAAc79     AAE
        69 AAAc79     AAF
        70 AAAc79     AAG
        71 AAAc7+     AAA
        72 AAAc7+     AAB
        73 AAAc7+     AAC
        74 AAAc7+     AAD
        75 AAAc7+     AAE
        76 AAAc7+     AAF
        77 AAAc7+     AAG

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
        78 AAAc7/     AAA
        79 AAAc7/     AAB
        80 AAAc7/     AAC
        81 AAAc7/     AAD
        82 AAAc7/     AAE
        83 AAAc7/     AAF
        84 AAAc7/     AAG
        85 AAAc75     AAA
        86 AAAc75     AAB
        87 AAAc75     AAC
        88 AAAc75     AAD

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
        89 AAAc75     AAE
        90 AAAc75     AAF
        91 AAAc75     AAG
        92 AAAdoC     AAA
       134 AAAdoB     AAA
       135 AAAdoB     AAB
       136 AAAdoB     AAC
       137 AAAdoB     AAD
       138 AAAdoB     AAE
       139 AAAdoB     AAF
       140 AAAdoB     AAG
       
       
select distinct blocknumber from ( select id, substr(rowid,10,6) blocknumber , substr(rowid,16,3) rownumber     from testseq  where rownum < 100  order by id )  ;         
       
顺序写入的数据基本按照顺序在一个数据块里面, 当根据id的范围来选择数据时, 例如 id > 1 and id < 100 ,     要读 15 个块
BLOCKNUMBE
----------
AAAc7s
AAAc7r
AAAc76
AAAc74
AAAc75
AAAdoB
AAAc7u
AAAc7v
AAAc78
AAAc7+
AAAdoC

BLOCKNUMBE
----------
AAAc77
AAAc7/
AAAc7t
AAAc79

15 rows selected.
   
       
       
       
       
       
       
       
       
select extent_id , bytes , blocks from user_extents where segment_name='TESTSCA' ;
 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8
         4      65536          8
         5      65536          8
         6      65536          8
         7      65536          8
         8      65536          8
         9      65536          8
        10      65536          8

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
        11      65536          8
        12      65536          8
        13      65536          8
        14      65536          8
        15      65536          8
        16    1048576        128
        17    1048576        128
        18    1048576        128
        19    1048576        128
        20    1048576        128
        21    1048576        128

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
        22    1048576        128
        23    1048576        128
        24    1048576        128
        25    1048576        128
        26    1048576        128

27 rows selected.


select sum(blocks) from user_extents where segment_name='TESTSCA' ;

SUM(BLOCKS)
-----------
       1536
       
exec dbms_stats.gather_table_stats(user,'TESTSCA');    
analyze table TESTSCA compute statistics;        
select num_rows, blocks, empty_blocks, avg_row_len from user_tables where table_name = 'TESTSCA' ;       
  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ---------- ------------ -----------
     10000       1504           32        1011


select id,  rowid from testsca  where rownum < 100  order by id   ;  

select id, substr(rowid,10,6) blocknumber , substr(rowid,16,3) rownumber     from testsca  where rownum < 100  order by id ;
        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
        90 AAAeC/     AAD
       210 AAAeDK     AAA
       285 AAAeDP     AAG
       298 AAAeC7     AAA
       657 AAAeC7     AAF
       740 AAAeDJ     AAA
       871 AAAeDP     AAA
       882 AAAeDI     AAB
      1022 AAAeC8     AAE
      1028 AAAeC+     AAG
      1082 AAAeC/     AAG

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
      1142 AAAeDO     AAC
      1589 AAAeC8     AAA
      1745 AAAeC/     AAF
      1774 AAAeC/     AAA
      1886 AAAeC9     AAE
      2130 AAAeDK     AAF
      2259 AAAeC8     AAD
      2352 AAAeDJ     AAD
      2441 AAAeDI     AAG
      2615 AAAeDR     AAC
      2759 AAAeC7     AAD

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
      2768 AAAeDM     AAG
      2832 AAAeC/     AAB
      2975 AAAeDI     AAA
      3184 AAAeDK     AAC
      3195 AAAeDN     AAA
      3229 AAAeDR     AAA
      3311 AAAeC8     AAB
      3430 AAAeDI     AAE
      3506 AAAeC8     AAF
      3659 AAAeDP     AAD
      3831 AAAeC+     AAB

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
      3879 AAAeC+     AAA
      3980 AAAeDM     AAA
      4202 AAAeC/     AAE
      4291 AAAeDO     AAG
      4372 AAAeDL     AAD
      4437 AAAeDJ     AAG
      4519 AAAeC7     AAB
      4541 AAAeC8     AAC
      4588 AAAeDN     AAD
      4674 AAAeDM     AAB
      4715 AAAeC7     AAG

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
      4772 AAAeC+     AAE
      4942 AAAeC8     AAG
      5051 AAAeDN     AAF
      5054 AAAeDI     AAC
      5107 AAAeC/     AAC
      5238 AAAeDO     AAE
      5289 AAAeC7     AAE
      5318 AAAeDJ     AAF
      5447 AAAeDL     AAE
      5468 AAAeDO     AAB
      5477 AAAeC9     AAA

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
      5514 AAAeDR     AAE
      5624 AAAeC9     AAG
      5908 AAAeDK     AAE
      5934 AAAeDJ     AAB
      6318 AAAeC+     AAF
      6371 AAAeDJ     AAC
      6510 AAAeDS     AAA
      6568 AAAeDN     AAC
      6668 AAAeDO     AAF
      6763 AAAeDP     AAF
      6845 AAAeDL     AAF

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
      6850 AAAeDL     AAG
      6937 AAAeDR     AAF
      7054 AAAeDL     AAA
      7127 AAAeDJ     AAE
      7256 AAAeC9     AAB
      7303 AAAeDN     AAB
      7348 AAAeDP     AAC
      7455 AAAeDR     AAD
      7504 AAAeDM     AAF
      7574 AAAeC9     AAF
      7751 AAAeDK     AAG

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
      7869 AAAeDI     AAF
      8018 AAAeDO     AAD
      8136 AAAeC9     AAD
      8264 AAAeDK     AAD
      8433 AAAeDK     AAB
      8527 AAAeDN     AAE
      8559 AAAeC7     AAC
      8559 AAAeC+     AAC
      8586 AAAeDM     AAD
      8659 AAAeDO     AAA
      8729 AAAeC9     AAC

        ID BLOCKNUMBE ROWNUMBER
---------- ---------- ----------
      8812 AAAeDP     AAE
      8813 AAAeDR     AAG
      8841 AAAeDR     AAB
      8901 AAAeC+     AAD
      8968 AAAeDL     AAB
      9108 AAAeDI     AAD
      9330 AAAeDP     AAB
      9396 AAAeDN     AAG
      9513 AAAeDM     AAC
      9702 AAAeDM     AAE
      9783 AAAeDL     AAC
    
    
select distinct blocknumber from ( select id, substr(rowid,10,6) blocknumber , substr(rowid,16,3) rownumber     from testsca  where rownum < 100  order by id )  ;  
BLOCKNUMBE
----------
AAAeC+
AAAeDR
AAAeC/
AAAeDK
AAAeDO
AAAeC8
AAAeDL
AAAeDP
AAAeDJ
AAAeC9
AAAeDI

BLOCKNUMBE
----------
AAAeDN
AAAeDS
AAAeC7
AAAeDM

15 rows selected.

同样写入 15 个块,  但是 访问时 被索引的值是分布在多个数据块里面进行排序。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16993226/viewspace-2148681/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-03-22

  • 博文量
    13
  • 访问量
    18188