oracle 区间索引,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)上建立局部前缀索引，第二个查询不会走索引。

createtablerange

(idint,

namevarchar2(20) ,

address varchar2(10)

)

partitionbyrange(address)

(

partition p1valuesless than('A') ,

partition p2valuesless than('F') ,

partition p3valuesless than('Y') ,

partition p4valuesless than('Z')

)

13  /

Tablecreated.

SQL>insertintorangeselectrownum,owner,'A'fromdba_objectswhererownum<1000;

999rowscreated.

SQL>insertintorangeselectrownum + 999,owner,'F'fromdba_objectswhererownum<1000;

999rowscreated.

SQL>selectmax(id)fromrange;

MAX(ID)

----------

1998

SQL>insertintorangeselectrownum + 999 + + 999,owner,'Y'fromdba_objectswhererownum<1000;

999rowscreated.

SQL>commit;

Commitcomplete.

SQL>selectname,count(name)fromrangegroupbyname;

NAMECOUNT(NAME)

-------------------- -----------

PUBLIC237

OUTLN                         21

SYS                         2739

SQL>setautot traceonly

SQL>selectid,name,addressfromrangewhereaddress='Y'andname='OUTLN';

7rowsselected.

Execution Plan

----------------------------------------------------------

Plan hash value: 955737907

------------------------------------------------------------------------------------------------

| Id  | Operation              |Name|Rows| Bytes | Cost (%CPU)|Time| Pstart| Pstop |

------------------------------------------------------------------------------------------------

|   0 |SELECTSTATEMENT       |       |     7 |   224 |     2   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE|       |     7 |   224 |     2   (0)| 00:00:01 |     4 |     4 |

|*  2 |TABLEACCESSFULL| RANGE |     7 |   224 |     2   (0)| 00:00:01 |     4 |     4 |

------------------------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):

---------------------------------------------------

2 - filter("ADDRESS"='Y'AND"NAME"='OUTLN')

Note

-----

-dynamicsampling usedforthis statement

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

6  consistent gets

0  physical reads

0  redosize

610  bytes sent via SQL*Nettoclient

385  bytes received via SQL*Netfromclient

2  SQL*Net roundtripsto/fromclient

0  sorts (memory)

0  sorts (disk)

7rowsprocessed

SQL>selectid,name,addressfromrangewherename='OUTLN';

21rowsselected.

Execution Plan

----------------------------------------------------------

Plan hash value: 184025858

---------------------------------------------------------------------------------------------

| Id  | Operation           |Name|Rows| Bytes | Cost (%CPU)|Time| Pstart| Pstop |

---------------------------------------------------------------------------------------------

|   0 |SELECTSTATEMENT    |       |    21 |   672 |     4   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGEALL|       |    21 |   672 |     4   (0)| 00:00:01 |     1 |     4 |

|*  2 |TABLEACCESSFULL| RANGE |    21 |   672 |     4   (0)| 00:00:01 |     1 |     4 |

---------------------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):

---------------------------------------------------

2 - filter("NAME"='OUTLN')

Note

-----

-dynamicsampling usedforthis statement

Statistics

----------------------------------------------------------

5  recursive calls

0  db block gets

42  consistent gets

0  physical reads

0  redosize

867  bytes sent via SQL*Nettoclient

396  bytes received via SQL*Netfromclient

3  SQL*Net roundtripsto/fromclient

0  sorts (memory)

0  sorts (disk)

21rowsprocessed

SQL>createindexidx_preonrange(address,name)local;--建立前缀索引

Indexcreated.

SQL>selectid,name,addressfromrangewhereaddress='Y'andname='OUTLN';

7rowsselected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1638943077

--------------------------------------------------------------------------------------------------------------

| Id  | Operation                          |Name|Rows| Bytes | Cost (%CPU)|Time| Pstart| Pstop |

--------------------------------------------------------------------------------------------------------------

|   0 |SELECTSTATEMENT                   |         |     7 |   224 |     2   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE            |         |     7 |   224 |     2   (0)| 00:00:01 |     4 |     4 |

|   2 |TABLEACCESSBYLOCALINDEXROWID| RANGE   |     7 |   224 |     2   (0)| 00:00:01 |     4 |     4 |

|*  3 |INDEXRANGE SCAN                | IDX_PRE |     1 |       |     1   (0)| 00:00:01 |     4 |     4 |

--------------------------------------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):

---------------------------------------------------

3 - access("ADDRESS"='Y'AND"NAME"='OUTLN')

Note

-----

-dynamicsampling usedforthis statement

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

5  consistent gets

0  physical reads

0  redosize

610  bytes sent via SQL*Nettoclient

385  bytes received via SQL*Netfromclient

2  SQL*Net roundtripsto/fromclient

0  sorts (memory)

0  sorts (disk)

7rowsprocessed

SQL>select/*+index(range idx_pre)*/ id,name,addressfromrangewherename='OUTLN';--没有使用索引

21rowsselected.

Execution Plan

----------------------------------------------------------

Plan hash value: 184025858

---------------------------------------------------------------------------------------------

| Id  | Operation           |Name|Rows| Bytes | Cost (%CPU)|Time| Pstart| Pstop |

---------------------------------------------------------------------------------------------

|   0 |SELECTSTATEMENT    |       |    21 |   672 |     4   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGEALL|       |    21 |   672 |     4   (0)| 00:00:01 |     1 |     4 |

|*  2 |TABLEACCESSFULL| RANGE |    21 |   672 |     4   (0)| 00:00:01 |     1 |     4 |

---------------------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):

---------------------------------------------------

2 - filter("NAME"='OUTLN')

Note

-----

-dynamicsampling usedforthis statement

Statistics

----------------------------------------------------------

5  recursive calls

0  db block gets

42  consistent gets

0  physical reads

0  redosize

867  bytes sent via SQL*Nettoclient

396  bytes received via SQL*Netfromclient

3  SQL*Net roundtripsto/fromclient

0  sorts (memory)

0  sorts (disk)

21rowsprocessed

SQL>createindexidx_nonpreonrange(name,address)local;

Indexcreated.

SQL>selectid,name,addressfromrangewhereaddress='Y'andname='OUTLN';

7rowsselected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1326523914

-----------------------------------------------------------------------------------------------------------------

| Id  | Operation                          |Name|Rows| Bytes | Cost (%CPU)|Time| Pstart| Pstop |

-----------------------------------------------------------------------------------------------------------------

|   0 |SELECTSTATEMENT                   |            |     7 |   224 |     2   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE            |            |     7 |   224 |     2   (0)| 00:00:01 |     4 |     4 |

|   2 |TABLEACCESSBYLOCALINDEXROWID| RANGE      |     7 |   224 |     2   (0)| 00:00:01 |     4 |     4 |

|*  3 |INDEXRANGE SCAN                | IDX_NONPRE |     1 |       |     1   (0)| 00:00:01 |     4 |     4 |

-----------------------------------------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):

---------------------------------------------------

3 - access("NAME"='OUTLN'AND"ADDRESS"='Y')

Note

-----

-dynamicsampling usedforthis statement

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

5  consistent gets

0  physical reads

0  redosize

610  bytes sent via SQL*Nettoclient

385  bytes received via SQL*Netfromclient

2  SQL*Net roundtripsto/fromclient

0  sorts (memory)

0  sorts (disk)

7rowsprocessed

SQL>select/*+index(range idx_nonpre)*/ id,name,addressfromrangewherename='OUTLN';--使用索引

21rowsselected.

Execution Plan

----------------------------------------------------------

Plan hash value: 279219031

-----------------------------------------------------------------------------------------------------------------

| Id  | Operation                          |Name|Rows| Bytes | Cost (%CPU)|Time| Pstart| Pstop |

-----------------------------------------------------------------------------------------------------------------

|   0 |SELECTSTATEMENT                   |            |    21 |   672 |    11   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGEALL|            |    21 |   672 |    11   (0)| 00:00:01 |     1 |     4 |

|   2 |TABLEACCESSBYLOCALINDEXROWID| RANGE      |    21 |   672 |    11   (0)| 00:00:01 |     1 |     4 |

|*  3 |INDEXRANGE SCAN                | IDX_NONPRE |     2 |       |     5   (0)| 00:00:01 |     1 |     4 |

-----------------------------------------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):

---------------------------------------------------

3 - access("NAME"='OUTLN')

Note

-----

-dynamicsampling usedforthis statement

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

14  consistent gets

0  physical reads

0  redosize

867  bytes sent via SQL*Nettoclient

396  bytes received via SQL*Netfromclient

3  SQL*Net roundtripsto/fromclient

0  sorts (memory)

0  sorts (disk)

21rowsprocessed