分区索引初步理解
来源:互联网 发布:淘宝的懒猫 浪花朵朵 编辑:程序博客网 时间:2024/06/05 08:40
Oracle 分区索引
分区索引类型:局部分区索引与全局分区索引。局部分区索引使用与底层表相同的机制分区;全局分区索引使用与底层表不同的机制进行分区,索引按区间或散列对索引进行分区,一个全局索引分区可能指向任何表分区或所有表分区。
对于local索引,每一个表分区对应一个索引分区,当表的分区发生变化时,索引的维护由Oracle自动进行。
对于global索引,可以选择是否分区,而且索引的分区可以不与表分区相对应。当对分区进行维护操作时,通常会导致全局索引的INVALID,必须在执行完操作后 REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES语句,可以在进行分区维护的同时重建全局索引,UPDATE GLOBAL INDEXES是一种允许用资源耗费的增加来换取可用性的选项。
局部索引比全局索引容易管理,而全局索引比较快。
局部索引多应用于数据仓库环境中,而全局索引多应用于oltp系统中。
局部前缀索引与局部非前缀索引:
如果局部索引的索引列以分区键开头,则称为前缀局部索引。如果局部索引的列不是以分区键开头,或者不包含分区键列,则称为非前缀索引。
像下面这样的查询(a为分区键):
Select … from partitioned_table where a=a:aand b=:b;
Select … from partitioned_table where b=:b;
可以考虑在(b,a)上建立局部非前缀索引。而如果在(a,b)上建立局部前缀索引,第二个查询不会走索引。
create table range(id int, name varchar2(20) , address varchar2(10))partition by range(address)(partition p1 values less than('A') ,partition p2 values less than('F') ,partition p3 values less than('Y') ,partition p4 values less than('Z')) 13 /Table created.SQL> insert into range select rownum,owner,'A' from dba_objects where rownum<1000;999 rows created.SQL> insert into range select rownum + 999,owner,'F' from dba_objects where rownum<1000;999 rows created.SQL> select max(id) from range; MAX(ID)---------- 1998SQL> insert into range select rownum + 999 + + 999,owner,'Y' from dba_objects where rownum<1000;999 rows created.SQL> commit;Commit complete.SQL> select name,count(name) from range group by name;NAME COUNT(NAME)-------------------- -----------PUBLIC 237OUTLN 21SYS 2739SQL> set autot traceonlySQL> select id,name,address from range where address='Y' and name='OUTLN';7 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 955737907------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 7 | 224 | 2 (0)| 00:00:01 | | || 1 | PARTITION RANGE SINGLE| | 7 | 224 | 2 (0)| 00:00:01 | 4 | 4 ||* 2 | TABLE ACCESS FULL | RANGE | 7 | 224 | 2 (0)| 00:00:01 | 4 | 4 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("ADDRESS"='Y' AND "NAME"='OUTLN')Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 610 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 7 rows processedSQL> select id,name,address from range where name='OUTLN';21 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 184025858---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 21 | 672 | 4 (0)| 00:00:01 | | || 1 | PARTITION RANGE ALL| | 21 | 672 | 4 (0)| 00:00:01 | 1 | 4 ||* 2 | TABLE ACCESS FULL | RANGE | 21 | 672 | 4 (0)| 00:00:01 | 1 | 4 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("NAME"='OUTLN')Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 5 recursive calls 0 db block gets 42 consistent gets 0 physical reads 0 redo size 867 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 21 rows processedSQL> create index idx_pre on range(address,name) local; --建立前缀索引Index created.SQL> select id,name,address from range where address='Y' and name='OUTLN';7 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1638943077--------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 7 | 224 | 2 (0)| 00:00:01 | | || 1 | PARTITION RANGE SINGLE | | 7 | 224 | 2 (0)| 00:00:01 | 4 | 4 || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| RANGE | 7 | 224 | 2 (0)| 00:00:01 | 4 | 4 ||* 3 | INDEX RANGE SCAN | IDX_PRE | 1 | | 1 (0)| 00:00:01 | 4 | 4 |--------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("ADDRESS"='Y' AND "NAME"='OUTLN')Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 610 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 7 rows processedSQL> select /*+index(range idx_pre)*/ id,name,address from range where name='OUTLN'; --没有使用索引21 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 184025858---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 21 | 672 | 4 (0)| 00:00:01 | | || 1 | PARTITION RANGE ALL| | 21 | 672 | 4 (0)| 00:00:01 | 1 | 4 ||* 2 | TABLE ACCESS FULL | RANGE | 21 | 672 | 4 (0)| 00:00:01 | 1 | 4 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("NAME"='OUTLN')Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 5 recursive calls 0 db block gets 42 consistent gets 0 physical reads 0 redo size 867 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 21 rows processedSQL> create index idx_nonpre on range(name,address) local;Index created.SQL> select id,name,address from range where address='Y' and name='OUTLN';7 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1326523914-----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 7 | 224 | 2 (0)| 00:00:01 | | || 1 | PARTITION RANGE SINGLE | | 7 | 224 | 2 (0)| 00:00:01 | 4 | 4 || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| RANGE | 7 | 224 | 2 (0)| 00:00:01 | 4 | 4 ||* 3 | INDEX RANGE SCAN | IDX_NONPRE | 1 | | 1 (0)| 00:00:01 | 4 | 4 |-----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("NAME"='OUTLN' AND "ADDRESS"='Y')Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 610 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 7 rows processedSQL> select /*+index(range idx_nonpre)*/ id,name,address from range where name='OUTLN'; --使用索引21 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 279219031-----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 21 | 672 | 11 (0)| 00:00:01 | | || 1 | PARTITION RANGE ALL | | 21 | 672 | 11 (0)| 00:00:01 | 1 | 4 || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| RANGE | 21 | 672 | 11 (0)| 00:00:01 | 1 | 4 ||* 3 | INDEX RANGE SCAN | IDX_NONPRE | 2 | | 5 (0)| 00:00:01 | 1 | 4 |-----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("NAME"='OUTLN')Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 0 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 867 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 21 rows processed
- 分区索引初步理解
- 索引分区
- 分区索引
- 分区索引
- 索引分区
- 分区索引
- 分区索引
- 分区索引
- 分区索引
- 分区索引
- 【索引分类】分区索引
- 深入理解 Oracle 分区(3):分区表和分区索引概述
- 深入理解Oracle索引(6):在实践中初步认识3大索引的使用场景
- 分区理解
- Oracle表分区、索引分区
- 分区与分区索引基本概念
- 表分区和分区索引
- 分组分区和索引分区
- SQL Server 2008可视化设置外键
- C语言学习笔记(23补码)
- 微软-测磁盘旋转方向
- Maven常用命令
- java三种方式实现文件的上传
- 分区索引初步理解
- 2011年11月18日 苹果App Store支持人民币支付
- [USACO 1.2.2] Transformations
- 计算一个数的二进制形式有多少个1
- 二分查找的非递归程序(折半查找)
- 10进制数转换为二进制
- 第一卷清晨的帝国 第一百四十八章 一夜无言观山景
- SQL Server 2008可视化设置键唯一
- oracle学习笔记(一)之逻辑结构和连接