分区索引初步理解

最新推荐文章于 2024-09-14 16:57:00 发布

Laughing

最新推荐文章于 2024-09-14 16:57:00 发布

阅读量927

点赞数

分类专栏： Oracle 分区应用文章标签： sql access table disk insert less

本文链接：https://blog.csdn.net/aqszhuaihuai/article/details/6993506

版权

Oracle 分区应用专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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)
----------
      1998

SQL> 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                       237
OUTLN                         21
SYS                         2739

SQL> set autot traceonly
SQL>  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 statement


Statistics
----------------------------------------------------------
          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 processed

SQL>  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 statement


Statistics
----------------------------------------------------------
          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 processed

SQL> 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 statement


Statistics
----------------------------------------------------------
          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 processed

SQL>  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 statement


Statistics
----------------------------------------------------------
          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 processed

SQL> 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 statement


Statistics
----------------------------------------------------------
          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 processed

SQL>  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 statement


Statistics
----------------------------------------------------------
          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