4. 实际创建过程中观察到的情况
1）开始之前：
SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------- ------------ ----------- -----------
TEMP 0 431360 0 431360
SQL> select * from v$sort_usage;
no rows selected
2）创建之初，抓到这么一条sql：
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,st
ime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spar
e1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,
:15,:16, :17)
3）然后v$sort_segment.USED_BLOCKS变大，v$sort_usage.BLOCKS变大，一直增长到：
SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------- ------------ ----------- -----------
TEMP 1 431360 46720 384640
SQL> select * from v$sort_usage;
USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH
------------------------------ ------------------------------ ---------------- ----------- ---------------- ----------
TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
DPC DPC 00000003974CFFB0 6134 0000000399CAB288 1254950678
TEMP TEMPORARY SORT 201 431113 365 46720 1
这个过程中抓到的sql为：
select file# from file$ where ts#=:1
4）v$sort_segment.USED_BLOCKS变为0，v$sort_usage.BLOCKS变为0。
5）重复3，4两步，估计这个是创建一个分区的索引。
需要解释一下的是，上面的sql只是我随机抓到的运行时间比较长的，整个create index过程会复杂很多，具体怎么样可以用sqltrace跟踪。这里主要看的是temp表空间的使用情况。
同时，在创建的过程中：
SQL> select segment_name,partition_name from user_segments where segment_name='IDX_SUBMIT_RECORDTIME';
no rows selected
SQL> select index_name,partition_name from user_ind_partitions where INDEX_NAME='IDX_SUBMIT_RECORDTIME';
no rows selected
当时忘了查user_segments中其实是有一个segment_name为一串数字的记录，那个才是正在创建的索引；如果这个事务失败了，将回滚。
最后耗时99分钟完成。
5. 创建完成后分析索引
但是接下来还有一件事。创建完成后要分析索引，否则就是走了索引，查询速度也会很慢。
SQL> explain plan for select count(*) from stat_submit_center where recordtime>trunc(sysdate);
Explained.
SQL> @?/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | |
| 2 | PARTITION RANGE ALL | | | | | 1 | 50 |
|* 3 | INDEX FAST FULL SCAN| IDX_SUBMIT_RECORDTIME | 8878K| 76M| 4 | 1 | 50 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("STAT_SUBMIT_CENTER"."RECORDTIME">TRUNC(SYSDATE@!))
Note: cpu costing is off
16 rows selected.
SQL> set autotrace on explain
SQL> set timing on
SQL> select count(*) from stat_submit_center where recordtime>trunc(sysdate);
aa^Cselect count(*) from stat_submit_center where recordtime>trunc(sysdate)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:11:49.85
SQL>
SQL> set autotrace off
上面可以看到，因为没有分析索引，虽然它走的是新建的IDX_SUBMIT_RECORDTIME索引，但是查询速度很慢，10分钟后也没有结果。下面我们分析一下：
SQL> Analyze index IDX_SUBMIT_RECORDTIME estimate statistics;
Index analyzed.
Elapsed: 00:00:06.84
SQL> set autotrace on explain
SQL> select count(*) from stat_submit_center where recordtime>trunc(sysdate);
COUNT(*)
----------
926736
Elapsed: 00:00:05.37
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4360 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 INDEX (RANGE SCAN) OF 'IDX_SUBMIT_RECORDTIME' (NON-UNI
QUE) (Cost=4360 Card=8878740 Bytes=79908660)
SQL> set autotrace off
索引分析之后，查询时间为5分钟左右，效率大大提高。
至此，完成全部操作。
作者简介：柔嘉维则；作者Email地址为baobaoc@hotmail.com；作者Blog为http://spaces.msn.com/roujiaweize/
