创建分区索引的优势(即使在非分区表上也适用 ）

--purview是一个非分区表

SQL> select table_name,partitioned from ALL_TABLES t where t.table_name='PURVIEW' ;

TABLE_NAME PARTITIONED
------------------------------ -----------
PURVIEW NO

--创建分区索引前,查看explain plan
SQL> explain plan for select * from purview where id<400;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4207087333
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 390 | 70590 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PURVIEW | 390 | 70590 | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<400)

13 rows selected

SQL>

--非分区表创建全局分区索引
create unique index idx_purview_id on purview(id)
global PARTITION by RANGE(id)
(PARTITION p1 values less than (300),
PARTITION p2 values less than (600),
PARTITION p3 values less than(MAXVALUE));
--创建主键时，自动使用分区索引
alter table PURVIEW
add constraint PK_PURVIEW primary key (ID) ;
--但是重建索引时需要分区重建
alter index idx_purview_id REBUILD PARTITION p1 tablespace test_index;
alter index idx_purview_id REBUILD PARTITION p2 tablespace test_index;
alter index idx_purview_id REBUILD PARTITION p3 tablespace test_index;

--创建分区索引后查看explain plan
SQL> explain plan for select * from purview t where t.id<400;

Explained

SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1251419756
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1253 | 5
| 1 | PARTITION RANGE ITERATOR | | 7 | 1253 | 5
| 2 | TABLE ACCESS BY INDEX ROWID| PURVIEW | 7 | 1253 | 5
|* 3 | INDEX RANGE SCAN | IDX_PURVIEW_ID | 7 | | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."ID"<400)

15 rows selected

从explain plan看，创建分区索引的优势明显。如果在大表上创建优势会更明显。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16298743/viewspace-1054922/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16298743/viewspace-1054922/