当前位置:操作系统 > Unix/Linux >>

[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

   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

  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" resize 8192m;

  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)因为这是一个比较长的过程,所以写脚本让后台运行:

  nohup time createind.sh &

  vi createind.sh

  #!/bin/sh

  sqlplus user/password <
  create index IDX_SUBMIT_RECORDTIME on STAT_SUBMIT_CENTER(RECORDTIME) local;

  exit

  EOF

  4)创建过程中可以观察v$sort_segment,v$sort_usage看排序情况:

  nohup time createind.sh &

  vi createind.sh

  #!/bin/sh

  sqlplus user/password <
  create index IDX_SUBMIT_RECORDTIME on STAT_SUBMIT_CENTER(RECORDTIME) local;

  exit

  EOF

  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 431360

  SQL> select * from v$sort_usage;

  no rows selected

  2)创建之初,抓到这么一条sql:

  insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,st

  ime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spar

  e1,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
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,