怎么样在亿级记录表中创建索引

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