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值,从下往上看，以最上面的的结果作为最终结果
页:
[1]