一, 创建索引注意事项
1, 预估索引大小
2, 查看索引表空间可用空间大小
3, 查看临时表空间大小
4, 业务空闲期创建
5, 可以考虑在线, 并行, nologging 模式
二, 预估索引方法
操作步骤如下:
SQL> explain plan for create index idx_object_id on tt(object_id) ;
Explained.
SQL> set lines 200 pages 1000
- SQL> select * from table(dbms_xplan.display());
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 3757763116
- ----------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------
- | 0 | CREATE INDEX STATEMENT | | 2762K| 13M| 22958 (1)| 00:04:36 |
| 1 | INDEX BUILD NON UNIQUE| IDX_OBJECT_ID | | | | |
- | 2 | SORT CREATE INDEX | | 2762K| 13M| | |
- | 3 | TABLE ACCESS FULL | TT | 2762K| 13M| 21560 (1)| 00:04:19 |
- ----------------------------------------------------------------------------------------
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Note
- -----
- - estimated index size: 67M bytes
14 rows selected.
- Elapsed: 00:00:00.01
- SQL>
来源: http://www.bubuko.com/infodetail-2565354.html