【IT168 技术文档】
摘要：本文讲述了如何在具有亿条数据记录的记录表上创建索引的具体步骤，并附有相关程序源代码，另外，在文章中还介绍了所需要注意的各种事项。
需求：在STAT_SUBMIT_CENTER表的RECORDTIME字段上面创建一索引。
环境：Sun OS 5.9、Oracle 9204、8 CPU、3G Memery
1. 查看表的具体情况
查看是不是分区表，有多少个分区、分区字段：
SQL> col table_name for a20
SQL> col column_name for a20
SQL> 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
SEGMENT_NAME PARTITION_NAME ROUND(BYTES/1024/1024)
-------------------------- ------------------------------ ----------------------
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051101 1722
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051021 1488
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051111 1440
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051121 1355
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051221 1335
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20050911 1309
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051211 1253
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051201 1247
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20050921 1198
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060101 1151
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060111 1068
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051001 1018
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051011 865
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060121 796
14 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
SEGMENT_NAME SUM(BYTES/1024/1024)
-------------------------------- --------------------
STAT_SUBMIT_CENTER 17234
查看表的记录数：
SQL> set timing on
SQL> select count(*) from STAT_SUBMIT_CENTER;
COUNT(*)
----------
170341007
Elapsed: 00:14:18.60
查看这个表上的索引情况如下：
table STAT_SUBMIT_CENTER 17234 M
index IDX_SUBCEN_ADDRUSER 5155 M ADDRUSER
PK_STAT_SUBMIT_CENTER 10653 M MSGDATE,ADDRUSER,MSGID
然后，查看一些数据库参数情况：
SQL> show parameter work
NAME TYPE VALUE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 209715200
SQL> 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 5242886400 3562012672 434816
