表中数据如何被存储

数据库块的结构
往块里插入数据 往块的最底部插入数据
block header 记录块的上级，找数据，首先读取块头
free space
row data 
通常块里有个参数 pct—free：控制一个块里的空闲空间,预留做updata
插入的数据 有一天可能被updata
file header-segment-extent-block-block
记得避免行迁移  设置合理的pct-free 才不会产生行迁移
行链接
机械硬盘的寻址很慢。
发生行迁移如何处理优化  通过手段知道哪些行发生了迁移，扫描块，找到哪些行发生了迁移，找出后，新创建一个表，把这些行都插入进去
行迁移：利用来回搬 可以避免
行连接：利用更大的块可以解决

SQL> create table test as select * from hr.employees;

Table created.

SQL> create index idx_test_id on test(employee_id);

Index created.    有索引的这条，如果where条件有employee就不用全盘扫描。
autotrace的使用 可以显示一条sql语句执行的时候，使用了多少I/O资源，访问多少内存资源。还能看到SQL语句分成几步执行，评估执行计划，执行开销
traceonly 不真正的执行这条这条SQL语句
SQL> set autotrace traceonly statistics        评估执行计划，执行开销
SQL> select /*+index(test,idx_test_id)*/ * from test where employee_id>0;   强制走索引

107 rows selected.

Statistics
----------------------------------------------------------
        212  recursive calls
          0  db block gets
         47  consistent gets
          0  physical reads
          0  redo size
       9944  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed

SQL> set autotrace off
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

SQL> alter table test  modify last_name varchar2(1000);

Table altered.

SQL> alter table test modify first_name varchar2(1000);

Table altered.

SQL> alter table test modify email varchar2(1000);

Table altered.

SQL> alter table test modify phone_number varchar2(1000);

Table altered.

SQL> update test
  2  set
  3  last_name=lpad(last_name,1000,'*'),
  4  first_name=lpad(first_name,1000,'*'),
  5  email=lpad(email,1000,'*'),
  6  phone_number=lpad(phone_number,1000,'*');

107 rows updated.

SQL> commit
  2  ;

Commit complete.

SQL> set autotrace traceonly statistics
SQL> select /*+index(test,idx_test_id)*/ * from test where employee_id>0;

107 rows selected.

Statistics
----------------------------------------------------------
         32  recursive calls
          0  db block gets
        295  consistent gets
          0  physical reads
          0  redo size
     437772  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed       有大量的行迁移

SQL> set autotrace off

SQL> @?/rdbms/admin/utlchain.sql     ?代表在ORACLE_HOME目录下

Table created.

SQL> desc chained_rows
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER_NAME                                         VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 HEAD_ROWID    哪些行从块里搬走了                                  ROWID
 ANALYZE_TIMESTAMP                                  DATE

SQL> analyze table test list chained rows into chained_rows;   分析表test列出所有发出行迁移的行，输出到chained_rows中

Table analyzed.

SQL> select table_name,count(*) from chained_rows group by table_name;    发生搬迁的行的数量，访问这些数据，还需要访问这些行

TABLE_NAME                       COUNT(*)
------------------------------ ----------
TEST                                  105

SQL> create table test_temp as select * from test
  2  where rowid in (select head_rowid from chained_rows);

Table created.
把发生行迁移的数据放入新表

SQL> select count(*) from test_temp;

  COUNT(*)
----------
       105
发生行迁移的数量105行

SQL> delete from test
  2  where rowid in(select head_rowid from chained_rows);  把test表中发生行迁移的删除

105 rows deleted.

SQL> insert into test select * from test_temp; 

105 rows created.

SQL> commit;

Commit complete.

SQL> truncate table chained_rows;

Table truncated.

SQL> set autotrace traceonly statistics
SQL> select /*+index(test,idx_test_id)*/ * from test where employee_id>0;

107 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        116  consistent gets
          0  physical reads
          0  redo size
     437142  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed
实际工作中也用这种消除行迁移

行链接实验
环境准备：
SQL> truncate table chained_rows;

Table truncated.
SQL> drop table test_temp purge;

Table dropped.
SQL> drop table test purge;

Table dropped.

SQL> set autotrace off
-------------------------------------

SQL> create table test as select * from hr.employees;

Table created.

SQL> create index idx_test_id on test(employee_id);

Index created.
SQL> set autotrace traceonly statistics
SQL> select /*+index(test,idx_test_id)*/ * from test where employee_id>0;

107 rows selected.

Statistics
----------------------------------------------------------
        212  recursive calls
          0  db block gets
         47  consistent gets
          0  physical reads
          0  redo size
       9944  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed
SQL> set autotrace off
SQL> alter table test  modify last_name varchar2(2000);

Table altered.

SQL> alter table test modify first_name varchar2(2000);

Table altered.

SQL> alter table test modify email varchar2(2000);

Table altered.

SQL> alter table test modify phone_number varchar2(2000);

Table altered.

SQL> update test
  2  set
  3  last_name=lpad('1',2000,'*'),
  4  first_name=lpad('1',2000,'*'),
  5  email=lpad('1',2000,'*'),
  6  phone_number=lpad('1',2000,'*');

107 rows updated.

SQL>
SQL> commit
  2  ;

Commit complete.

这个表里的每一行绝对大于8K
SQL> select /*+index(test,idx_test_id)*/ * from test where employee_id>0;

107 rows selected.

Statistics
----------------------------------------------------------
         32  recursive calls
          0  db block gets
        391  consistent gets
          0  physical reads
          0  redo size
     868637  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed

SQL> set autotrace off
SQL> analyze table test list chained rows into chained_rows;

Table analyzed.

SQL> select table_name,count(*) from chained_rows group by table_name;

TABLE_NAME                       COUNT(*)
------------------------------ ----------
TEST                                  107

SQL> analyze table test list chained rows into chained_rows;

Table analyzed.

SQL> select table_name,count(*) from chained_rows group by table_name;

TABLE_NAME                       COUNT(*)
------------------------------ ----------
TEST                                  107
提升了接近一半的性能

SQL> set autotrace off
SQL> show parameter 16k

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
SQL> alter system set db_16k_cache_size=20m;

System altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.267.985112183
+DATA/orcl/datafile/sysaux.268.985112183
+DATA/orcl/datafile/undotbs1.269.985112183
+DATA/orcl/datafile/users.270.985112183
+DATA/orcl/datafile/example.276.985112295
SQL> create tablespace tbs_16k blocksize 16k datafile '+DATA/orcl/datafile/tbs_16k.dbf' size 10m;

Tablespace created.

SQL> alter table test move  tablespace tbs_16k;

Table altered.
给了一个更大的块，一个块可以存16K
SQL> truncate table chained_rows;

Table truncated.
SQL> select table_name,count(*) from chained_rows group by table_name;

no rows selected

SQL> alter index idx_test_id rebuild; 重建索引

Index altered.

SQL> set autotrace traceonly statistics  
SQL> select /*+index(test,idx_test_id)*/ * from test where employee_id>0;

107 rows selected.

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        177  consistent gets
          0  physical reads
          0  redo size
     866854  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed
SQL> set autotrace off
少读一个块都可以提升性能