9月28日分区索引和执行计划

-、分区索引
  定义:通过创建索引实际上就是将全部索引字段值进行排序,存放在B+TREE中;
      oracle数据表的数据存放在数据段里;
      oracle数据表的索引存放在索引段里;
目的:因为oracle 是堆表,通过创建索引提高数据查询能力--减少数据的查询量;
有索引和没有索引区别:
1、有索引:数据有序排列,可以进行分区裁剪,再通过B+tree法则快速定位到数据所在位置;
   注:oracle索引里保存的是:索引字段+rowid;
2、没有索引:数据排列,不无做分区裁剪,需要做全表扫描,定位数据速度慢;
      分类:
      本地索引--基于分区表创建的索引--分区之间没有关联
                      本地前缀索引  --精确扫描
                       --索引的第一个列等于表的分区列(左侧第一例)
      本地非前缀索引--部分扫描
                       --索引的第一个列不等于表的分区列。
        LOCAL的索引只能是基于表的分区上建索引,不能自己写分区方式
      全局索引--基于数据表创建的索引--分区之间有关联
         全局前缀(分区)索引--必定包含分区字段(表分区本身是不排序);
         全局非前缀(非区分)索引--也全表扫描,因此不存在;
        GLOBAL索引可以不分区,这个时候就是普通的一个索引。
      索引有索引字段
      分区有分区字段
      索引字段里包含分区字段--前缀
      索引字段里不包含分区字段--非前缀
      本地非前缀索引==全局索引(差别不大)
注:
     通过索引扫描,定位rowid,再通过rowid去定位行记录,把这个过程叫回表
索引扫描--》定位rowid            索引段
通过rowid --》定位行记录         数据段

3、本地索引流程:创建表分区--》创建分区索引
1)创建表分区
  create table test (id number,data varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (10000) ,
partition p2 values less than (20000) ,
partition p3 values less than (maxvalue)
);
  查询状态
SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SH' AND TABLE_NAME='TEST';

2)创建索引
  create index id_local on test(id) local;
    命名规则 : ixd_字段
   查询状态
SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SH' AND TABLE_NAME='TEST';
3)删除
drop index id_global;
4、全局分区索引创建
create index i_id_global on test(id ) global
   partition by range(id)
   ( partition p1 values less than (10000) ,
     partition p2 values less than (MAXVALUE)
   );
也可以建全局不分区索引
  create index i_id_global on test(id ) global
全局不分区就不会在数据字典 dba_ind_partition
(它是表分区与本地索引相关联信息)
而是在 dba_indexes
注:1)索引字段值与分区字段值必须一致;
2) 如果存在MAXVALUE,必须切分分区,因为他是最大值,无法在增加了;
3) 表分区发生增加,索引也会跟着发生变化;
    5、分区索引的缺点
   1)不同的SQL语句,不同的业务而言,不同的索引性能表现不同;如果我们需要基于某字段创建索引,提升查询性能,需要进行测试。
   2)创建索引比较占用物理IO
    a)需要从数据段读取索引字段值(数据块人数据文件加载到buffer cache);
       b)排序
          数据量小发生在UGA--逻辑IO
          数据量大发生在临时空间--物理IO
       c)存放在索引段--索引值保存到用户表空间文件的索引段中;
     3)创建索引需要在测试中决定最优方案;
     4)生产环境中需要在夜间创建索引--不影响业务
     5)如果有频繁的增删改操作   
        
重建索引方法:
                1
                 drop index
                 creat index
                2)
                 alter index IND_ID_GLOBAL rebuild
二、执行计划
1、打开执行计划
   set autot trace exp; -->打开执行计划
   set autot off;      -->关闭执行计划
2、执行计划器--选择最优的执行计划,并将最优的计划显示出来
3、分区索引不能决定查询率,执行计划的效率取决于开发人员的SQL语句,由SQL决定采用索引方式,一定要跟公司业务挂钩;
4、注要参数--cost,从下往上看,以最上面的的结果作为最终结果

标签: 暂无标签
fm98888

写了 26 篇文章,拥有财富 107,被 1 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

成为第一个吐槽的人

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈