为1.7亿张记录表创建快速索引

核心提示： nohup time createind.sh &vi createind.sh#!/bin/shsqlplus user/password <<EOFcreate index IDX_SUBMIT_RECORDTIME on STAT_SUBMIT_CENTER(RECORD

nohup time createind.sh &vi createind.sh#!/bin/shsqlplus user/password <<EOFcreate index IDX_SUBMIT_RECORDTIME on STAT_SUBMIT_CENTER(RECORDTIME) local;exitEOF

4）创建过程中可以观察v$sort_segment，v$sort_usage看排序情况：

select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;select * from v$sort_usage;

5）创建完成后，把tempfile和pga_aggregate_target改回原值：

alter database tempfile '/bgdata/oracle/temp01.dbf' resize 4096m;alter system set pga_aggregate_target=500m;

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 431360SQL> select * from v$sort_usage;no rows selected

2）创建之初，抓到这么一条sql：

insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,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 384640SQL> 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 1254950678TEMP 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