[Oracle]为1.7亿张记录表创建快速索引
作者：柔嘉维则 来源：it专家网 添加时间：2006-5-26 9:53:54本文讲述了在大表上创建索引需要注意的事项，以及整个过程。
1. 查看表的具体情况
是不是分区表，有多少个分区，分区字段：
SQL> col table_name for a20SQL> col column_name for a20SQL> select a.table_name,a.partitioned,b.partition_count,c.column_name 2 from user_tables a, user_part_tables b, user_part_key_columns c 3 where a.table_name='STAT_SUBMIT_CENTER' 4 and b.table_name='STAT_SUBMIT_CENTER' 5 and c.name='STAT_SUBMIT_CENTER';TABLE_NAME PAR PARTITION_COUNT COLUMN_NAME-------------------- --- --------------- --------------------STAT_SUBMIT_CENTER YES 50 MSGDATE
SQL> select segment_name,partition_name,round(bytes/1024/1024) from user_segments where segment_name ='STAT_SUBMIT_CENTER' and bytes/1024/1024>0.25 order by 3 desc;SEGMENT_NAME PARTITION_NAME ROUND(BYTES/1024/1024)-------------------------- ------------------------------ ----------------------STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051101 1722STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051021 1488STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051111 1440STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051121 1355STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051221 1335STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20050911 1309STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051211 1253STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051201 1247STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20050921 1198STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060101 1151STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060111 1068STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051001 1018STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051011 865STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060121 79614 rows selected.
整个表的大小：
SQL> select segment_name,sum(bytes/1024/1024) from user_segments where segment_name ='STAT_SUBMIT_CENTER' group by segment_name;SEGMENT_NAME SUM(BYTES/1024/1024)-------------------------------- --------------------STAT_SUBMIT_CENTER 17234
表的记录数：
SQL> set timing onSQL> select count(*) from STAT_SUBMIT_CENTER; COUNT(*)---------- 170341007Elapsed: 00:14:18.60
还有这个表上的索引情况如下：
table STAT_SUBMIT_CENTER 17234 Mindex IDX_SUBCEN_ADDRUSER 5155 M ADDRUSER PK_STAT_SUBMIT_CENTER 10653 M MSGDATE,ADDRUSER,MSGID
SQL> show parameter workNAME TYPE VALUE------------------------------------ ----------- ------------------------------workarea_size_policy string AUTOSQL> show parameter pgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------pga_aggregate_target big integer 209715200SQL> select * from dba_temp_files;FILE_NAME------------------------------------------------------------------------------------------------------------------------ FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ----------INCREMENT_BY USER_BYTES USER_BLOCKS------------ ---------- -----------/bgdata/oracle/temp01.dbf 1 TEMP 3563061248 434944 AVAILABLE 1 YES 4294967296 524288 6400 3562012672 434816
第 1 页，共 2 页 [1] [2]
站内搜索