Welcome 微信登录

首页 / 数据库 / MySQL / 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）上建立局部前缀索引，第二个查询不会走索引。
  1. create table range  
  2. （id int,  
  3.  name varchar2（20） ,  
  4.  address varchar2（10）  
  5. ）  
  6. partition by range（address）  
  7. （  
  8. partition p1 values less than（"A"） ,  
  9. partition p2 values less than（"F"） ,  
  10. partition p3 values less than（"Y"） ,  
  11. partition p4 values less than（"Z"）  
  12. ）  
  13.  13  /  
  14.   
  15. Table created.  
  16.   
  17. SQL> insert into range select rownum,owner,"A" from dba_objects where rownum<1000;  
  18.   
  19. 999 rows created.  
  20.   
  21. SQL> insert into range select rownum + 999,owner,"F" from dba_objects where rownum<1000;  
  22.   
  23. 999 rows created.  
  24.   
  25. SQL> select max（id） from range;  
  26.   
  27.    MAX（ID）  
  28. ----------   
  29.       1998  
  30.   
  31. SQL> insert into range select rownum + 999 + + 999,owner,"Y" from dba_objects where rownum<1000;  
  32.   
  33. 999 rows created.  
  34.   
  35. SQL> commit;  
  36.   
  37. Commit complete.  
  38.   
  39. SQL> select name,count（name） from range group by name;  
  40.   
  41. NAME                 COUNT（NAME）  
  42. -------------------- -----------   
  43. PUBLIC                       237  
  44. OUTLN                         21  
  45. SYS                         2739  
  46.   
  47. SQL> set autot traceonly  
  48. SQL>  select id,name,address from range where address="Y" and name="OUTLN";  
  49.   
  50. 7 rows selected.  
  51.   
  52.   
  53. Execution Plan  
  54. ----------------------------------------------------------   
  55. Plan hash value: 955737907  
  56.   
  57. ------------------------------------------------------------------------------------------------   
  58. | Id  | Operation              | Name  | Rows  | Bytes | Cost （%CPU）| Time     | Pstart| Pstop |  
  59. ------------------------------------------------------------------------------------------------   
  60. |   0 | SELECT STATEMENT       |       |     7 |   224 |     2   （0）| 00:00:01 |       |       |  
  61. |   1 |  PARTITION RANGE SINGLE|       |     7 |   224 |     2   （0）| 00:00:01 |     4 |     4 |  
  62. |*  2 |   TABLE ACCESS FULL    | RANGE |     7 |   224 |     2   （0）| 00:00:01 |     4 |     4 |  
  63. ------------------------------------------------------------------------------------------------   
  64.   
  65. Predicate Information （identified by operation id）:  
  66. ---------------------------------------------------   
  67.   
  68.    2 - filter（"ADDRESS"="Y" AND "NAME"="OUTLN"）  
  69.   
  70. Note  
  71. -----   
  72.    - dynamic sampling used for this statement  
  73.   
  74.   
  75. Statistics  
  76. ----------------------------------------------------------   
  77.           0  recursive calls  
  78.           0  db block gets  
  79.           6  consistent gets  
  80.           0  physical reads  
  81.           0  redo size  
  82.         610  bytes sent via SQL*Net to client  
  83.         385  bytes received via SQL*Net from client  
  84.           2  SQL*Net roundtrips to/from client  
  85.           0  sorts （memory）  
  86.           0  sorts （disk）  
  87.           7  rows processed  
  88.   
  89. SQL>  select id,name,address from range where  name="OUTLN";  
  90.   
  91. 21 rows selected.  
  92.   
  93.   
  94. Execution Plan  
  95. ----------------------------------------------------------   
  96. Plan hash value: 184025858  
  97.   
  98. ---------------------------------------------------------------------------------------------   
  99. | Id  | Operation           | Name  | Rows  | Bytes | Cost （%CPU）| Time     | Pstart| Pstop |  
  100. ---------------------------------------------------------------------------------------------   
  101. |   0 | SELECT STATEMENT    |       |    21 |   672 |     4   （0）| 00:00:01 |       |       |  
  102. |   1 |  PARTITION RANGE ALL|       |    21 |   672 |     4   （0）| 00:00:01 |     1 |     4 |  
  103. |*  2 |   TABLE ACCESS FULL | RANGE |    21 |   672 |     4   （0）| 00:00:01 |     1 |     4 |  
  104. ---------------------------------------------------------------------------------------------   
  105.   
  106. Predicate Information （identified by operation id）:  
  107. ---------------------------------------------------   
  108.   
  109.    2 - filter（"NAME"="OUTLN"）  
  110.   
  111. Note  
  112. -----   
  113.    - dynamic sampling used for this statement  
  114.   
  115.   
  116. Statistics  
  117. ----------------------------------------------------------   
  118.           5  recursive calls  
  119.           0  db block gets  
  120.          42  consistent gets  
  121.           0  physical reads  
  122.           0  redo size  
  123.         867  bytes sent via SQL*Net to client  
  124.         396  bytes received via SQL*Net from client  
  125.           3  SQL*Net roundtrips to/from client  
  126.           0  sorts （memory）  
  127.           0  sorts （disk）  
  128.          21  rows processed  
  129.   
  130. SQL> create index idx_pre on range（address,name） local; --建立前缀索引   
  131.   
  132. Index created.  
  133.   
  134. SQL>  select id,name,address from range where address="Y" and name="OUTLN";  
  135.   
  136. 7 rows selected.  
  137.   
  138.   
  139. Execution Plan  
  140. ----------------------------------------------------------   
  141. Plan hash value: 1638943077  
  142.   
  143. --------------------------------------------------------------------------------------------------------------   
  144. | Id  | Operation                          | Name    | Rows  | Bytes | Cost （%CPU）| Time     | Pstart| Pstop |  
  145. --------------------------------------------------------------------------------------------------------------   
  146. |   0 | SELECT STATEMENT                   |         |     7 |   224 |     2   （0）| 00:00:01 |       |       |  
  147. |   1 |  PARTITION RANGE SINGLE            |         |     7 |   224 |     2   （0）| 00:00:01 |     4 |     4 |  
  148. |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| RANGE   |     7 |   224 |     2   （0）| 00:00:01 |     4 |     4 |  
  149. |*  3 |    INDEX RANGE SCAN                | IDX_PRE |     1 |       |     1   （0）| 00:00:01 |     4 |     4 |  
  150. --------------------------------------------------------------------------------------------------------------   
  151.   
  152. Predicate Information （identified by operation id）:  
  153. ---------------------------------------------------   
  154.   
  155.    3 - access（"ADDRESS"="Y" AND "NAME"="OUTLN"）  
  156.   
  157. Note  
  158. -----   
  159.    - dynamic sampling used for this statement  
  160.   
  161.   
  162. Statistics  
  163. ----------------------------------------------------------   
  164.           0  recursive calls  
  165.           0  db block gets  
  166.           5  consistent gets  
  167.           0  physical reads  
  168.           0  redo size  
  169.         610  bytes sent via SQL*Net to client  
  170.         385  bytes received via SQL*Net from client  
  171.           2  SQL*Net roundtrips to/from client  
  172.           0  sorts （memory）  
  173.           0  sorts （disk）  
  174.           7  rows processed  
  175.   
  176. SQL>  select /*+index（range idx_pre）*/ id,name,address from range where  name="OUTLN"; --没有使用索引   
  177.   
  178. 21 rows selected.  
  179.   
  180.   
  181. Execution Plan  
  182. ----------------------------------------------------------   
  183. Plan hash value: 184025858  
  184.   
  185. ---------------------------------------------------------------------------------------------   
  186. | Id  | Operation           | Name  | Rows  | Bytes | Cost （%CPU）| Time     | Pstart| Pstop |  
  187. ---------------------------------------------------------------------------------------------   
  188. |   0 | SELECT STATEMENT    |       |    21 |   672 |     4   （0）| 00:00:01 |       |       |  
  189. |   1 |  PARTITION RANGE ALL|       |    21 |   672 |     4   （0）| 00:00:01 |     1 |     4 |  
  190. |*  2 |   TABLE ACCESS FULL | RANGE |    21 |   672 |     4   （0）| 00:00:01 |     1 |     4 |  
  191. ---------------------------------------------------------------------------------------------   
  192.   
  193. Predicate Information （identified by operation id）:  
  194. ---------------------------------------------------   
  195.   
  196.    2 - filter（"NAME"="OUTLN"）  
  197.   
  198. Note  
  199. -----   
  200.    - dynamic sampling used for this statement  
  201.   
  202.   
  203. Statistics  
  204. ----------------------------------------------------------   
  205.           5  recursive calls  
  206.           0  db block gets  
  207.          42  consistent gets  
  208.           0  physical reads  
  209.           0  redo size  
  210.         867  bytes sent via SQL*Net to client  
  211.         396  bytes received via SQL*Net from client  
  212.           3  SQL*Net roundtrips to/from client  
  213.           0  sorts （memory）  
  214.           0  sorts （disk）  
  215.          21  rows processed  
  216.   
  217. SQL> create index idx_nonpre on range（name,address） local;  
  218.   
  219. Index created.  
  220.   
  221. SQL>  select id,name,address from range where address="Y" and name="OUTLN";  
  222.   
  223. 7 rows selected.  
  224.   
  225.   
  226. Execution Plan  
  227. ----------------------------------------------------------   
  228. Plan hash value: 1326523914  
  229.   
  230. -----------------------------------------------------------------------------------------------------------------   
  231. | Id  | Operation                          | Name       | Rows  | Bytes | Cost （%CPU）| Time     | Pstart| Pstop |  
  232. -----------------------------------------------------------------------------------------------------------------   
  233. |   0 | SELECT STATEMENT                   |            |     7 |   224 |     2   （0）| 00:00:01 |       |       |  
  234. |   1 |  PARTITION RANGE SINGLE            |            |     7 |   224 |     2   （0）| 00:00:01 |     4 |     4 |  
  235. |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| RANGE      |     7 |   224 |     2   （0）| 00:00:01 |     4 |     4 |  
  236. |*  3 |    INDEX RANGE SCAN                | IDX_NONPRE |     1 |       |     1   （0）| 00:00:01 |     4 |     4 |  
  237. -----------------------------------------------------------------------------------------------------------------   
  238.   
  239. Predicate Information （identified by operation id）:  
  240. ---------------------------------------------------   
  241.   
  242.    3 - access（"NAME"="OUTLN" AND "ADDRESS"="Y"）  
  243.   
  244. Note  
  245. -----   
  246.    - dynamic sampling used for this statement  
  247.   
  248.   
  249. Statistics  
  250. ----------------------------------------------------------   
  251.           0  recursive calls  
  252.           0  db block gets  
  253.           5  consistent gets  
  254.           0  physical reads  
  255.           0  redo size  
  256.         610  bytes sent via SQL*Net to client  
  257.         385  bytes received via SQL*Net from client  
  258.           2  SQL*Net roundtrips to/from client  
  259.           0  sorts （memory）  
  260.           0  sorts （disk）  
  261.           7  rows processed  
  262.   
  263. SQL>  select /*+index（range idx_nonpre）*/ id,name,address from range where  name="OUTLN"; --使用索引   
  264.   
  265. 21 rows selected.  
  266.   
  267.   
  268. Execution Plan  
  269. ----------------------------------------------------------   
  270. Plan hash value: 279219031  
  271.   
  272. -----------------------------------------------------------------------------------------------------------------   
  273. | Id  | Operation                          | Name       | Rows  | Bytes | Cost （%CPU）| Time     | Pstart| Pstop |  
  274. -----------------------------------------------------------------------------------------------------------------   
  275. |   0 | SELECT STATEMENT                   |            |    21 |   672 |    11   （0）| 00:00:01 |       |       |  
  276. |   1 |  PARTITION RANGE ALL               |            |    21 |   672 |    11   （0）| 00:00:01 |     1 |     4 |  
  277. |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| RANGE      |    21 |   672 |    11   （0）| 00:00:01 |     1 |     4 |  
  278. |*  3 |    INDEX RANGE SCAN                | IDX_NONPRE |     2 |       |     5   （0）| 00:00:01 |     1 |     4 |  
  279. -----------------------------------------------------------------------------------------------------------------   
  280.   
  281. Predicate Information （identified by operation id）:  
  282. ---------------------------------------------------   
  283.   
  284.    3 - access（"NAME"="OUTLN"）  
  285.   
  286. Note  
  287. -----   
  288.    - dynamic sampling used for this statement  
  289.   
  290.   
  291. Statistics  
  292. ----------------------------------------------------------   
  293.           0  recursive calls  
  294.           0  db block gets  
  295.          14  consistent gets  
  296.           0  physical reads  
  297.           0  redo size  
  298.         867  bytes sent via SQL*Net to client  
  299.         396  bytes received via SQL*Net from client  
  300.           3  SQL*Net roundtrips to/from client  
  301.           0  sorts （memory）  
  302.           0  sorts （disk）  
  303.          21  rows processed  
Oracle 10g AWR Report 分析Oracle 自动段空间管理（ASSM）相关资讯      Oracle数据库  Oracle入门教程  oracle数据库教程 
  • Oracle数据库全球化  （03月01日）
  • Oracle数据库日期过滤方法性能比较  （02/02/2015 13:20:26）
  • Oracle数据库安装中端口被占用问题  （10/29/2014 07:42:24）
  • 在CentOS 6.6上搭建C++运行环境并  （10/10/2015 19:44:40）
  • Oracle数据库无法使用localhost和  （11/14/2014 16:39:10）
  • 使用SQLT来构建Oracle测试用例  （08/28/2014 06:17:41）
本文评论 查看全部评论 （0）
表情： 姓名： 字数