逻辑体系的实际应用

容量和速度：

1. 实验1：针对不同的业务要有不同的容量规划：
   1. 分别创建两个类型的表空间A和B：A.初始1M，到极限后每次自动增长64k；B.直接给2G空间。
   2. 建两张表aa和bb，各自表空间分别是A和B。
   3. 同时往aa,bb插入1千万条数据。
   4. 由于A空间不够需要申请，每次扩展只有64k，所以比较耗时。
   5. 结果A：2分14秒 B：22秒
   6. select count( * ) from user_extents where segment_name=’aa’ :查看表aa有多少个extent。
2. 实验2：PCTFREE和行迁移
   1. 首先建一个表，向里面插入200条数据（简单的字符）。
   2. 然后将表的字段容量改为2000
   3. 更新表的字段为2000的字符。
   4. 查看查询的逻辑读。

      create table rt1 (a varchar2(10),b varchar2(10),c varchar2(10));
      @procrt1.sql
      exec procrt1
      alter table rt1 modify a varchar2(2000);
      alter table rt1 modify b varchar2(2000);
      alter table rt1 modify c varchar2(2000);
      update rt1
      set a=lpad('1',2000,'*'),b=lpad('1',2000,'*'),c=lpad('1',2000,'*');
      commit;
      set autotrace traceonly
      set linesize 1000
      select * from rt1;
      

      Statistics
      ----------------------------------------------------------
            0  recursive calls
            0  db block gets
          248  consistent gets
            0  physical reads
            0  redo size
          1212109  bytes sent via SQL*Net to client
          666  bytes received via SQL*Net from client
           15  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
          200  rows processed
      

      create table rt2 as select * from rt1;
      select * from rt2;
      

      Statistics
      ----------------------------------------------------------
            0  recursive calls
            0  db block gets
          204  consistent gets
            0  physical reads
            0  redo size
          1210909  bytes sent via SQL*Net to client
          666  bytes received via SQL*Net from client
           15  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
          200  rows processed

   5. procrt1:

      create or replace procedure procrt1
      as
      begin
          for i in 1..200
          loop
              insert into rt1 values( 'AA','BB','CC' );
          end loop;
      commit;
      end;
      /

   6. 查询PCT_FREE值：

      select pct_free from user_tables where table_name='RT1';

3. 实验3：行迁移的发现和优化：
1. 步骤：第一步运行脚本utlchain.sql(?为$ORACLE_HOME)建一个表格用来存分析数据。
2. 分别分析两个对比表格
3. 查看被插入的相应的数量
4. 附：脚本和操作语句

   create table CHAINED_ROWS (
     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
   );

   @?/rdbms/admin/utlchain.sql
   analyze table rt1 list chained rows into chained_rows;
   analyze table rt2 list chained rows into chained_rows;
   select count(*) from chained_rows where table_name='RT1';
   select count(*) from chained_rows where table_name='RT2';

5. 要对当前用户的所有表进行分析

   select 'analyze table '|| table_name || ' list chained rows into chained_rows;' from user_tables;
   select * from chained_rows;

4. 实验4：块大小的设置思想：
1. 分别建8k和16k的表空间。
2. 分别在两个表空间里建大表。各插入300万条记录。
3. 进行全表读：16k的表花的时间少，逻辑读也少。
4. 进行索引定位查单条记录：花的时间差不多。

   create tablespace tbs16 blocksize 16k datafile '/tmp/tbs16a.dbf' size 1g;
   create table t16 tablespace tbs16 as select * from dba_objects;
   insert into t16 select * from t16;
   insert into t16 select * from t16;
   insert into t16 select * from t16;
   insert into t16 select * from t16;
   insert into t16 select * from t16;
   insert into t16 select * from t16;
   update t16 set object_id=rownum;
   create index idx_16_oid on t16(object_id);
   
   create tablespace tbs8 datafile '/tmp/tbs8a.dbf' size 1g;
   create table t8 tablespace tbs8 as select * from dba_objects;
   insert into t8 select * from t8;
   insert into t8 select * from t8;
   insert into t8 select * from t8;
   insert into t8 select * from t8;
   insert into t8 select * from t8;
   insert into t8 select * from t8;
   update t8 set object_id=rownum;
   create index idx_8_oid on t8(object_id);
   
   set autotrace on
   set linesize 1000
   set timing on
   select count(*) from t16;
   select count(*) from t8;
   select count(*) from t16 where object_id=299999;
   select count(*) from t8 where object_id=299999;