1.索引一般可以优化查询,但不是越多索引越好,还要考虑数据更新的问题。另外要注意“有效索引”的使用
2.可以考虑根据数据的层次和使用频率组织数据库。
比如人员数据的标不要建得过大,可能有很多字段很少使用到,这样可以将它们记录在另一个表中,通过外键进行关联,而频繁使用的数据放在一个较小的标中,这样就提高了效率,只是在数据编辑的时候要注意一致性处理。
另外可以建立各种历史信息表,对简历、职务等变动较多的数据可以将部分内容放到历史表中,以减轻当前表的负担
这个问题都是说起来容易作起来难的.
优化无非是两部分:
一个是数据库数据组织上的优化,
比如:不同用途的数据放置在不同的表空间,还有就是对容量很大,并却增长快的表分区.还有表的统计数字.
另一个是你的查询程序的优化,相应的书很多,相关的帖子也是很多的.
ORACLE的一些优化技术很值得一提的是基于cost的优化,其智能性很高.能根据相关查询所用到的cpu和io数据来自行选择最佳的执行计划.
还有就是分区技术.
1、 首先当然是建立数据库时的一些参数能设置好!
2、表空间规划。
3、建立索引
4、视图
5、SQL语句中尽量将检索条件中有索引的放在前面
了解Sybase IQ的人都知道,它的一个重要突破是采用了按列存储技术。其主要目的是为在海量数据查询中,提高数据检索效率。例如:在一个有20个列的customer表中,语句:select id,name,age from customer 在普通的数据库会将20个列的数据都读出来,再进行运算。而IQ中,只需选出用户关系的几个列,即列id,name,age就可以了,从而大大提高检索效率。在oracle数据库种目前,还没有采用类似的存储技术,不过我们可以借鉴这种思想,将customer进行纵向分片,即将高频使用的字段组织在一个表中,而将另外的字段组织在其他表中,两个表使用同一个关键字。同时,辅助横向分片技术,即将数据按月份、地区等分类组织在不同的表中,也可时增加检索的准确率,从而提高效率。
笔者在实际工作中,有幸接触到海量的数据处理问题,对其进行处理是一项艰巨而复杂的任务。原因有以下几个方面:
一、数据量过大,数据中什么情况都可能存在。如果说有10条数据,那么大不了每条去逐一检查,人为处理,如果有上百条数据,也可以考虑,如果数据上到千万级别,甚至过亿,那不是手工能解决的了,必须通过工具或者程序进行处理,尤其海量的数据中,什么情况都可能存在,例如,数据中某处格式出了问题,尤其在程序处理时,前面还能正常处理,突然到了某个地方问题出现了,程序终止了。
二、软硬件要求高,系统资源占用率高。对海量的数据进行处理,除了好的方法,最重要的就是合理使用工具,合理分配系统资源。一般情况,如果处理的数据过TB级,小型机是要考虑的,普通的机子如果有好的方法可以考虑,不过也必须加大CPU和内存,就象面对着千军万马,光有勇气没有一兵一卒是很难取胜的。
三、要求很高的处理方法和技巧。这也是本文的写作目的所在,好的处理方法是一位工程师长期工作经验的积累,也是个人的经验的总结。没有通用的处理方法,但有通用的原理和规则。
那么处理海量数据有哪些经验和技巧呢,我把我所知道的罗列一下,以供大家参考:
一、选用优秀的数据库工具
现在的数据库工具厂家比较多,对海量数据的处理对所使用的数据库工具要求比较高,一般使用Oracle或者DB2,微软公司最近发布的SQL Server 2005性能也不错。另外在BI领域:数据库,数据仓库,多维数据库,数据挖掘等相关工具也要进行选择,象好的ETL工具和好的OLAP工具都十分必要,例如Informatic,Eassbase等。笔者在实际数据分析项目中,对每天6000万条的日志数据进行处理,使用SQL Server 2000需要花费6小时,而使用SQL Server 2005则只需要花费3小时。
二、编写优良的程序代码
处理数据离不开优秀的程序代码,尤其在进行复杂数据处理时,必须使用程序。好的程序代码对数据的处理至关重要,这不仅仅是数据处理准确度的问题,更是数据处理效率的问题。良好的程序代码应该包含好的算法,包含好的处理流程,包含好的效率,包含好的异常处理机制等。
三、对海量数据进行分区操作
对海量数据进行分区操作十分必要,例如针对按年份存取的数据,我们可以按年进行分区,不同的数据库有不同的分区方式,不过处理机制大体相同。例如SQL Server的数据库分区是将不同的数据存于不同的文件组下,而不同的文件组存于不同的磁盘分区下,这样将数据分散开,减小磁盘I/O,减小了系统负荷,而且还可以将日志,索引等放于不同的分区下。
四、建立广泛的索引
对海量的数据处理,对大表建立索引是必行的,建立索引要考虑到具体情况,例如针对大表的分组、排序等字段,都要建立相应索引,一般还可以建立复合索引,对经常插入的表则建立索引时要小心,笔者在处理数据时,曾经在一个ETL流程中,当插入表时,首先删除索引,然后插入完毕,建立索引,并实施聚合操作,聚合完成后,再次插入前还是删除索引,所以索引要用到好的时机,索引的填充因子和聚集、非聚集索引都要考虑。
五、建立缓存机制
当数据量增加时,一般的处理工具都要考虑到缓存问题。缓存大小设置的好差也关系到数据处理的成败,例如,笔者在处理2亿条数据聚合操作时,缓存设置为100000条/Buffer,这对于这个级别的数据量是可行的。
六、加大虚拟内存
如果系统资源有限,内存提示不足,则可以靠增加虚拟内存来解决。笔者在实际项目中曾经遇到针对18亿条的数据进行处理,内存为1GB,1个P4 2.4G的CPU,对这么大的数据量进行聚合操作是有问题的,提示内存不足,那么采用了加大虚拟内存的方法来解决,在6块磁盘分区上分别建立了6个4096M的磁盘分区,用于虚拟内存,这样虚拟的内存则增加为 4096*6 + 1024 = 25600 M,解决了数据处理中的内存不足问题。
七、分批处理
海量数据处理难因为数据量大,那么解决海量数据处理难的问题其中一个技巧是减少数据量。可以对海量数据分批处理,然后处理后的数据再进行合并操作,这样逐个击破,有利于小数据量的处理,不至于面对大数据量带来的问题,不过这种方法也要因时因势进行,如果不允许拆分数据,还需要另想办法。不过一般的数据按天、按月、按年等存储的,都可以采用先分后合的方法,对数据进行分开处理。
八、使用临时表和中间表
数据量增加时,处理中要考虑提前汇总。这样做的目的是化整为零,大表变小表,分块处理完成后,再利用一定的规则进行合并,处理过程中的临时表的使用和中间结果的保存都非常重要,如果对于超海量的数据,大表处理不了,只能拆分为多个小表。如果处理过程中需要多步汇总操作,可按汇总步骤一步步来,不要一条语句完成,一口气吃掉一个胖子。
九、优化查询SQL语句
在对海量数据进行查询处理过程中,查询的SQL语句的性能对查询效率的影响是非常大的,编写高效优良的SQL脚本和存储过程是数据库工作人员的职责,也是检验数据库工作人员水平的一个标准,在对SQL语句的编写过程中,例如减少关联,少用或不用游标,设计好高效的数据库表结构等都十分必要。笔者在工作中试着对1亿行的数据使用游标,运行3个小时没有出结果,这是一定要改用程序处理了。
十、使用文本格式进行处理
对一般的数据处理可以使用数据库,如果对复杂的数据处理,必须借助程序,那么在程序操作数据库和程序操作文本之间选择,是一定要选择程序操作文本的,原因为:程序操作文本速度快;对文本进行处理不容易出错;文本的存储不受限制等。例如一般的海量的网络日志都是文本格式或者csv格式(文本格式),对它进行处理牵扯到数据清洗,是要利用程序进行处理的,而不建议导入数据库再做清洗。
十一、 定制强大的清洗规则和出错处理机制
海量数据中存在着不一致性,极有可能出现某处的瑕疵。例如,同样的数据中的时间字段,有的可能为非标准的时间,出现的原因可能为应用程序的错误,系统的错误等,这是在进行数据处理时,必须制定强大的数据清洗规则和出错处理机制。
十二、 建立视图或者物化视图
视图中的数据来源于基表,对海量数据的处理,可以将数据按一定的规则分散到各个基表中,查询或处理过程中可以基于视图进行,这样分散了磁盘I/O,正如10根绳子吊着一根柱子和一根吊着一根柱子的区别。
十三、 避免使用32位机子(极端情况)
目前的计算机很多都是32位的,那么编写的程序对内存的需要便受限制,而很多的海量数据处理是必须大量消耗内存的,这便要求更好性能的机子,其中对位数的限制也十分重要。
十四、 考虑操作系统问题
海量数据处理过程中,除了对数据库,处理程序等要求比较高以外,对操作系统的要求也放到了重要的位置,一般是必须使用服务器的,而且对系统的安全性和稳定性等要求也比较高。尤其对操作系统自身的缓存机制,临时空间的处理等问题都需要综合考虑。
十五、 使用数据仓库和多维数据库存储
数据量加大是一定要考虑OLAP的,传统的报表可能5、6个小时出来结果,而基于Cube的查询可能只需要几分钟,因此处理海量数据的利器是OLAP多维分析,即建立数据仓库,建立多维数据集,基于多维数据集进行报表展现和数据挖掘等。
十六、 使用采样数据,进行数据挖掘
基于海量数据的数据挖掘正在逐步兴起,面对着超海量的数据,一般的挖掘软件或算法往往采用数据抽样的方式进行处理,这样的误差不会很高,大大提高了处理效率和处理的成功率。一般采样时要注意数据的完整性和,防止过大的偏差。笔者曾经对1亿2千万行的表数据进行采样,抽取出400万行,经测试软件测试处理的误差为千分之五,客户可以接受。
还有一些方法,需要在不同的情况和场合下运用,例如使用代理键等操作,这样的好处是加快了聚合时间,因为对数值型的聚合比对字符型的聚合快得多。类似的情况需要针对不同的需求进行处理。
海量数据是发展趋势,对数据分析和挖掘也越来越重要,从海量数据中提取有用信息重要而紧迫,这便要求处理要准确,精度要高,而且处理时间要短,得到有价值信息要快,所以,对海量数据的研究很有前途,也很值得进行广泛深入的研究。
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/DaiZiLiang/archive/2006/12/06/1432193.aspx
Oracle中海量数据不用愁,向难处say no
假设你要把Oracle里的大量数据(80MB以上)转移到另外的用户,另外的表空间里。能够用下面引见的高速转移数据的办法。
一、建新表的方式
create table target_tablename tablespace
target_tablespace_name nologging
pctfree 10 pctused 60
storage(initial 5M next 5M minextents 1
maxextents unlimited pctincrease 0)
as select * from username.source_tablename where 条件;
留意事项: 新建的表没有原来表的索引和默许值, 只需非空(not null)的约束素条件能够承袭过去,其它的约束条件或索引须要重新树立.
二、直接插入的办法
INSERT /* APPEND */ IN和空姐在一同的日子分集引见TO ta和空姐在一同的日子分集引见rget_tablename
SELECT * FROM username.source_tablename where 条件;
COMMIT;
留意事项:
用INSERT /* APPEND */ 的办法会对target_tablename发生级别为6的独占锁,假设运转此命令时髦有对target_tablename的DML操作会排队在它后面,对OLTP系统在用的表操作是不适宜的。
标明:这两种办法转移数据时没有用SGA里数据缓冲区和事物处置的回滚段, 也不写联机事物日志,就象数据库装载工具Solload一样直接把数据写到物理文件,速度是很快的。在Oracle8i现在的版本都能够运用。
停机没问题,要的是速度。20T的数据量。
下面的三种方案
1。exp/imp
2。通过dblink create table as select * from tablename@dblink;
3。表空间传输
这三种方法哪种更快一点?
如果用方案2,undo恐怕受不了吧。如果用游标循环,1万一提交。速度是不是又得降下来?
目前先不考虑dd考数据文件,然后再升级的方案。
已经采用:nologging
parallel
partition table
raw device(裸设备,也叫裸分区(原始分区),是一种没有经过格式化,不被Unix通过文件系统来读取的特殊字符设备。它由应用程序负责对它进行读写操作。不经过文件系统的缓冲。)
ORACLE传输表空间的总结
有时我们需要把比较大的数据进行跨平台(10G支持跨平台)的迁移,使用EXP/IMP等方法很慢,可能通过传输表空间快速安全的实现。此操作需要在SYSDBA的权限下进行,具体步骤如下:
1.检查所要迁移的表空间是否自包含 就是检测是否符合传输表空间的基本条件)
exec sys.dbms_tts.transport_set_check('tablespace_name',true);
select * from sys.transport_set_violations;
如果无记录返回,则说明符合传输表空间的条件,如果有记录返回则不符合
2.设置所要传输表空间为只读:
alter tablespace tablespace_name read only;
3.使用exp工具导出所要传输表空间的元数据(metadata)
exp userid=\'sys/lclsys2008 as sysdba\' file=/opt/test.dmp log=/opt/test.log transport_tablespace=y tablespaces=tablespace_name
注意:这里使用SYSDBA时需要转义字符,在LINUX下用\',WINDOWS下使用单引号就可以
4.使用RMAN转换所要传输表空间的数据文件头为目标系统文件
登陆RMAN: connect target /
rman>convert tablespace "TABLESPACE_NAME" to platform. 'Linux IA (32-bit)' format 'D:\xxx.dbf'
注意:TABLESPACE_NAME为传输表空间的名称,需要使用双引号且大写,Linux IA (32-bit)为目标平台的名称,可以在目标平台数据库中通过select platform_name form. v$database来查询。
5.复制表空间转换后的数据文件及导出文件到目标平台
6.使用IMP工具加载数据库文件到目标平台:
imp userid=\'sys/ad as sysdba\' file=expdat.dmp transport_tablespace=y datafiles=('D:\xx.dbf') tablespaces=tablespace_name
注意在使用IMP和EXP时尽量使用相同的版本,以避免操作失败。
水平有限,有什么问题大家一起讨论一起学习,谢谢!
补充一点,9*55555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555,在piner的书提到。
就是seq,function,proc,view等元数据并没有迁移过来,要在执行一次迁移。
就是执行一次exp ... rows=n
在imp导入才行。
另外再补充一点,迁移表空间之前必需先建立相对应的用户,要不然会迁移不成功的
oracle提出可传输表空间的本意,是为了解决数据仓库中海量数据从各个业务系统到数据仓库的迁移,
可以使用传输表空间技术来实现,转贴一个例子给你:
下面是一个简单的小例子:
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK('TEST', TRUE);
PL/SQL 过程已成功完成。
SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
----------------------------------------------------------------
Index YANGTK.IND_T_NAME in tablespace TEST points to table YANGTK.T in tablespace YANGTK
SQL> ALTER INDEX IND_T_NAME REBUILD TABLESPACE YANGTK;
索引已更改。
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK('TEST', TRUE);
PL/SQL 过程已成功完成。
SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
未选定行
SQL> ALTER TABLESPACE TEST READ ONLY;
表空间已更改。
E:>exp """/@test as sysdba""" file=trans.dmp transport_tablespace=y tablespaces=test triggers=n constraints=y grants=y
Export: Release 9.2.0.1.0 - Production on 星期四 1月 13 16:47:24 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不会导出表数据(行)
关于导出可传输的表空间元数据...
用于表空间 TEST...
. 正在导出群集定义
. 正在导出表定义
. . 正在导出表 TEST
. 正在导出引用完整性约束条件
. 结束导出可传输的表空间元数据
在没有警告的情况下成功终止导出。
SQL> ALTER TABLESPACE TEST READ WRITE;
表空间已更改。
E:>copy e:oracleoradatatesttest.dbf e:oracleoradatayangtktest.dbf
已复制 1 个文件。
E:>imp """/@yangtk as sysdba""" file=trans.dmp transport_tablespace=y datafiles='e:oracleoradatayangtktest.dbf' tablespaces=test tts_owners=yangtk
Import: Release 9.2.0.1.0 - Production on 星期四 1月 13 16:53:26 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
经由常规路径导出由EXPORT:V09.02.00创建的文件
关于导入可传输表空间元数据...
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将SYS的对象导入到 SYS
. 正在将YANGTK的对象导入到 YANGTK
. . 正在导入表 "TEST"
成功终止导入,但出现警告。
SQL> CONN YANGTK/YANGTK@YANGTK
已连接。
SQL> ALTER TABLESPACE TEST READ WRITE;
表空间已更改。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12122734/viewspace-675172/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12122734/viewspace-675172/