--索引分区(本地索引和全局索引)
对索引分区有以下两种方法:
按表分区的方式对索引分区:这也称为本地索引(local index)。每个表分区都有一个索引分区,而且这个索引分区只会对这个表分区中的数据进行索引。
同一索引分区中的所有条目都指向一个表分区,同一表分区中的所有行都会放在一个索引分区中。
按区间或散列对索引分区:这也称为全局分区索引(globally partitioned index)。索引可以按区间分区,一个索引分区可以指向任何表分区。
--本地索引
本地索引分为两类:
本地前缀索引(local prefixed index):在这类索引中,分区键在索引定义的前几列上。
本地非前缀索引(local nonprefixed index):这些索引不以表的分区键作为它的前几列。索引可能包含表的分区键列,也可能不包含。
1.分区修剪行为:如果查询首先访问索引,他是否能够修建分区完全取决于查询中的谓词。
EODA@PROD1> CREATE TABLE partitioned_table
2 ( a int,
3 b int,
4 data char(20)
5 )
6 PARTITION BY RANGE (a)
7 (
8 PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
9 PARTITION part_2 VALUES LESS THAN(3) tablespace p2
10 )
11 /
Table created.
EODA@PROD1>
EODA@PROD1> create index local_prefixed on partitioned_table (a,b) local; --创建本地前缀索引
Index created.
EODA@PROD1> create index local_nonprefixed on partitioned_table (b) local; --创建本地非前缀索引
Index created.
EODA@PROD1>
EODA@PROD1> insert into partitioned_table
2 select mod(rownum-1,2)+1, rownum, 'x'
3 from dual connect by level <= 70000;
70000 rows created.
EODA@PROD1> begin
2 dbms_stats.gather_table_stats
3 ( user,
4 'PARTITIONED_TABLE',
5 cascade=>TRUE );
6 end;
7 /
PL/SQL procedure successfully completed.
EODA@PROD1> alter tablespace p2 offline; --讲p2放于离线状态,模拟介质故障
Tablespace altered.
EODA@PROD1> select * from partitioned_table where a = 1 and b = 1;
A B DATA
---------- ---------- --------------------
1 1 x
EODA@PROD1> explain plan for select * from partitioned_table where a = 1 and b = 1;
Explained.
EODA@PROD1> set lines 132
EODA@PROD1> select * from table(dbms_xplan.display); --PSTART分区开始,PSTOP准确显示哪些分区在线且可用
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1622054381
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 29 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 29 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1 AND "B"=1)
15 rows selected.
EODA@PROD1> select * from partitioned_table where b=1; --这个查询却失败了
ERROR:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_p2_d24hxpq0_.dbf'
no rows selected
EODA@PROD1> explain plan for select * from partitioned_table where b = 1;
Explained.
EODA@PROD1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 440752652
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 29 | 4 (0)| 00:00:01 | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 29 | 4 (0)| 00:00:01 | 1 | 2 |
|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 3 (0)| 00:00:01 | 1 | 2 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=1)
15 rows selected.
--PSTOP为2,说明在使用本地非前缀索引的时候必须考虑part_2,但是表空间已经离线所以失败。
EODA@PROD1> drop index local_prefixed; --然后删除本地前缀索引
Index dropped.
EODA@PROD1> select * from partitioned_table where a = 1 and b = 1; --再次查询成功的查询
A B DATA
---------- ---------- --------------------
1 1 x
EODA@PROD1> explain plan for select * from partitioned_table where a = 1 and b = 1;
Explained.
EODA@PROD1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 904532382
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
索引分区(本地索引和全局索引)
最新推荐文章于 2024-08-10 07:57:54 发布
本文介绍了Oracle数据库中的索引分区,包括本地索引(本地前缀和非前缀索引)和全局索引的概念及使用。通过示例展示了不同索引类型在查询时的分区修剪行为,以及11g和12c版本中全局索引维护的差异。此外,还提到了不完全索引的特性,允许对部分分区进行索引。
摘要由CSDN通过智能技术生成