Oracle 性能调优学习笔记(十四)-- 有效使用Oracle 存储结构

有效使用Oracle 存储结构
       表的种类: heap table,cluster table, IOT table,partition table;
       选择适当物理结构的影响因子
         1.查询或DML语句.
      2.表大小.
      3.行大小,row分组,block size.
      4.事物的大小
      5.并行查询.
       数据访问方式:
        1.索引
     2.B-Tre
     3.BitMap
     4.基于函数
     5.IOT
     6.cluster表.
    index cluster表的使用:
    1.create cluster mycluster (deptno number(2)) size 1024;
    
    2.create index myc_idx on cluster mycluster;
    3.create table deptInfo
      (
        deptid number(2) primary key,
     ename varchar(20) ,sal number,
     deptno number(2) references deptInfo(deptid)
       )cluster mycluster(deptno)
     /
     先读索引,在读数据.
    Hash clustered Tables
    1.create cluster hc(hk number) hashkeys 1000 size 8192;
    备注:hash的key的大小.
         size:hash的大小.
       hash 索引使用于等号的查询.
      
    分区表
     范围分区(Range Partition)
         create table sales(
       acct_no number(5),
       week_no number(2))
      partition by range(week_no)
      (
        partition p1 values less than (4) tablespace tbs1,
        partition p2 values less than (8) tablespace tbs2,
        partition p3 values less than (12) tablespace tbs4,
        .......................................
        partition p4 values less than (16) tablespace tbs5     
      )
      备份:1.分区键week_no
        2.子分区的表空间可以不一样.
        3.分区的各分区的大小一样.
      create tablespace tbs
        datafile ''
        size 200M
        extent management local uniform
        segment space management auto;
      
      创建分区
      create table range1
      (
        rk date,
        data,varchar(20)
      )
      partition by range(rk)
      (
         partition p1 less than (to_date('2011-12-31','yyyy-mm-dd')) tablespace train,
         partition p2 less than (to_date('2012-12-31','yyyy-mm-dd')) tablespace train1
      )
      查询分区信息
      select segment_name,partition_name,segment_type,tablespace_name from user_segments;
      查询分区数据
      select * from range1 partition(p1);
      修改分区
         alter table rang1
       add partition
       p3 values less than (maxvalue)  tablespace train;
       
     Hash分区(Hash Partition)
      create table empt
       (empno int,
        ename varchar(20)
        )
        partition by hash(empno)
        (
         partition p1 tablespace ts1,
         partition p2 tablespace ts2
        );
        建议分区的个数为幂数(2的n次幂数)个.
          数据分布通过分区键实现无法控制.
        当数据磁盘或者分区有问题.使用带分区的查看分区
        select * from empt partition(p1);
       
       
       
     List分区(List Partition)
        create table locations
        (location_id,
         street_address,
      state_province
      )storage(
        initial 10k
        next 20k
      )
      tablespace users
      partition  by list(state_province)
      (
         partition region_east values('MA','MY') tablespace tbs1,
         partition region_west values('CA','CK') tablespace tbs2,
         partition region_south values('QA','QY') tablespace tbs1,
         partition region_south values('KA','KY') tablespace tbs2,
         partition p3 values(default)
      );
      
      备注list 分区有default的,添加分区不容易.
     
     Composite分区(Compsite Partition)
      并行DML操作提高性能.
      支持本地组合索引.
       create table table1
       (
        range_key date,
        hash_key int,
        data varchar(20)
       )partition by range(range_key)
       subpartition by hash(hash_key) subpartitions 2
       (
        partition p1 values less than (to_date('2011-01-01','yyyy-mm-dd'))
         (
        subpartition h1,
        subpartition h2
         ),
         partition p2 values less than (to_date('2012-01-01','yyyy-mm-dd'))
         (
        subpartition h11,
        subpartition h22
         )
       );
     
            select segment_name,partition_name,segment_type from user_segments;
     
     
   分区索引
      本地索引
      create index local_idx01 on range1(a,b) local;
      分区索引
        create index gp_idx on range1(b) global
         partition by range(b)
         (
        partition idx1 values less than (1000),
        partition idx2 values less than (2000),
        partition idx3 values less than (maxvalue)
         );
        
                  select object_name,object_type from user_objects;
      
   收集统计分区信息       
        dbms_stats.gather_table_stats(
        ownname=>'',
     tabname=>'',
     partitionname=>'',
     granularity=>''
     );
   收集索引分区信息
        dbms_stats.gather_index_stats(
        ownname=>'',
     indexname=>'',
     partitionname=>''
     );