在亿级记录表中创建索引-oracle数据库

[Oracle]如何在亿级记录表中创建索引

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

2from user_tables a,user_part_tables b,user_part_key_columns c

3where a.table_name='STAT_SUBMIT_CENTER'

4and b.table_name='STAT_SUBMIT_CENTER'

5and ='STAT_SUBMIT_CENTER';

TABLE_NAME PAR PARTITION_COUNT COLUMN_NAME

----------------------------------------------------------

STAT_SUBMIT_CENTER YES50MSGDATE

查看已使用的每个分区的大小：

SQL>select segment_name,partition_name,round(bytes/1024/1024)from user_segments

where segment_name='STAT_SUBMIT_CENTER'and bytes/1024/1024>0.25order by3desc;

SEGMENT_NAME PARTITION_NAME

SEGMENT_NAME PARTITION_NAME ROUND(BYTES/1024/1024)

------------------------------------------------------------------------------

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200511011722

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200510211488

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200511111440

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200511211355

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200512211335

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200509111309

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200512111253

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200512011247

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200509211198

STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200601011151 STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200601111068 STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_200510011018 STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051011865 STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060121796

14rows 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_CENTER17234

查看表的记录数：

SQL>set timing on

SQL>select count(*)from STAT_SUBMIT_CENTER;

COUNT(*)

----------

170341007

Elapsed:00:14:18.60

查看这个表上的索引情况如下：

table STAT_SUBMIT_CENTER17234M

index IDX_SUBCEN_ADDRUSER5155M ADDRUSER

PK_STAT_SUBMIT_CENTER10653M 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 integer209715200

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

1TEMP3563061248434944AVAILABLE1YES429496729652428864003562012672 434816

2.需要考虑的几个方面

1）创建的索引需要几个G的磁盘空间。

2）创建索引需要排序，使用pga_aggregate_target，要把这个值从200M加大到2G。

3）如果内存不够，需要temp表空间，则要把temp表空间加大到8G——itpub上有一个帖子说过，15亿条记录用了34G空间。

4）在线创建，时间会比较长。讨论后，停止这个表的操作，非online创建。

3.实际操作过程

1）数据文件够，不扩展；temp数据文件扩展：

alter database tempfile'/bgdata/oracle/temp01.dbf'resize8192m;

2）在workarea_size_policy=AUTO的情况下，改pga_aggregate_target=2048m。对于串行操作，一个session能使用的pga=MIN(5%PGA_AGGREGATE_TARGET,100MB)，这样可以使得pga用到最大的值：

alter system set pga_aggregate_target=2048m;

3）因为这是一个比较长的过程，所以写脚本让后台运行：