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

转载 2015年07月10日 17:55:01
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
     from user_tables a, user_part_tables b, user_part_key_columns c
     where a.table_name='STAT_SUBMIT_CENTER'
     and b.table_name='STAT_SUBMIT_CENTER'
     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 46720 384640
SQL> select * from v$sort_usage;
USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH
------------------------------ ------------------------------ ---------------- ----------- ---------------- ----------
TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
DPC DPC 00000003974CFFB0 6134 0000000399CAB288 1254950678
TEMP TEMPORARY SORT 201 431113 365 46720 1
这个过程中抓到的sql为:
select file# from file$ where ts#=:1
4)v$sort_segment.USED_BLOCKS变为0,v$sort_usage.BLOCKS变为0。
5)重复3,4两步,估计这个是创建一个分区的索引。
需要解释一下的是,上面的sql只是我随机抓到的运行时间比较长的,整个create index过程会复杂很多,具体怎么样可以用sqltrace跟踪。这里主要看的是temp表空间的使用情况。
同时,在创建的过程中:
SQL> select segment_name,partition_name from user_segments where segment_name='IDX_SUBMIT_RECORDTIME';
no rows selected
SQL> select index_name,partition_name from user_ind_partitions where INDEX_NAME='IDX_SUBMIT_RECORDTIME';
no rows selected
当时忘了查user_segments中其实是有一个segment_name为一串数字的记录,那个才是正在创建的索引;如果这个事务失败了,将回滚。
最后耗时99分钟完成。
5. 创建完成后分析索引
但是接下来还有一件事。创建完成后要分析索引,否则就是走了索引,查询速度也会很慢。
SQL> explain plan for select count(*) from stat_submit_center where recordtime>trunc(sysdate);
Explained.
SQL> @?/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | |
| 2 | PARTITION RANGE ALL | | | | | 1 | 50 |
|* 3 | INDEX FAST FULL SCAN| IDX_SUBMIT_RECORDTIME | 8878K| 76M| 4 | 1 | 50 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("STAT_SUBMIT_CENTER"."RECORDTIME">TRUNC(SYSDATE@!))
Note: cpu costing is off
16 rows selected.
SQL> set autotrace on explain
SQL> set timing on
SQL> select count(*) from stat_submit_center where recordtime>trunc(sysdate);
aa^Cselect count(*) from stat_submit_center where recordtime>trunc(sysdate)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:11:49.85
SQL>
SQL> set autotrace off
上面可以看到,因为没有分析索引,虽然它走的是新建的IDX_SUBMIT_RECORDTIME索引,但是查询速度很慢,10分钟后也没有结果。下面我们分析一下:
SQL> Analyze index IDX_SUBMIT_RECORDTIME estimate statistics;
Index analyzed.
Elapsed: 00:00:06.84
SQL> set autotrace on explain
SQL> select count(*) from stat_submit_center where recordtime>trunc(sysdate);
COUNT(*)
----------
926736
Elapsed: 00:00:05.37
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4360 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 INDEX (RANGE SCAN) OF 'IDX_SUBMIT_RECORDTIME' (NON-UNI
QUE) (Cost=4360 Card=8878740 Bytes=79908660)
SQL> set autotrace off
索引分析之后,查询时间为5分钟左右,效率大大提高。
至此,完成全部操作。

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

1.前阶段做了个项目,需要将生产系统中的一个数据,导出到备份机中,这个表近40个字段,14亿条的数据。 数据抽取用的是开源的ETL工具,比较了一下,还是先导出txt文件,在导入到备份机中,速度最...
  • PAPALIAN
  • PAPALIAN
  • 2013年07月23日 17:28
  • 802

如何在亿级记录表中创建索引(转)

备注备注备注备注备注备注备注备注备注备注备注备注备注备注备注备注备注备注备注备注备注备注1...
  • henrybai
  • henrybai
  • 2011年02月23日 17:13
  • 465

Oracle创建索引要做到三个适当

在Oracle数据库中,创建索引虽然比较简单。但是要合理的创建索引则比较困难了。笔者认为,在创建索引时要做到三个适当,即在适当的表上、适当的列上创建适当数量的索引。虽然这可以通过一句话来概括优化的索引...
  • yang_best
  • yang_best
  • 2014年10月24日 10:22
  • 2174

ORACLE库中 大数据量的表 中索引建立的注意事项

最近在工作中遇到这样的事情,从 表A(几万条)、表B(几百万条)数据表中进行交叉查询,因为建立的索引不当,大大影响了查询的速度,在此根据自已遇到的情况总结出一些经验,在此与大家分享:1、在遇到这种数据...
  • aiunong
  • aiunong
  • 2006年03月01日 12:16
  • 3069

网络爬虫中高效率的URL索引 更新支持千万条数据

网络爬虫中高效率的URL索引 http://blog.csdn.net/chinafe/article/details/7816878中使用数组方式 保存,但数组是有限制的,这里改进一下方式,使用向量...
  • chinafe
  • chinafe
  • 2012年08月16日 17:47
  • 1830

hibernate添加索引

在一对多的关系中,在多的一方会产生一个外键,这个外键没有自动添加索引,当存在从一的一端产生对多的一端的查询时,有可能会在多的一端造成全表查询问题,数据量巨大时会产生严重的性能问题。可以在多一端的外键上...
  • fyqcdbdx
  • fyqcdbdx
  • 2011年10月28日 09:56
  • 3337

十亿级同步,百亿级调用,千亿级访问量如何炼成?

十亿级同步,百亿级调用,千亿级访问量如何炼成?
  • rise51
  • rise51
  • 2017年05月10日 11:43
  • 890

oracle创建索引---如何创建所以

今天在update一个字段时候,因为数据量比较大,所以慢的像蜗牛一样 然后就在相应的字段上面加了一个索引:  create index indexName on tableName(conlu...
  • datouniao1
  • datouniao1
  • 2017年08月11日 17:43
  • 404

mysql 存储及查询亿级数据

第一阶段: 1,一定要正确设计索引 2,一定要避免SQL语句全表扫描,所以SQL一定要走索引(如:一切的 > < != 等等之类的写法都会导致全表扫描) 3,一定要避免 limit 10000000,...
  • czw698
  • czw698
  • 2014年12月31日 13:54
  • 11100

Oracle复合类型:嵌套表、可变数组与索引表

一、 在pl/sql中使用嵌套表和可变数组1、在pl/sql中使用嵌套表在PL/SQL中使用嵌套表,此时嵌套表就相当于其他语言中的数组。①、示例:set SERVEROUTPUT ON DECLARE...
  • yu766588220
  • yu766588220
  • 2017年02月24日 11:04
  • 220
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:[Oracle]如何在亿级记录表中创建索引
举报原因:
原因补充:

(最多只允许输入30个字)