缩略显示
SQLServer,Oracle,DB2数据库SQL语句比较
关键字:sqlserveroracledb2sql1.1.1取前n条记录
SQLServer：
Selecttopn*fromxtable
Oracle：
Select*fromxtablewhererownum<=n
DB2：1.1.2取当前日期
Select*fromxtablefetchfirstnrowsonly
SQLServer：
Selectgetdate()
Oracle：
Selectsysdatefromdual
DB2：1.1.3连接字符串
Selectcurrent
timestampfromsysibm.sysdummy1
SQLServer：
SelectHello+Toone
Oracle：
Select
Hello||Toonefromdual
DB2：
Select
Hello||Toonefromsysimb.sysdummy1
1.1.4空值转换
SQLServer：
SELECT
userid,username,isnull(email,0)FROMAUTH_USER
Oracle：
SELECT
userid,username,nvl(email,0)FROMAUTH_USER
DB2：1.1.5类型转换
SELECT
userid,username,value(email,0)FROMAUTH_USER
SQLServer：
SELECTconvert(varchar,getdate(),20)
Oracle：
SELECT
to_char(sysdate,yyyy-mm-ddhh24:mi:ss)fromdual
DB2：
SELECT
varchar(currenttimestamp)fromsysibm.sysdummy1
注1：Server中转换日期格式改变Style参数：20；
■SQL
■Oracle中转换日期格式改变格式化参数：yyyy-mm-ddhh24:mi:ss‘yyyy’、’mm’、’dd’、’hh12’、’hh24’、’mi’、’ss’等；
■Db2中转换日期格式改变系统常量：currenttimestamp‘currentdate’、’current注2：■SQLServer数据类型改变“数据类型参数”：int、varchar等；■Oracle数据类型改变函数：to_char()、to_date()、to_number()等；■DB2数据类型改变函数：varchar()、int()、date()、time()等；1.1.6取值判断
time’等；
SQLServer：
select
caseConvert=casewheng.master_type=systemelse普通用户
then管理员
when
g.master_type=roletypethen特殊角色
endfromglobal_codegthen管理员whenroletypethen特殊角
Oracle：色
select
caseg.master_type
whensystem
else普通用户end
AScaseConvertfromglobal_codegthen管理员whenroletypethen特殊角色
DB2：
selectend
caseg.master_type
whensystem
else普通用户1.1.7位置SQL
AScaseConvertFromglobal_codeg
Server：
selectcharindex(E,ABCDEF)selectpatindex(%E%,ABCDEF)
Oracle：
selectinstr(ABCDEF,E)fromdual
DB2：
Selectlocate(E,ABCDEF)from
sysibm.sysdummy11.1.8其他函数长度
SQLServer
Oracle
DB2
Len()
Length()
Length()
取子串Substring()Substr()
Substr()
1.2附
1.2.1DB2V8.1常用命令创建数据库在服务器上执行删除数据库在服务器上执行
■■■
db2CREATEDBoatemp
db2DROPDBoatemp
创建表空间db2CREATEUSERTEMPORARYTABLESPACEUSERSPACE1MANAGEDBY
SYSTEMUSING(USERSPACE1)启动数据库切换用户关闭数据库切换用户创建远程管理节点进入启动数据库：关闭数据库
■■■
su–db2inst1
db2start
su–db2inst1DB2命令窗口
db2stop[force]
db2CATALOGTCPIPNODEasnode
REMOTE10.1.22.176SERVER50000连接数据库
db2CATALOGDBoadb2ASoadb2ATNODEasnode
■
db2CONNECT
TOoadb2USERdb2inst1USINGibmdb2
■■■
关闭数据库连接执行脚本
db2terminate
db2-td!-vfioa2.db2-zinfo.log
导出脚本
db2look-doadb2-idb2inst1-wibmdb2-e-oputsql.db2-tauth_userdb2look-d
oadb2-idb2inst1-wibmdb2-e-oputsq.db2查看正在使用的端口netstat-a查询系统表
■■
selectcount(*)fromSYSCAT.TABLESwhereselectcount(*)fromSYSCAT.PROCEDURESwhere
TABSCHEMA=’DB2INST1’PROCSCHEMA=’DB2INST1’
■
常用命令DB2SELECTDROPTALBE||TABNAMEFROMSYSCAT.TABLESWHERETDB2SELECTDROPVIEW||TABNAMEDB2
ABSCHEMA=DB2INST1ANDTYPE=T>db2droptables.db2
FROMSYSCAT.TABLESWHERETABSCHEMA=DB2INST1ANDTYPE=V>db2dropviews.db2
–vfdb2droptables.db2–zinfo.logdb2SELECTusernameFROMauth_userFETCHFIRST3ROWSONLY启动JDBC
■
db2jstrtJDBCAPPLETSERVER6789
1.1
数据库移植注意事项
1.1.1取前n条记录
SQLServer：Selecttopn*fromxtable
Oracle：Select*fromxtablewhererownum<=n
DB2：Select*fromxtablefetchfirstnrowsonly1.1.2取当前日期
SQLServer：Selectgetdate()
Oracle：Selectsysdatefromdual
DB2：Selectcurrenttimestampfromsysibm.sysdummy11.1.3连接字符串
SQLServer：Select'Hello'+'Toone'
Oracle：Select'Hello'||'Toone'fromdual
DB2：Select'Hello'||'Toone'fromsysimb.sysdummy11.1.4空值转换
SQLServer：SELECTuserid,username,isnull(email,'0')FROMAUTH_USER
Oracle：SELECTuserid,username,nvl(email,'0')FROMAUTH_USER
DB2：SELECTuserid,username,value(email,'0')FROMAUTH_USER1.1.5类型转换
SQLServer：SELECTconvert(varchar,getdate(),20)
Oracle：SELECTto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual
DB2：SELECTvarchar(currenttimestamp)fromsysibm.sysdummy1注1：
■SQLServer中转换日期格式改变Style参数：20；■Oracle中转换日期格式改变格式化参数：yyyy-mm-ddhh24:mi:ssXML:namespaceprefix=ons="urn:schemas-microsoft-com:Office:office"/>
‘yyyy’、’mm’、’dd’、’hh12’、’hh24’、’mi’、’ss’等；■Db2中转换日期格式改变系统常量：currenttimestamp‘currentdate’、’currenttime’等；注2：■SQLServer数据类型改变“数据类型参数”：int、varchar等；■Oracle数据类型改变函数：to_char()、to_date()、to_number()等；■DB2数据类型改变函数：varchar()、int()、date()、time()等；1.1.6取值判断
SQLServer：
selectcaseConvert=
casewheng.master_type='system'
then'管理员'
wheng.master_type='roletype'then'特殊角色'
else'普通用户'
end
fromglobal_codeg
Oracle：
select
caseg.master_type
when'system'
then'管理员'
when'roletype'then'特殊角色'
else'普通用户'
end
AScaseConvert
fromglobal_codeg
DB2：
select
caseg.master_type
when'system'
then'管理员'
when'roletype'then'特殊角色'
else'普通用户'
end
AScaseConvert
Fromglobal_codeg1.1.7位置
SQLServer：
selectcharindex('E','ABCDEF')
selectpatindex('%E%','ABCDEF')
Oracle：
selectinstr('ABCDEF','E')fromdual
DB2：
Selectlocate('E','ABCDEF')fromsysibm.sysdummy11.1.8其他函数
SQLServer长度
Oracle
DB2
Len()
Length()
Length()
取子串Substring()
Substr()
Substr()
1.2
附
1.2.1DB2V8.1常用命令创建数据库在服务器上执行db2CREATEDBoatemp删除数据库在服务器上执行db2DROPDBoatemp创建表空间
■■■
db2"CREATEUSERTEMPORARYTABLESPACEUSERSPACE1MANAGEDBYSYSTEMUSING('USERSPACE1')"启动数据库切换用户su–db2inst1启动数据库：db2start关闭数据库切换用户su–db2inst1关闭数据库db2stop[force]创建远程管理节点进入DB2命令窗口
■
■■
db2CATALOGTCPIPNODEasnodeREMOTE10.1.22.176SERVER50000
db2CATALOGDBoadb2ASoadb2ATNODEasnode连接数据库
■
db2CONNECTTOoadb2USERdb2inst1USINGibmdb2关闭数据库连接
■
db2terminate执行脚本
■
db2-td!-vfioa2.db2-zinfo.log导出脚本
■
db2look-doadb2-idb2inst1-wibmdb2-e-oputsql.db2-tauth_user
db2look-doadb2-idb2inst1-wibmdb2-e-oputsq.db2查看正在使用的端口
■
netstat-a查询系统表
■
selectcount(*)fromSYSCAT.TABLESwhereTABSCHEMA=’DB2INST1’SYSCAT.PROCEDURESwherePROCSCHEMA=’DB2INST1’
selectcount(*)from
????
缩略显示
13:14浏览(484)评论(0)分类:数据库2009-02-23
异常是否会引起oracle事物回滚
关键字:oracle原理上说，异常是不会终止事务的。但是如果异常传递到了调用环境中，由于数据库的原子性，服务器会对事物进行回滚，所以就会让人有异常终止事务的假象。
sample1:BEGINinsertintotest_tablevalues(1,11);Declareinumber:=1;Cu_exException;Begin
insertintotest_tablevalues(2,22);If(i=1)thenRaiseCu_ex;Endif;DBMS_OUTPUT.put_line('TEST');commit;ExceptionWhenCu_exthenRaise_application_error(-20000,'hasancustomererror'||sqlerrm);WhenothersthenRaise_application_error(-20001,'hasanerror'||sqlerrm);End;END;/
在这个例子中，两条insert都会被回滚。
sample2:BEGINinsertintotest_tablevalues(1,11);Declareinumber:=1;Cu_exException;Begininsertintotest_tablevalues(2,22);If(i=1)thenRaiseCu_ex;Endif;DBMS_OUTPUT.put_line('TEST');commit;ExceptionWhenCu_exthenRaise_application_error(-20000,'hasancustomererror'||sqlerrm);WhenothersthenRaise_application_error(-20001,'hasanerror'||sqlerrm);End;EXCEPTIONWHENOTHERSTHENNULL;END;/在这个例子中，两条insert都不会回滚，因为异常被捕捉并处理了，并没有传递到调用环境中。
顾推荐在处理异常的时候，不要捕获whenothers，让它传递的客户端。
????
缩略显示
16:21浏览(391)评论(0)分类:数据库2009-02-23
oracle数据迁移imp/exp和物化视图的区别
关键字:oracle数据迁移使用ONPREBUILTMATERIALIZEDVIEW进行数据迁移有很多的优点：物化视图迁移方式是基于对象方式，这种方式是基于Oracle的，因此和数据库所在的平台无关，这使得这种方式可以用于跨平台迁移；Oracle对物化视图的后向兼容性使得物化视图可以在不同版本之间进行刷新，这也使得这种方式具有跨版本的特点。因此，这种迁移方式可以用作系统的升级；物化视图迁移的另一个优点是具有很短的停机时间。应该说，采用物化视图迁移总的消耗时间并没有缩短，但是物化视图的特点使得很多工作可以放在停机之前进行。这样耗费时间的物化视图建立、完全同步等工作都可以放到平常工作时间进行，一旦停机之后，只需要将物化视图执行一次快速刷新，同步上次刷新之后的修改就可以了。这样极大的缩短了迁移所需的时间。物化视图迁移还有一个重要的特点，迁移方式是逻辑性质的。这意味着迁移过程中可以只迁移需要的表；只迁移表中必要的列；只迁移表中部分记录；可以在迁移过程中修改表的逻辑结构和物理结构；也可以在迁移过程中进行数据的处理和转换。下面对比一下物化视图迁移方式和传统逻辑迁移工具EXP/IMP。物化视图方式和EXP都是跨平台的；物化视图方式和EXP都是跨版本的：其中EXP跨版本需要遵循低版本导出，目标版本导入的原则，而物化视图则没有这个限制。不过个别版本之间创建物化视图是存在bug的，这个需要进行额外的处理。上面这两点对比，似乎物化视图方式并没有多大的优势，不过下面的两个方面，物化视图方式的优势就很大了。物化视图迁移的所需停机时间远远小于EXP/IMP的时间。在利用EXP/IMP进行迁移的时候，很头痛的一个问题就是导入、导出的速度。即使采用10g的新功能数据泵EXPDP/IMPDP，速度仍然是导出、导入工具的一个比较致命的缺点。几百G的数据库采用EXP/IMP或EXPDP/IMPDP方式，停机时间很可能会超过1天，这对于很多系统都是无法接受的。而对于物化视图迁移而言，这就是最大的优势之一。首先要说的是，物化视图迁移方式并不能明显的缩短所需的总时间。但是由于物化视图可以进行快速刷新，因此，可以把完全刷新的步骤放在停机之前进行，这样只需要在停机的时候进行一次快速刷新就可以了，极大的缩短了大数据量数据库的迁移时间。
物化视图迁移和EXP都属于逻辑迁移：但是EXP方式很难对表的逻辑结构进行修改。如果提前创建表，并使用IGNORE=Y的方式，可以修改表的物理存储结果，如改变表空间，改变表的分区方式等；如果在EXP的时候指定QUERY参数，可以导出表中的部分记录，但是这种方式使用方式很有限，只能针对个别的表，否则就要求QUERY语句中的WHERE条件对导出所涉及的所有表都生效，对于大部分的情况，这是很难做到的。如果要改变逻辑结果，使用EXP就非常困难了，如果添加新的列还好一些，如果要去掉一些列，用EXP的方式基本上不太可能。而上面提到的所有的修改，对于物化视图方式来说都不是困难的事情，物化视图方式可以轻松的修改基表的结构，创建物化视图也可以指定行和列。当然，物化视图迁移方式也不是尽善尽美的，这种方式也存在很多的缺点，比如前面提到的跨版本的bug问题，如果刨除bug不谈，那么物化视图迁移方式至少会面临以下问题：首先，物化视图迁移是表和数据的迁移，而EXP则是数据库中所有对象的迁移，因此使用物化视图迁移方式，必须手工处理索引、约束、过程、触发器、对象、序列、同义词、视图、数据库链以及对象的授权。对于一个大型数据库环境，这些要手工处理这些工作，工作量可想而知。一个相对简单的处理方法是，物化视图与EXP/IMP配合使用，通过EXP导出所有的对象，只是不包含表中的记录，表中的记录通过物化视图的功能进行同步。不过这样还需要在物化视图同步之后，检查两个数据库中所有对象的是否一致，避免丢失源环境中EXP之后做出的修改。总而言之，使用物化视图迁移方式，迁移的复杂程度要远远超过使用EXP的方式。其次，物化视图迁移对表结构有一定的要求。首先，为了保证迁移后目标数据库中仍然是表，不会变成物化视图，必须采用ONPREBUILT方式建立物化视图。而这种方式建立的物化视图必须是基于主键的，这就要求基表必须包括主键。对于不包括主键的表，是没有办法采用物化视图方式进行迁移的。
????
缩略显示
13:31浏览(1170)评论(0)分类:数据库2009-02-22
DB2函数大全函数大全
关键字:db2函数名AVG()函数解释返回一组数值的平均值.函数举例SELECTAVG(SALARY)FROMBSEMPMS;返回一对数值的关系系数.
CORR(),CORRELATION()
SELECTCORRELATION(SALARY,BONUS)FROMBSEMPMS;COUNT()返回一组行或值的个数.SELECTCOUNT(*)FROMBSEMPMS;返回一对数值的协方差.
COVAR(),COVARIANCE()
SELECTCOVAR(SALARY,BONUS)FROMBSEMPMS;MAX()MIN()返回一组数值中的最大值.返回一组数值中的最小值.返回一组数据的和.返回一组数值的标准偏差.SELECTMAX(SALARY)FROMBSEMPMS;SELECTMIN(SALARY)FROMBSEMPMS;SELECTSTDDEV(SALARY)FROMBSEMPMS;
STDDEV()SUM()
SELECTSUM(SALARY)FROMBSEMPMS;SELECTVARIANCE(SALARY)FROMBSEMPMS;
VAR(),VARIANCE()ABS(),ABSVAL()
返回一组数值的方差.返回参数的绝对值.
SELECTABS(-3.4)FROMBSEMPMS;
ACOS()ASCII()ASIN()ATAN()
返回参数的反余弦值.
SELECTACOS(0.9)FROMBSEMPMS;SELECTASCII('R')FROMBSEMPMS;SELECTASIN(0.9)FROMBSEMPMS;
返回整数参数最左边的字符的ASCII码.返回用弧度表示的角度的参数的反正弦函数.
返回参数的反正切值,该参数用弧度表示的角度的参数.返回用弧度表示的角度的X和Y坐标的反正切值.返回整型常量中的数字或字符串的64位整数表示.返回比参数大或等于参数的最小的整数值.SELECTCEIL(4.67)FROMBSEMPMS;
SELECTATAN(0.9)FROMBSEMPMS;ATAN2()
SELECTATAN2(0.5,0.9)FROMBSEMPMS;BIGINT()
SELECTBIGINT(EMP_NO)FROMBSEMPMS;CEILING()ORCEIL()
SELECTCEILING(3.56)FROMBSEMPMS;CHAR()
返回日期时间型,字符串,整数,十进制或双精度浮点数的字符串表示.返回具有由参数指定的ASCII码的字符.返回两个字符串的连接.返回数值的年部分.
SELECTCHAR(SALARY,',')FROMBSEMPMS;CHR()SELECTCHAR(167)FROMBSEMPMS;
CONCAT()YEAR()
SELECTCONCAT(EMP_NO,EMP_NAM)FROMBSEMPMS;
SELECTYEAR('2003/01/02')FROMBSEMPMS;
VARCHAR()
返回字符串,日期型,图形串的可变长度的字符串表示.返回字符串的大写.
SELECTVARCHAR(EMP_NAM,50)FROMBSEMPMS;UCASE()ORUPPER()SELECTUCASE(EMP_NAM)FROMBSEMPMS;
SELECTUPPER(EMP_NO)FROMBSEMPMS;TRUNCATE()ORTRUNC()从表达式小数点右边的位置开始截断并返回该数值.
SELECTTRUNCATE(345.6789,2)FROMBSEMPMS;TIME()返回一个数值中的时间.SELECTTIME('2001-03-19.12.30.123456')FROMBSEMPMS;SELECT返回EXP1串自EXP2处开始的子串.
SUBSTR(EXP1,EXP2)
SUBSTR('CDNJFDJFJD',5)FROMBSEMPMS;BSEMPMS;SQRT()SPACE()返回该参数的平方根.
SELECTSUBSTR('CDNJFDJFJD',5,2)FROM
SELECTSQRT(36)FROMBSEMPMS;
返回由参数指定的长度,包含空格在内的字符串.返回一个数值的秒部分.删除字符串尾部的空格.
SELECTSPACE(10)FROMBSEMPMS;SECOND()RTRIM()SELECTSECOND('18:34:32')FROMBSEMPMS;SELECTRTRIM('COMMENT')FROMBSEMPMS;
ROUND(EXP1,EXP2)
返回EXP1小数点右边的第EXP2位置处开始的四舍五入值.用EXP3替代EXP1中所有的EXP2
SELECTROUND(2345.6789,2)FROMBSEMPMSREPLACE(EXP1,EXP2,EXP3)
SELECTCHAR(REPLACE('ROMANDD','NDD','CCB'),10)FROMBSEMPMS;REPEAT(EXP1,EXP2)返回EXP1重复EXP2次后的字符串.
SELECTCHAR(REPEAT('REPEAT',3),21)FROMBSEMPMS;REAL()RAND()返回一个数值的单精度浮点数表示.返回0和1之间的随机浮点数.SELECTREAL(10)FROMBSEMPMS;SELECTRAND()FROMBSEMPMS;SELECTPOWER(2,5)FROMBSEMPMS;
POWER(EXP1,EXP2)POSSTR(EXP1,EXP2)
返回EXP1的EXP2次幂.
返回EXP2在EXP1中的位置.如果EXP1=EXP2,则为NULL,否则为EXP1
SELECT('ABCDEFGH','D')FROMBSEMPMS;NULLIF(EXP1,EXP2)NODENUMBER()返回行的分区号.
SELECTNODENUMBER(EMP_NO)FROMBSEMPMS;
MONTH()
返回一个数值的月部分.返回一个数值的分钟部分.删除字符串前面的空格.返回一个数值的小时部分.
SELECTMONTH('2003/10/20')FROMBSEMPMS;SELECTMOD(20,8)FROMBSEMPMS;
MOD(EXP1,EXP2)MINUTE()LTRIM()HOUR()DOUBLE()
返回EXP1除以EXP2的余数.
SELECTMINUTE('18:34:23')FROMBSEMPMS;SELECTLTRIM('CDDD')FROMBSEMPMS;SELECTHOUR('18:34:23')FROMBSEMPMS;
如果参数是一个数字表达式,返回与其相对应的浮点数,如果参数是字符串表达式,则返回该SELECTDOUBLE('5678')FROMBSEMPMS;SELECTEXP(2)FROMBSEMPMS;SELECTFLOAT(789)FROMBSEMPMS;SLECTFLOOR(88.93)FROMBSEMPMS;SELECTHEX(16)FROMBSEMPMS;
数的字符串表达式.EXP()FLOAT()FLOOR()HEX()
返回参数的指数函数.
返回一个数的浮点表示.
返回小于或等于参数的最大整数.
返回一个表示为字符串的值的16进制表示.14:31浏览(290)评论(0)分类:数据库
????
缩略显示
2009-02-22
oraclesql转换成db2sql
关键字:db2oracle1、Oracel中的decodeDB2解决方案：用case条件表达式完成。case两种语法模式：
(1)CASEWHEN条件THEN结果1ELSE结果2
END(2)CASE表达式1WHEN表达式2THEN结果1ELSE结果2
END上面的WHEN可以重复多次，就像C中的SWITCH..CASE的表达.例如：
SELECTORDNO,CUSNO,
CASEMONTH(SHIPDATE)
WHEN''01''THEN''Jan''
WHEN''02''THEN''Feb''
WHEN''03''THEN''Mar''
WHEN''04''THEN''Apr''
WHEN''05''THEN''May''
WHEN''06''THEN''Jun''
WHEN''07''THEN''Jul''
WHEN''08''THEN''Aug''
WHEN''09''THEN''Sep''
WHEN''10''THEN''Oct''
WHEN''11''THEN''Nov''
WHEN''12''THEN''Dec''
END
FROMFILE应用实例：
OracleSQL：
-------------------------
selectdecode(t.organtypecode,''D'',t.parent,''S'',t.parent,t.id)
fromA_ORGANt
wheret.parent=35
DB2SQL：
-------------------------
selectcasex.organtypecode
when''D''then
x.parent
when''S''then
x.parent
else
x.id
end
froma_Organx
wherex.parent=35;2、Oracle中的Startwith...ConnectBy递归查询DB2解决方案：用with公共递归表达式来解决。DB2解决方案：用case条件表达式完成。
OracleSQL：
-------------------
selectt.id
froma_organt
startwitht.idin(selectdecode(t.organtypecode,
''D'',
t.parent,
''S'',
t.parent,
t.id)
fromA_ORGAN
wheret.id=35)
connectbyt.parent=priort.id
DB2SQL：
-------------------------
WITHFKK(id)as
(selecto.idfroma_organo
whereo.id=35
UNIONALL
selectcasex.organtypecode
when''D''thenx.parent
when''S''thenx.parent
elsex.id
end
fromFKKfk,a_organx
wherefk.id=x.parent)
selectdistinctidfromFKK;3、Oracle中的dual表对应DB2中的SYSIBM.SYSDUMMY1表DB2解决方案：对应于DB2中的SYSIBM.SYSDUMMY1表
OracleSQL：
-------------------------
select15astttfromdual结果：
ttt
-------
15
DB2SQL：
-------------------------
select15astttfromSYSIBM.SYSDUMMY1结果：
ttt
-------
15
4、日期转换问题DB2解决方案：有相应的函数
OracleSQL：
-------------------------
selectm.*
fromdj_mcdjm
wherem.mcqc||''''like''%$P%''
andm.xzqhdm||''''like''%$P%''
andm.hylbdm||''''like''%$P%''
andm.blqsrq>=to_date(''$P'',''yyyy-mm-dd'')
andm.blqsrq
DB2SQL：
---------------------
--------------------
--名称：名称库查询
--作者：雷智民
--日期：2006-10-27--FOR：DB2
--------------------
selectm.*
fromdj_mcdjm
wherem.mcqc||''''like''%%''
andm.xzqhdm||''''like''%%%''
andm.hylbdm||''''like''%%%''
anddate(m.blqsrq)>=date(''1900-01-01'')
anddate(m.blqsrq) 6、左右外连接问题db2的左右外连接的语法和标准sql语法一样，只是没有oracle中的(+)这个简单符号来标记左右外连接，left(right)outerjoinon1）.内连接INNERJOIN的Oracle和DB2的写法Oracle可以这样实现?Selecta.*frombsempmsa,bsdptmsbwherea.dpt_no=b.dpt_no;DB2可以这样实现?Select*fromdb2admin.bsempmsinnerjoindb2admin.bsdptmsondb2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;2）.外连接的Oracle和DB2的写法(右外连接,左外连接,完全外连接,组合外连接)Oracle可以这样实现?Selecta.*frombsempmsa,bsdptmsbwherea.dpt_no=b.dpt_no(+);Selecta.*frombsempmsa,bsdptmsbwherea.dpt_no(+)=b.dpt_no;DB2可以这样实现?Select*fromdb2admin.bsempmsrightouterjoindb2admin.bsdptmsondb2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;
Select*fromdb2admin.bsempmsleftouterjoindb2admin.bsdptmsondb2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;
Select*fromdb2admin.bsempmsfullouterjoindb2admin.bsdptmsondb2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;7、LIKE问题db2中谓词LIKE后边的表达式不支持字段。只支持一下类型：AconstantAspecialregisterAhostvariableAscalarfunctionwhoseoperandsareanyoftheaboveAnexpressionconcatenatinganyoftheabove（附DB2文档：使用格式：match-expressionLIKEpattern-expressionmatch-expressionAnexpressionthatspecifiesthestringthatistobeexaminedtoseeifitconformstoacertainpatternofcharacters.Theexpressioncanbespecifiedby:
AconstantAspecialregisterAhostvariable(includingalocatorvariableorafilereferencevariable)
AscalarfunctionAlargeobjectlocatorAcolumnnameAnexpressionconcatenatinganyoftheabove
pattern-expressionAnexpressionthatspecifiesthestringthatistobematched.Theexpressioncanbespecifiedby:
AconstantAspecialregisterAhostvariableAscalarfunctionwhoseoperandsareanyoftheaboveAnexpressionconcatenatinganyoftheabovewiththefollowingrestrictions:
NoelementintheexpressioncanbeoftypeLONGVARCHAR,CLOB,LONGVARGRAPHIC,orDBCLOB.InadditionitcannotbeaBLOBfilereferencevariable.Theactuallengthofpattern-expressioncannotbemorethan32672bytes.）DB2中几个隔离级别select..forupdatewith**的行锁看了很多介绍DB2中隔离级别和锁的各种用法和机制，动手做了一个比较详尽的试验，对于有些结果我还真没想明白。。
在
db29中我做了以下的试验，
CreatetableRRTest(pkIDVARCHAR(20)NOTNULL,unID1varchar(20)NotNULL,UnID2varchar(20),"CUSTOMER_ID"VARCHAR(6),"ORDER_TYPE"DECIMAL(2,0),"EXECUTION_TYPE"DECIMAL(2,0),"ORDER_DATE"VARCHAR(8),"ORDER_TIME"VARCHAR(6),"ORDER_DATETIME"TIMESTAMP,"SIDE"DECIMAL(1,0),"TRADE_TYPE"DECIMAL(1,0),"ORDER_AMOUNT"DECIMAL(15,2),"ORDER_PRICE"DECIMAL(8,4),TSIDvarchar(20))
insertintoRRTestSELECTOrder_ID,Order_ID,Order_ID,CUSTOMER_ID,ORDER_TYPE,EXECUTION_TYPE,ORDER_DATE,ORDER_TIME,ORDER_DATETIME,SIDE,TRADE_TYPE,ORDER_AMOUNT,
ORDER_PRICE,ORDER_IDFROMDB2INST1.Fx_OrderwhereORDER_DATE>'20070401'GOselectcount(*)FromRRTEST72239
ALTERTABLE"DB2INST1".RRTestADDPRIMARYKEY(pkID);
CREATEUNIQUEINDEXUNIQINDXONRRTest(unID1)CREATEINDEXINDX002ONRRTest(unID2)db2"RUNSTATSONTABLEDB2INST1.RRTestONALLCOLUMNSANDINDEXESALLALLOWWRITEACCESS"
db2connecttodb2TTdb2+c
select*FromRRTESTwhereTSID='20070223ORD01267732'forupdatewithRRselect*FromRRTESTwhereTSID='20070222ORD01266302'forupdatewithRR
select*FromRRTESTwhereTSID='20070223ORD01267732'forupdatewithRSselect*FromRRTESTwhereTSID='20070222ORD01266302'forupdatewithRS
select*FromRRTESTwhereunID1='20070223ORD01267732'forupdatewithRRselect*FromRRTESTwhereunID1='20070222ORD01266302'forupdatewithRRselect*FromRRTESTwhereunID1='20070223ORD01267732'forupdatewithRSselect*FromRRTESTwhereunID1='20070222ORD01266302'forupdatewithRS
select*FromRRTESTwhereunID2='20070223ORD01267732'forupdatewithRRselect*FromRRTESTwhereunID2='20070222ORD01266302'forupdatewithRRselect*FromRRTESTwhereunID2='20070223ORD01267732'forupdatewithRSselect*FromRRTESTwhereunID2='20070222ORD01266302'forupdatewithRS
select*FromRRTESTwherepkID='20070223ORD01267732'forupdatewithRRselect*FromRRTESTwherepkID='20070222ORD01266302'forupdatewithRRselect*FromRRTESTwherepkID='20070223ORD01267732'forupdatewithRSselect*FromRRTESTwherepkID='20070222ORD01266302'forupdatewithRS
按照以上字段上建立索引。
pkID是主键，unID1是唯一健索引，unID2是普通健索引，TSID是普通字段，没有在
试验结论：
PK_INDEXUNIQ_INDEXNormalINDEXNO_INDEXWITHRR锁行,不锁表锁行,不锁表不锁行,不锁表(1)锁行,锁表WITHRS锁行,不锁表锁行,不锁表锁行,不锁表锁行,锁表(2)
锁行是指在一个事务中用某种方式读取并更改了改行数据并显示得指明要修改后，这个事务将锁住改行，直到它提交或者回滚了事务后，才释放该锁。锁表是指在用以上各种SQL在读取并更改一行的同时锁住了整个表。对以上红字部分(1)可能有不能理解的是:为什么对普通索引和主键或者唯一健索引的不同结论?对PK和UNIQ的解释是因为RR是可重复的读的级别，对这次检索扫描到的有可能成为自己的潜在
检索对象的内容都会锁住，而因为是主键或者唯一健，别的行不可能成为这次这个检索的潜在读的范围，就是对别的数据此事务根本就没有必要锁，任何情况的更改都不可能出现幻读的情况(此表上的约束限制），所以只锁这一行。这么理解对PK,UNIQ没有问题。但是NormalINDEX我认为应该是锁住这个表而不是不锁。这点一直没想明白。留待以后再加强理解。对RS隔离级别是“锁定检索到的数据行”，是通过SQL检索到的结果进行锁定，PK,UNIQ,INDEX的结对tableScan的检索而出现的锁表有些象RR隔离级别的所为。
论完全都可以理解。
嗯，想了一圈没想明白，故把详细过程贴出来给自己留个纪念，以供以后遇到此类并发控制程序中注意一下，select*FromTTTwhere****=?forupdatewithRR(RS)，这里的隔离级别分为RR/RS/CS/UR这四个级别。下面让我们来逐一论述：1．RR隔离级别：在此隔离级别下，DB2会锁住所有相关的纪录。在一个SQL语句执行期间，所有执行此语句扫描过的纪录都会被加上相应的锁。具体的锁的类型还是由操作的类型来决定，如果是读取，则加共享锁；如果是更新，则加独占锁。由于会锁定所有为获得SQL语句的结果而扫描的纪录，所以锁的数量可能会很庞大，这个时候，索引的增加可能会对SQL语句的执行有很大的影响，因为索引会影响SQL语句扫描的纪录数量。2．RS隔离级别：此隔离级别的要求比RR隔离级别稍弱，此隔离级别下会锁定所有符合条件的纪录。不论是读取，还是更新，如果SQL语句中包含查询条件，则会对所有符合条件的纪录加相应的锁。如果没有条件语句，也就是对表中的所有记录进行处理，则会对所有的纪录加锁。3．CS隔离级别：此隔离级别仅锁住当前处理的纪录。***可不是随便定义的。
4．UR隔离级别：此隔离级别下，如果是读取操作，不会出现任何的行级锁。对于非只读的操作，它的锁处理和CS相同。
DB2默认的隔离级别是DB2分页查询
CS。即
游标稳定性。
SELECT*FROM(Select字段1,字段2,字段3,rownumber()over(ORDERBY排序用的列名ASC)ASrnfrom表名)ASa1WHEREa1.rnBETWEEN10AND20SELECT*FROM(SelectT.*,rownumber()over(ORDERBYT.IDASC)ASrnfromADMINISTRATOR.TEST_BY_HUFENGT)ASa1WHEREa1.rnBETWEEN1AND3
1.“||”的字符连接问题在DB2中是用“||”连接字符串的，这点与别的语言和数据库是使用“＋”有很大区别。在使用“||”时经常会出现“[IBM][CLIDriver][DB2/6000]SQL0440N未找到类型为"FUNCTION"命名为"||"且具有兼容自变量的已授权例程。SQLSTATE=42884”的错误，原因是有非字符类型参与了“||”运算，如有变量或字段出现在“||”的运算中，解决办法是把变量或字段传给char()函数再参与运算就OK了。下面举个例子：--会出错,INF年份，IYF月份
selectA.INF||'-'||A.IYF||'-01'fromtableAA
selectchar(A.INF)||'-'||char(A.IYF)||'-01'fromtableAA
--正确
2.时间比较问题在DB2中不能使用DATEDIFF函数，或者说是我的不能用，那么怎么比较两个时间相差多少并返回以日、月或年等为时间单位的值呢？用timestampdiff()函数！例子：
selecttimestampdiff(64,char(timestamp(A.DCRAETETIME)-timestamp('2007-5-25-13.56.41')))fromtableAA--其中64表示返回以月为单位的值比如现在是oracle是::sql=sql+"andLoginTime>=to_date('"+startTime+"00:00:00"+"','yyyy-mm-ddhh24:mi:ss')";那在DB2里应该怎么写才能实现这个查询啊?DB2如下
TO_DATE('2000-01-0110:00:01','yyyy-mm-ddhh24:mi:ss')
3.like后面不能跟字段如这样写会出错：
selectitem1fromtable1whereitem2likeitem3||'%'如果仅仅是想写像上面那样的like，可以用left或substr函数：
selectitem1fromtable1whereleft(item2,length(item3))=item3或
selectitem1fromtable1wheresubstr(item2,1,length(item3))=item3
oracle
selectitem1fromtable1whereitem2like'%'||item2
db2selectitem1fromtable1wheresubstr(item2,abs(length(item2)-length(item3))+1,length(item3))=item3.
1,isnullconvertcoalescefunction2,stringlinkSQLchar+convert||char3,join-condition,Itcannotcontainanysubqueries4,SQLServer的top或Oraclerownum函数，在DB2中:select*from(select或者select*fromorgorderbyorg.iddescFETCHFIRST3ROWSONLY5,lenconvertlengthfunction\6,to_date日期比较，DB2是TO_DATE('2000-01-0100:00:00','yyyy-mm-ddhh24:mi:ss')7,比较两个时间相差多少并返回以日、月或年等为时间单位的值,用timestampdiff()函数.如selecttimestampdiff(64,char(timestamp(A.DCRAETETIME)-timestamp('2007-5-25-13.56.41')))fromtableAA8,HQL语句里以冒号变量名后面是isnull的，要转换变量名,如fromprojectwhere:idisnull转换为DB2的fromprojectwherecast(:idasstring)isnull9,DB2substring涵数是substr(str,strart,count),strart以1开始13:28浏览(348)评论(0)ROW_NUMBER()over(ordeyorg.iddesc)asa,org.*fromorg)
astempwherea>=5anda<=100
???
?
缩略显示
分类:数据库2008-12-03
总结)通过分析SQL语句的执行计划优化SQL(总结总结
关键字:oracle做DBA快7年了，中间感悟很多。在DBA的日常工作中，调整个别性能较差的SQL语句时一项富有挑战性的工作。其中的关键在于如何得到SQL语句的执行计划和如何从SQL语句的执行计划中发现问题。总是想将日常经验的点点滴滴总结一下，但是直到最近才下定决心，总共花了3个周末时间，才将其整理成册，便于自己日常工作。不好意思独享，所以将其贴出来。第一章、第2章并不是很重要，是自己的一些想法，关于如何做一个稳定、高效的应用系统的一些想法。第三章以后都是比较重要的。附录的内容也是比较重要的。我常用该部分的内容。前言本文档主要介绍与SQL调整有关的内容，内容涉及多个方面：SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步步入SQL调整之门，然后你将发现……。该文档的不当之处，敬请指出，以便进一步改正。请将其发往我的信箱：xu_yu_jin2000@sina。如果引用本文的内容，请著名出处！
作者：徐玉金MSN：sunny_xyj@hotmailEmail:xu_yu_jin2000@sina日期：2005.12.12活跃于:cnoug.orgSunnyXu目录
第1章性能调整综述第2章有效的应用设计第3章SQL语句处理的过程第4章ORACLE的优化器第5章ORACLE的执行计划访问路径(方法)--accesspath
表之间的连接如何产生执行计划如何分析执行计划如何干预执行计划--使用hints提示具体案例分析第6章其它注意事项附录第1章性能调整综述Oracle数据库是高度可调的数据库产品。本章描述调整的过程和那些人员应与Oracle服务器的调整有关，以及与调整相关联的操作系统硬件和软件。本章包括以下方面:llllll谁来调整系统?什么时候调整?建立有效调整的目标在设计和开发时的调整调整产品系统监控产品系统
谁来调整系统:谁来调整系统为了有效地调整系统，若干类人员必须交换信息并牵涉到系统调整中，例如:ll应用设计人员必须传达应用系统的设计，使得每个人都清楚应用中的数据流动.应用开发人员必须传达他们选择的实现策略，使得语句调整的过程中能快速、容易地识别有问题的数据库管理人员必须仔细地监控系统活动并提供它们的资料，使得异常的系统性能可被快速得识别硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料，使得相关人员能有效地设计
应用模块和可疑的SQL语句.l和纠正.l和管理系统。简而言之，与系统涉及的每个人都在调整过程中起某些作用，当上面提及的那些人员传达了系统的特性并提供了它们的资料，调整就能相对的容易和更快一些。不幸的是，事实上的结果是：数据库管理员对调整负有全部或主要的责任。但是，数据库管理员很少有合适的系统方面的资料，而且，在很多情况下，数据库管理员往往是在实施阶段才介入数据库，这就给调整工作带来许多负面的影响，因为在设计阶段的缺陷是不能通过DBA的调整而得以解决，而设计阶段的缺陷往往对数据库性能造成极大的影响。其实，在真正成熟的开发环境下，开发人员作为纯代码编写人员时，对性能的影响最小，此时大部分的工作应由应用设计人员完成，而且数据库管理员往往在前期的需求管理阶段就介入，为设计人员提供必要的技术支持。调整并不是数据库管理员的专利，相反大部分应该是设计人员和开发人员的工作，这就需要设计人员和开发人员具体必要的数据库知识，这样才能组成一个高效的团队，然而事实上往往并非如此。什么时候作调整？什么时候作调整？多数人认为当用户感觉性能差时才进行调整，这对调整过程中使用某些最有效的调整策略来说往往是太迟了。此时，如果你不愿意重新设计应用的话，你只能通过重新分配内存(调整SGA)和调整I/O的办法或
多或少地提高性能。Oracle提供了许多特性，这些特性只有应用到正确地设计的系统中时才能够很大地提高性能。应用设计人员需要在设计阶段设置应用的性能期望值。然后在设计和开发期间，应用设计人员应考虑哪些Oracle特性可以对系统有好处，并使用这些特性。通过良好的系统设计，你就可以在应用的生命周期中消除性能调整的代价和挫折。图1-1图1-2说明在应用的生命周期中调整的相对代价和收益，正如你见到的，最有效的调整时间是在设计阶段。在设计期间的调整能以最低的代价给你最大的收益。
图1-1
在应用生命周期中调整的代价
图1-2在应用生命周期中调整的收益当然，即使在设计很好的系统中，也可能有性能降低。但这些性能降低应该是可控的和可以预见的。调整目标不管你正在设计或维护系统，你应该建立专门的性能目标，它使你知道何时要作调整。如果你试图胡乱地改动初始化参数或SQl语句，你可能会浪费调整系统的时间，而且无什么大的收益。调整你的系统的最有效方法如下：lllll当设计系统时考虑性能调整操作系统的硬件和软件识别性能瓶颈确定问题的原因采取纠正的动作
当你设计系统时，制定专门的目标；例如，响应时间小于3秒。当应用不能满足此目标时，识别造成变慢的瓶颈（例如，I/O竞争），确定原因，采取纠正动作。在开发期间，你应测试应用研究，确定在采取应用之前是否满足设计的性能目标。当你正在维护生产库系统时，有多种快速有效的方法来识别性能瓶颈。不管怎样，调整通常是一系列开销。一旦你已确定了瓶颈，你可能要牺牲一些其它方面的指标来达到所要的结果。例如，如果I/O有问题，你可能需要更多内存或磁盘。如果不可能买，你可能要限制系统的并发性，来获取所需的性能。然而，如果你已经明确地定义了性能的目标，那用什么来交换高性能的决策就变的很容易的，因为你已经确定了哪些方面是最重要的，如过我的目标为高性能，可能牺牲一些空间资源。随着应用的越来越庞大，硬件性能的提高，全面的调整应用逐渐变成代价高昂的行为，在这样情况下，要取得最大的投入/效率之比，较好的办法是调整应用的关键部分，使其达到比较高的性能，这样从总体上来说，整个系统的性能也是比较高的。这也就是有名的20/80原则，调整应用的20%(关键部分)，能解决80%的问题。在设计和开发系统时作调整良好设计的系统可以防止在应用生命周期中产生性能问题。系统设计人员和应用开发人员必须了解
Oracle的查询处理机制以便写出高效的SQL语句。“第2章有效的应用设计”讨论了你的系统中各种可用的配置，以及每种配置更适合哪种类型的应用。“第5章优化器”讨论了Oracle的查询优化器，以及如何写语句以获取最快的结果。当设计你的系统时，使用下列优化性能的准则：llllll消除客户机／服务器应用中不必要的网络传输。--使用存储过程。使用适合你系统的相应Oracle服务器选件（例如，并行查询或分布式数据库）。除非你的应用有特殊的需要，否则使用缺省的Oracle锁。利用数据库记住应用模块，以便你能以每个模块为基础来追踪性能。选择你的数据块的最佳大小。--原则上来说大一些的性能较好。分布你的数据，使得一个节点使用的数据本地存贮在该节点中。
调整产品系统本节描述对应用系统快速、容易地找出性能瓶颈，并决定纠正动作的方法。这种方法依赖于对Oracle服务器体系结构和特性的了解程度。在试图调整你的系统前，你应熟悉Oracle调整的内容。为调整你已有的系统，遵从下列步骤：ll调整操作系统的硬件和软件通过查询V$SESSION_WAIT视图，识别性能的瓶颈，这个动态性能视图列出了造成会话(session)通过分析V$SESSION_WAIT中的数据，决定瓶颈的原因。纠正存在的问题。
等待的事件。ll
监控应用系统这主要是通过监控oracle的动态视图来完成。各种有用的动态视图：如v$session_wait,v$session_event等。
第2章有效的应用设计我们通常将最常用的应用分为2种类型：联机事务处理类型(OLTP)，决策支持系统(DSS)。联机事务处理(OLTP)联机事务处理该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特征，它们提供给成百用户同时存取，典型的OLTP系统是订票系统，银行的业务系统，订单系统。OTLP的主要目标是可用性、速度、并发性和可恢复性。当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与cluster表，因为这些结构会使插入和更新操作变慢。决策支持(DSS)决策支持该类型的应用将大量信息进行提取形成报告，协助决策者作出正确的判断。典型的情况是：决策支持系统将OLTP应用收集的大量数据进行查询。典型的应用为客户行为分析系统(超市，保险等)。决策支持的关键目标是速度、精确性和可用性。该种类型的设计往往与OLTP设计的理念背道而驰，一般建议使用数据冗余、大量索引、cluster
table、并行查询等。近年来，该类型的应用逐渐与OLAP、数据仓库紧密的联系在一起，形成的一个新的应用方向。
??????
缩略显示
pdf.rar(552.6KB)下载次数:7710:23浏览(760)评论(8)分类:数据库
2008-12-02
Oracle中的HashJoin祥解
关键字:oracle、hashjoin概念hashjoin(HJ)是一种用于equi-join（而anti-join就是使用NOTIN时的join）的技术。在Oracle中，它是从7.3开始引入的，以代替sort-merge和nested-loopjoin方式，提高效率。在CBO（hashjoin只有在CBO才可能被使用到）模式下，优化器计算代价时，首先会考虑hashjoin。可以通过提示use_hash来强制使用hashjoin，也可以通过修改会话或数据库参数HASH_JOIN_ENABLED=FALSE（默认为TRUE）强制不使用hashjoin。Hashjoin的主要资源消耗在于CPU（在内存中创建临时的hash表，并进行hash计算），而mergejoin的资源消耗主要在于此盘IO（扫描表或索引）。在并行系统中，hashjoin对CPU的消耗更加明显。所以在CPU紧张时，最好限制使用hashjoin。在绝大多数情况下，hashjoin效率比其他join方式效率更高：在Sort-MergeJoin(SMJ)，两张表的数据都需要先做排序，然后做merge。因此效率相对最差；Nested-LoopJoin(NL)效率比SMJ更高。特别是当驱动表的数据量很大（集的势高）时。这样可以并行扫描内表。Hashjoin效率最高，因为只要对两张表扫描一次。Hashjoin一般用于一张小表和一张大表进行join时。Hashjoin的过程大致如下（下面所说的内存就指sortarea，关于过程，后面会作详细讨论）：1．一张小表被hash在内存中。因为数据量小，所以这张小表的大多数数据已经驻入在内存中，剩下的少量数据被放置在临时表空间中；2．每读取大表的一条记录，就和小表中内存中的数据进行比较，如果符合，则立即输出数据（也就是说没有读取临时表空间中的小表的数据）。而如果大表的数据与小表中临时表空间的数据相符合，则不直接输出，而是也被存储临时表空间中。3．当大表的所有数据都读取完毕，将临时表空间中的数据以其输出。如果小表的数据量足够小（小于hashareasize），那所有数据就都在内存中了，可以避免对临时表空间的读写。
如果是并行环境下，前面中的第2步就变成如下了：2．每读取一条大表的记录，和内存中小表的数据比较，如果符合先做join，而不直接输出，直到整张大表数据读取完毕。如果内存足够，Join好的数据就保存在内存中。否则，就保存在临时表空间中。
二、Oracle中与hashjoin相关的参数
首先，要注意的是，hashjoin只有在CBO方式下才会被激活。在oracle中与hashjoin相关的参数主要有以下几个：
1．．
HASH_JOIN_ENABLED
这个参数是控制查询计划是否采用hashjoin的“总开关”。它可以在会话级和实例级被修改。默
认为TRUE，既可以（不是一定，要看优化器计算出来的代价）使用。如果设为FALSE，则禁止使用hashjoin。
2．．
HASH_AREA_SIZE
这个参数控制每个会话的hash内存空间有多大。它也可以在会话级和实例级被修改。默认（也是
推荐）值是sortarea空间大小的两倍（2*SORT_AREA_SIZE）。要提高hashjoin的效率，就一定尽量保证sortarea足够大，能容纳下整个小表的数据。但是因为每个会话都会开辟一个这么大的内存空间作为hash内存，所以不能过大（一般不建议超过2M）。在Oracle9i及以后版本中，Oracle不推荐在dedicatedserver中使用这个参数来设置hash内存，而是推荐通过设置PGA_AGGRATE_TARGET参数来自动管理PGA内存。保留HASH_AREA_SIZE只是为了向后兼容。在dedicatedserver中，hasharea是从PGA中分配的，而在MTS(Multi-ThreadedServer)中，hasharea是从UGA中分配的。另外，还要注意的是，每个会话并不一定只打开一个hasharea，因为一个查询中可能不止一个hashjoin，这是就会相应同时打开多个hasharea。
3．．
HAHS_MULTIBLOCK_IO_COUNT
这个参数决定每次读入hasharea的数据块数量。因此它会对IO性能产生影响。他只能在
init.ora或spfile中修改。在8.0及之前版本，它的默认值是1，在8i及以后版本，默认值是0。一般设置为1-(65536/DB_BLOCK_SIZE)。在9i中，这个参数是一个隐藏参数：_HASH_MULTIBLOCK_IO_COUNT，可以通过表x$ksppi查询和修改。另外，在MTS中，这个参数将不起作用（只会使用1）。它的最大值受到OS的IO带宽和DB_BLOCK_SIZE的影响。既不能大于MAX_IO_SIZE/DB_BLOCK_SIZE。在8i及以后版本，如果这个值设置为0，则表示在每次查询时，Oracle自己自动计算这个值。这个值对IO性能影响非常大，因此，建议不要修改这个参数，使用默认值0，让Oracle自己去计算这个值。如果一定要设置这个值，要保证以下不等式能成立：
R/M 三、Hashjoin的过程
一次完整的hashjoin如下：
1．．
计算小表的分区（计算小表的分区（bucket）数）
决定hashjoin的一个重要因素是小表的分区（bucket）数。这个数字由hash_area_size、
hash_multiblock_io_count和db_block_size参数共同决定。Oracle会保留hasharea的20%来存储分区的头信息、hash位图信息和hash表。因此，这个数字的计算公式是：Bucket数=0.8*hash_area_size/(hash_multiblock_io_count*db_block_size)
2．．
Hash计算
读取小表数据（简称为R），并对每一条数据根据hash算法进行计算。Oracle采用两种hash算
法进行计算，计算出能达到最快速度的hash值（第一hash值和第二hash值）。而关于这些分区的全部hash值（第一hash值）就成为hash表。
3．．
存放数据到hash内存中
将经过hash算法计算的数据，根据各个bucket的hash值（第一hash值）分别放入相应的bucket
中。第二hash值就存放在各条记录中。
4．．
创建hash位图
与此同时，也创建了一个关于这两个hash值映射关系的hash位图。
5．．
超出内存大小部分被移到磁盘
如果hasharea被占满，那最大一个分区就会被写到磁盘（临时表空间）上去。任何需要写入到
磁盘分区上的记录都会导致磁盘分区被更新。这样的话，就会严重影响性能，因此一定要尽量避免这种情况。2-5一直持续到整个表的数据读取完毕。
6．．
对分区排序
为了能充分利用内存，尽量存储更多的分区，Oracle会按照各个分区的大小将他们在内存中排序。
7．．
读取大表数据，读取大表数据，进行hash匹配
接下来就开始读取大表（简称S）中的数据。按顺序每读取一条记录，计算它的hash值，并检查
是否与内存中的分区的hash值一致。如果是，返回join数据。如果内存中的分区没有符合的，就将S中的数据写入到一个新的分区中，这个分区也采用与计算R一样的算法计算出hash值。也就是说这些
S中的数据产生的新的分区数应该和R的分区集的分区数一样。这些新的分区被存储在磁盘（临时表空间）上。
8．．
完全大表全部数据的读取
一直按照7进行，直到大表中的所有数据的读取完毕。
9．．
处理没有join的数据
这个时候就产生了一大堆join好的数据和从R和S中计算存储在磁盘上的分区。
10．二次hash计算．
从R和S的分区集中抽取出最小的一个分区，使用第二种hash函数计算出并在内存中创建hash表。采用第二种hash函数的原因是为了使数据分布性更好。
11．二次hash匹配．
在从另一个数据源（与hash在内存的那个分区所属数据源不同的）中读取分区数据，与内存中的新hash表进行匹配。返回join数据。
12．完成全部hashjoin．
继续按照9-11处理剩余分区，直到全部处理完毕。整个hashjoin就完成了。
四、关于唯一健值的hash位图
这个位图包含了每个hash分区是否有有值的信息。它记录了有数据的分区的hash值。这个位图的最大作用就是，如果S表中的数据没有与内存中的hash表匹配上，先查看这个位图，已决定是否将没有匹配的数据写入磁盘。那些不可能匹配到的数据（即位图上对应的分区没有数据）就不再写入磁盘。
????
缩略显示
13:41浏览(478)评论(0)分类:数据库2008-11-27
oracle分区表总结转)分区表总结(转
关键字:oracle
在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。1.1分区表PARTITIONtable
在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。
1.1.1分区表的建立：
某公司的每年产生巨大的销售记录，DBA向公司建议每季度的数据放在一个分区内，以下示范的是该公司1999年的数据(假设每月产生30M的数据)，操作如下：
范围分区表：CREATETABLEsales(invoice_noNUMBER,...sale_dateDATENOTNULL)PARTITIONBYRANGE(sale_date)(PARTITIONsales1999_q1
VALUESLESSTHAN(TO_DATE(‘1999-04-01’,’YYYY-MM-DD’)
TABLESPACEts_sale1999q1,PARTITIONsales1999_q2
VALUESLESSTHAN(TO_DATE(‘1999-07-01’,’YYYY-MM-DD’)
TABLESPACEts_sale1999q2,PARTITIONsales1999_q3
VALUESLESSTHAN(TO_DATE(‘1999-10-01’,’YYYY-MM-DD’)
TABLESPACEts_sale1999q3,PARTITIONsales1999_q4
VALUESLESSTHAN(TO_DATE(‘2000-01-01’,’YYYY-MM-DD’)
TABLESPACEts_sale1999q4);--valueslessthan(maxvalue)列表分区表：createtableemp(empnonumber(4),enamevarchar2(30),locationvarchar2(30))partitionbylist(location)
(partitionp1values('北京'),partitionp2values('上海','天津','重庆'),partitionp3values('广东','福建')
partitionp0values(default));哈希分区：createtableemp(empnonumber(4),enamevarchar2(30),salnumber)partitionbyhash(empno)partitions8storein(emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
组合分区：范围哈希组合分区：createtableemp(empnonumber(4),enamevarchar2(30),hiredatedate)partitionbyrange(hiredate)subpartitionbyhash(empno)subpartitions2(partitione1valueslessthan(to_date('20020501','YYYYMMDD')),partitione2valueslessthan(to_date('20021001','YYYYMMDD')),partitione3valueslessthan(maxvalue));范围列表组合分区：CREATETABLEcustomers_part(customer_idNUMBER(6),cust_first_nameVARCHAR2(20),cust_last_nameVARCHAR2(20),nls_territoryVARCHAR2(30),credit_limitNUMBER(9,2))PARTITIONBYRANGE(credit_limit)SUBPARTITIONBYLIST(nls_territory)SUBPARTITIONTEMPLATE(SUBPARTITIONeastVALUES('CHINA','JAPAN','INDIA','THAILAND'),SUBPARTITIONwestVALUES('AMERICA','GERMANY','ITALY','SWITZERLAND'),SUBPARTITIONotherVALUES(DEFAULT))(PARTITIONp1VALUESLESSTHAN(1000),PARTITIONp2VALUESLESSTHAN(2500),PARTITIONp3VALUESLESSTHAN(MAXVALUE));
createtablet1(id1number,id2number)partitionbyrange(id1)subpartitionbylist(id2)(partitionp11valueslessthan(11)(subpartitionsubp1values(1)));索引分区：CREATEINDEXmonth_ixONsales(sales_month)GLOBALPARTITIONBYRANGE(sales_month)(PARTITIONpm1_ixVALUESLESSTHAN(2)PARTITIONpm12_ixVALUESLESSTHAN(MAXVALUE));1.1.2分区表的维护：
增加分区：
ALTERTABLEsalesADDPARTITIONsales2000_q1
VALUESLESSTHAN(TO_DATE(‘2000-04-01’,’YYYY-MM-DD’)
TABLESPACEts_sale2000q1;
如果已有maxvalue分区，不能增加分区，可以采取分裂分区的办法增加分区！
删除分区：ALTERTABLEsalesDROPPARTIONsales1999_q1;截短分区:
altertablesalestruncatepartitonsales1999_q2;合并分区：altertablesalesmergepartitonssales1999_q2,sales1999_q3intosales1999_q23;alterindexind_t2rebuildpartitionp123parallel2;
分裂分区：
ALTERTABLEsalesSPLITPARTITONsales1999_q4
ATTO_DATE(‘1999-11-01’,’YYYY-MM-DD’)
INTO(partitionsales1999_q4_p1,partitionsales1999_q4_p2);altertablet2splitpartitionp123values(1,2)into(partitionp12,partitionp3);
交换分区:
altertablexexchangepartitionp0withtablebsvcbusrundatald;访问指定分区：select*fromsalespartition(sales1999_q2)
EXPORT指定分区：
expsales/sales_passwordtables=sales:sales1999_q1file=sales1999_q1.dmp
IMPORT指定分区：
impsales/sales_passwordFILE=sales1999_q1.dmpTABLES=(sales:sales1999_q1)IGNORE=y
查看分区信息：user_tab_partitions,user_segments注：若分区表跨不同表空间，做导出、导入时目标数据库必须预建这些表空间。分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间！这些表空间不一定是用户的默认表空间，只要存在即可。如果有一个不存在，就会报错！默认时，对分区表的许多表维护操作会使全局索引不可用，标记成UNUSABLE。那么就必须重建整个全局索引或其全部分区。如果已被分区，Oracle允许在用于维护操作的ALTERTABLE语句中指定UPDATEGLOBALINDEXES来重载这个默认特性，指定这个子句也就告诉Oracle当它执行维护操作的DDL语句时更新全局索引，这提供了如下好处：1.在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引；2.因为没有被标记成UNUSABLE，所以全局索引的可用性更高了，甚至正在执行分区的DDL语句时仍然可用索引来访问表中的其他分区,避免了查询所有失效的全局索引的名字以便重建它们；另外在指定UPDATEGLOBALINDEXES之前还要考虑如下性能因素:1.因为要更新事先被标记成UNUSABLE的索引，所以分区的DDL语句要执行更长时间，当然这要与先不更新索引而执行DDL然后再重建索引所花的时间做个比较，一个适用的规则是如果分区的大小小于表的大小的5%，则更新索引更
快一点；2.DROPTRUNCATE和EXCHANGE操作也不那么快了，同样这必须与先执行DDL然后再重建所有全局索引所花的时间做个比较；3.要登记对索引的更新并产生重做记录和撤消记录，重建整个索引时可选择NOLOGGING；4.重建整个索引产生一个更有效的索引，因为这更利于使用空间，再者重建索引时允许修改存储选项。注意分区索引结构表不支持UPDATEGLOBALINDEXES子句。
1.1.3普通表变为分区表
将已存在数据的普通表转变为分区表，没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，一般可以有三种方法，视不同场景使用：用例：方法一：利用原表重建分区表。CREATETABLET(IDNUMBERPRIMARYKEY,TIMEDATE);
INSERTINTOTSELECTROWNUM,SYSDATE-ROWNUMFROMDBA_OBJECTSWHEREROWNUM<=5000;
COMMIT;
CREATETABLET_NEW(ID,TIME)PARTITIONBYRANGE(TIME)(PARTITIONP1VALUESLESSTHAN(TO_DATE('2000-1-1','YYYY-MM-DD')),PARTITIONP2VALUESLESSTHAN(TO_DATE('2002-1-1','YYYY-MM-DD')),PARTITIONP3VALUESLESSTHAN(TO_DATE('2005-1-1','YYYY-MM-DD')),PARTITIONP4VALUESLESSTHAN(MAXVALUE))ASSELECTID,TIMEFROMT;
RENAMETTOT_OLD;RENAMET_NEWTOT;SELECTCOUNT(*)FROMT;COUNT(*)---------5000SELECTCOUNT(*)FROMTPARTITION(P1);COUNT(*)---------2946SELECTCOUNT(*)FROMTPARTITION(P2);COUNT(*)---------731SELECTCOUNT(*)FROMTPARTITION(P3);COUNT(*)---------1096
优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATETABLE语句和RENAMET_NEWTOT语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。方法二：使用交换分区的方法。Droptablet;CREATETABLET(IDNUMBERPRIMARYKEY,TIMEDATE);
INSERTINTOTSELECTROWNUM,SYSDATE-ROWNUMFROMDBA_OBJECTSWHEREROWNUM<=5000;
COMMIT;
CREATETABLET_NEW(IDNUMBERPRIMARYKEY,TIMEDATE)PARTITIONBYRANGE(TIME)(PARTITIONP1VALUESLESSTHAN(TO_DATE('2005-9-1','YYYY-MM-DD')),PARTITIONP2VALUESLESSTHAN(MAXVALUE));
ALTERTABLET_NEWEXCHANGEPARTITIONP1WITHTABLET;RENAMETTOT_OLD;RENAMET_NEWTOT;
优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。不足：仍然存在一致性问题，交换分区之后RENAMET_NEWTOT之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。方法三：Oracle9i以上版本，利用在线重定义功能Droptablet;CREATETABLET(IDNUMBERPRIMARYKEY,TIMEDATE);
INSERTINTOTSELECTROWNUM,SYSDATE-ROWNUMFROMDBA_OBJECTSWHEREROWNUM<=5000;
COMMIT;
EXECDBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'T');
PL/SQL过程已成功完成。
CREATETABLET_NEW(IDNUMBERPRIMARYKEY,TIMEDATE)PARTITIONBYRANGE(TIME)(PARTITIONP1VALUESLESSTHAN(TO_DATE('2004-7-1','YYYY-MM-DD')),PARTITIONP2VALUESLESSTHAN(TO_DATE('2005-1-1','YYYY-MM-DD')),PARTITIONP3VALUESLESSTHAN(TO_DATE('2005-7-1','YYYY-MM-DD')),PARTITIONP4VALUESLESSTHAN(MAXVALUE));
表已创建。
EXECDBMS_REDEFINITION.START_REDEF_TABLE(USER,'T','T_NEW');
PL/SQL过程已成功完成。
EXECDBMS_REDEFINITION.FINISH_REDEF_TABLE(USER,'T','T_NEW');
PL/SQL过程已成功完成。
&, nbsp; SELECTCOUNT(*)FROMT;COUNT(*)---------5000SELECTCOUNT(*)FROMTPARTITION(P3);COUNT(*)---------1096
优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。不足：实现上比上面两种略显复杂。适用于各种情况。这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。Oracle的在线重定义表功能：blog.itpub.net/post/468/12855Oracle的在线重定义表功能（二）：blog.itpub.net/post/468/12962XSB:把一个已存在数据的大表改成分区表：第一种（表不是太大）：1.把原表改名：renamexsb1toxsb2;2.创建分区表：CREATETABLExsb1PARTITIONBYLIST(c_test)(PARTITIONxsb1_p1VALUES(1),PARTITIONxsb1_p2VALUES(2),PARTITIONxsb1_p0VALUES(default))nologgingASSELECT*FROMxsb2;3.将原表上的触发器、主键、索引等应用到分区表上；
4.删除原表：droptablexsb2;第二种(表很大)：1.创建分区表：CREATETABLExPARTITIONBYLIST(c_test)[range()](PARTITIONp0VALUES[lessthan](1)tablespacetbs1,PARTITIONp2VALUES(2)tablespacetbs1,PARTITIONxsb1_p0VALUES([maxvalue]default))ASSELECT*FROMxsb2[where1=2];2.交换分区altertablexexchangepartitionp0withtablebsvcbusrundatald;3.原表改名altertablebsvcbusrundataldrenametox0;4.新表改名altertablexrenametobsvcbusrundatald;5.删除原表droptablex0;6.创建新表触发器和索引createindexind_busrundata_lponbsvcbusrundatald(。。。)localtablespacetbs_brd_ind;或者：1.规划原大表中数据分区的界限，原则上将原表中近期少量数据复制至另一表；2.暂停原大表中的相关触发器；3.删除原大表中近期数据；4.改名原大表名称；5.创建分区表；6.交换分区；7.重建相关索引及触发器（先删除之再重建）.参考脚本：selectcount(*)fromt1whererecdate>sysdate-2createtablex2nologgingasselect*fromt1whererecdate>trunc(sysdate-2)altertrigertrg_t1disabledeletet1whererecdate>sysdate-2commitrenamet1tox1createtablet1[nologging]partitionbyrange(recdate)(partitionpbeforevalueslessthan(trunc(sysdate-2)),partitionpmaxvalueslessthan(maxvalue))asselect*fromx1where1=2altertablet1exchangepartitionpbeforewithtablex1altertablet1exchangepartitionpmaxwithtablex2droptablex2[重建触发器]droptablex1
1.1.4参考材料：
如果表中预期的数据量较大，通常都需要考虑使用分区表，确定使用分区表后，还要确定什么类型的分区（rangepartition、hashpartition、listpartition等）、
分区区间大小等。分区的创建最好与程序有某种默契，偶曾经创建分区表，按自然月份定义分区的，但程序却在查询时默认的开始时间与结束时间是：当前日期-30至当前日期，比如当天是9.18号，那查询条件被产生为8.18-9.18，结果分区后并不没有大幅提高性能，后来对程序的查询日期做了调整，按自然月查询，系统的负载小了很多。从Oracle8.0开始支持表分区（MSSQL2005开始支持表分区）。Oracle9i分区能够提高许多应用程序的可管理性、性能与可用性。分区可以将表、索引及索引编排表进一步划分，从而可以更精细地对这些数据库对象进行管理和访问。Oracle提供了种类繁多的分区方案以满足所有的业务需要。另外，由于在SQL语句中是完全透明的，所以分区可以用于几乎所有的应用程序。分区表允许将数据分成被称为分区甚至子分区的更小的更好管理的块。索引也可以这么分区。每个分区可以被单独管理，可以不依赖于其他分区而单独发挥作用，因此提供了一个更有利于可用性和性能的结构。分区可以提高可管理性、性能与可用性，从而给各种各样的应用程序带来极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外，分区还能够在很大程度上简化日常管理任务。分区还使数据库设计人员和管理员能够解决尖端应用程序带来的最难的问题。分区是建立上亿万字节数据系统或需要极高可用性系统的关键工具。在多CPU配置环境下，如果打算使用并行执行，则分区提供了另一种并行的方法。通过给表或索引的不同分区分配不同的并行执行服务器，就可以并行执行对分区表和分区索引的操作。表或索引的分区和子分区都共享相同的逻辑属性。例如表的所有分区或子分区共享相同的列和约束定义，一个索引的分区或子分区共享相同的索引选项。然而它们可以具有不同的物理属性如表空间。尽管不需要将表或索引的每个分区或子分区放在不同的表空间，但这样做更好。将分区存储到不同的表空间能够减少数据在多个分区中冲突的可能性可以单独备份和恢复每个分区控制分区与磁盘驱动器之间的映射对平衡I/O负载是重要的改善可管理性可用性和性能分区操作对现存的应用和运行在分区表上的标准DML语句来说是透明的。但是可以通过在DML中使用分区扩展表或索引的名字来对应用编程，使其利用分区的优点。可以使用SQL*Loader、Import和Export工具来装载或卸载分区表中的数据。这些工具都是支持分区和子分区的。分区的方法Oracle9i提供了如下5种分区方法：范围分区Range散列分区Hash列表分区List组合范围-散列分区Range-Hash组合范围-列表分区Range-List
可对索引和表分区。全局索引只能按范围分区，但可以将其定义在任何类型的分区或非分区表上。通常全局索引比局部索引需要更多的维护。一般组建局部索引，以便反映其基础表的结构。它与基础表是等同分区的，即它与基础表在同样的列上分区，创建同样数量的分区或子分区，设置与基础表相对应的同样的分区边界。对局部索引而言，当维护活动影响分区时，会自动维护索引分区。这保证了索引与基础表之间的等同分区。关于范围分区Range：要想将行映射到基于列值范围的分区，就使用范围分区方法。当数据可以被划分成逻辑范围时如年度中的月份，这种类型的分区就有用了。当数据在整个范围中能被均等地划分时性能最好。如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时，就需要考虑其他的分区方法。关于散列分区Hash：如果数据不那么容易进行范围分区，但为了性能和管理的原因又想分区时，就使用散列分区方法。散列分区提供了一种在指定数量的分区中均等地划分数据的方法。基于分区键的散列值将行映射到分区中。创建和使用散列分区会给你提供了一种很灵活的放置数据的方法，因为你可以通过在I/O驱动器之间播撒(摘掉)这些均等定量的分区，来影响可用性和性能。关于列表分区List：当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值，这不同于范围分区，在那里一个范围与一个分区相关，这也不同于散列分区，在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或散列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和组织到一起。与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。关于组合范围-散列分区：范围和散列技术的组合，首先对表进行范围分区，然后用散列技术对每个范围分区再次分区。给定的范围分区的所有子分区加在一起表示数据的逻辑子集。关于组合范围-列表分区：范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。创建或更改分区表时可以指定行移动子句，即ENABLEROWMOVEMENT或者DISABLEROWMOVEMENT，当其键被更改时，该子句启用或停用将行迁移到一个新的分区。默认值为DISABLEROWMOVEMENT。本产品(项目)使用ENABLEROWMOVEMENT子句。分区技术能够提高数据库的可管理性：使用分区技术，维护操作可集中于表的特定部分。例如，数据库管理员可以只对表的一部分做备份，而不必对整个表做备份。对整个数据库对象的维护操作，可以在每个分区的基础上进行，从而将维护工作分解成更容易管理的小块。
分区技术提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程。假设数据库管理员每周向表中加载新数据。该表可以是范围分区，以便每个分区包含一周的数据。加载进程只是简单地添加新的分区。添加一个新分区的操作比修改整个表效率高很多，因为数据库管理员不需要修改任何其他分区。从分区后的表中去除数据也是一样。你只要用一个很简便快捷的数据字典操作删掉一个分区，而不必发出使用大量资源和调动所有要删除的数据的‘DELETE’命令。分区技术
????
缩略显示15:25浏览(523)评论(1)分类:数据库2008-11-27
oracle索引
关键字:oracle概述索引在各种关系型数据库系统中都是举足轻重的组成部分，其对于提高检索数据的速度起至关重要的作用。在Oracle中，索引基本分为以下几种：B*Tree索引，反向索引，降序索引，位图索引，函数索引，interMedia全文索引等。本文主要就前6种索引进行分析。首先给出各种索引的简要解释：b*treeindex：几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rid快速定位所访问的行。反向索引：反转了b*tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争。降序索引：8i中新出现的索引类型，针对逆向排序的查询。位图索引：使用位图来管理与数据行的对应关系，多用于OLAP系统。函数索引：这种索引中保存了数据列基于function返回的值，在select*fromtablewherefunction（column）=value这种类型的语句中起作用。B*Tree索引B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branchblock）和叶块（leafblock）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址，如图26-1所示。假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75－100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如wherecolumn>20andcolumn<80，那么会先定位到第一个包含20的叶块，然后横向查找其他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。反向索引
反向索引是B*Tree索引的一个分支，它的设计是为了运用在某些特定的环境下的。Oracle推出它的主要目的就是为了降低在并行服务器（OracleParallelServer）环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节，通过dump（）函数我们可以清楚得看见它做了什么。举个例子：1，2，3三个连续的数，用dump()函数看它们在Oracle内部的表示方法。SQL>select'number',dump(1,16)fromdual2unionallselect'number',dump(2,16)fromdual3unionallselect'number',dump(3,16)fromdual;'NUMBEDUMP(1,16)----------------------numberTyp=2Len=2:c1,2（1）numberTyp=2Len=2:c1,3（2）numberTyp=2Len=2:c1,4（3）再对比一下反向以后的情况：SQL>select'number',dump(reverse(1),16)fromdual2unionallselect'number',dump(reverse(2),16)fromdual3unionallselect'number',dump(reverse(3),16)fromdual;'NUMBEDUMP(REVERSE(1),1----------------------numberTyp=2Len=2:2,c1（1）numberTyp=2Len=2:3,c1（2）numberTyp=2Len=2:4,c1（3）我们发现索引码的结构整个颠倒过来了，这样1，2，3个索引码基本上不会出现在同一个叶块里，所以减少了争用。不过反向索引又一个缺点就是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用，例如，wherecolumn>value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。函数索引基于函数的索引也是8i以来的新产物，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件：（1）必须拥有QUERYREWRITE（本模式下）或GLOBALQUERYREWRITE（其他模式下）权限。（2）必须使用基于成本的优化器，基于规则的优化器将被忽略。（3）必须设置以下两个系统参数：QUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEGRITY=TRUSTED可以通过altersystemset,altersessionset在系统级或线程级设置，也可以通过在init.ora添加实现。这里举一个基于函数的索引的例子：SQL>createindextest.ind_funontest.testindex(upper(a));索引已创建。SQL>insertintotestindexvalues('a',2);已创建1行。SQL>commit;提交完成。
SQL>select/*+RULE*/*FROMtest.testindexwhereupper(a)='A';A-----------a2B
ExecutionPlan---------------------------------------------------------010SELECTSTATEMENTOptimizer=HINT:RULETABLEACCESS(FULL)OF'TESTINDEX'
(优化器选择了全表扫描)-------------------------------------------------------------------SQL>select*FROMtest.testindexwhereupper(a)='A';A-----------a2B
ExecutionPlan---------------------------------------------------------010SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=5)TABLEACCESS(BYINDEXROWID)OF'TESTINDEX'(Cost=2Card=
1Bytes=5)21INDEX(RANGESCAN)OF'IND_FUN'(NON-UNIQUE)(Cost=1Card=1)(使用了ind_fun索引)降序索引降序索引是8i里面新出现的一种索引，B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式是从升序变成了降序，在某些场合下降序索引将会起作用。举个例子，我们来查询一张表并进行排序：SQL>select*fromtestwhereabetween1and100orderbyadesc，basc;已选择100行。ExecutionPlan---------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=100Bytes=400)
10SORT(ORDERBY)(Cost=2Card=100Bytes=400)21INDEX(RANGESCAN)OF'IND_BT'(NON-UNIQUE)(Cost=2Card=100Bytes=400)这里优化器首先选择了一个索引范围扫描,然后还有一个排序的步骤。如果使用了降序索引，排序的过程会被取消。SQL>createindextest.ind_descontest.testrev(adesc，basc);索引已创建。SQL>analyzeindextest.ind_desccomputestatistics;索引已分析再来看下执行路径：SQL>select*fromtestwhereabetween1and100orderbyadesc，basc;已选择100行。ExecutionPlan（SQL执行计划，稍后会讲解如何使用）。---------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=100Bytes=400)
10INDEX(RANGESCAN)OF'IND_DESC'(NON-UNIQUE)(Cost=2Card=100Bytes=400)我们看到排序过程消失了，这是因为创建降序索引时Oracle已经把数据都按降序排好了。另外一个需要注意的地方是要设置init.ora里面的compatible参数为8.1.0或以上，否则创建时desc关键字将被忽略。位图索引位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。位图索引的格式如表26-1所示。表26-1位图索引的格式行值12345678910Male1000000011Female0111001100Null0000110000如果搜索wheregender=’Male’,要统计性别是”Male”的列行数的话，Oracle很快就能从位图中找到共3行即第1，9，10行是符合条件的；如果要搜索wheregender=’Male’orgender=’Female’的列的行数的话，也很容易从位图中找到共8行即1，2，3，4，7，8，9，10行是符合条件的。如果要搜索表的值的话，那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。
????
缩略显示
14:04浏览(482)评论(0)分类:数据库2008-11-27
常见OracleHINT的用法
关键字:oracle1./*+ALL_ROWS*/表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.例如:SELECT/*+ALL+_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';2./*+FIRST_ROWS*/表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.例如:SELECT/*+FIRST_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';3./*+CHOOSE*/表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;例如:SELECT/*+CHOOSE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';4./*+RULE*/表明对语句块选择基于规则的优化方法.例如:SELECT/*+RULE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';5./*+FULL(TABLE)*/表明对表选择全局扫描的方法.例如:SELECT/*+FULL(A)*/EMP_NO,EMP_NAMFROMBSEMPMSAWHEREEMP_NO='SCOTT';6./*+ROWID(TABLE)*/提示明确表明对指定表根据ROWID进行访问.例如:SELECT/*+ROWID(BSEMPMS)*/*FROMBSEMPMSWHEREROWID>='AAAAAAAAAAAAAA'ANDEMP_NO='SCOTT';7./*+CLUSTER(TABLE)*/提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.例如:SELECT/*+CLUSTER*/BSEMPMS.EMP_NO,DPT_NOFROMBSEMPMS,BSDPTMSWHEREDPT_NO='TEC304'ANDBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;8./*+INDEX(TABLEINDEX_NAME)*/表明对表选择索引的扫描方法.例如:SELECT/*+INDEX(BSEMPMSSEX_INDEX)USESEX_INDEXBECAUSETHEREAREFEWMALEBSEMPMS*/FROMBSEMPMSWHERESEX='M';9./*+INDEX_ASC(TABLEINDEX_NAME)*/表明对表选择索引升序的扫描方法.例如:SELECT/*+INDEX_ASC(BSEMPMSPK_BSEMPMS)*/FROMBSEMPMSWHEREDPT_NO='SCOTT';10./*+INDEX_COMBINE*/为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.例如:SELECT/*+INDEX_COMBINE(BSEMPMSSAL_BMIHIREDATE_BMI)*/*FROMBSEMPMSWHERESAL<5000000ANDHIREDATE11./*+INDEX_JOIN(TABLEINDEX_NAME)*/提示明确命令优化器使用索引作为访问路径.例如:SELECT/*+INDEX_JOIN(BSEMPMSSAL_HMIHIREDATE_BMI)*/SAL,HIREDATEFROMBSEMPMSWHERESAL<60000;
12./*+INDEX_DESC(TABLEINDEX_NAME)*/表明对表选择索引降序的扫描方法.例如:SELECT/*+INDEX_DESC(BSEMPMSPK_BSEMPMS)*/FROMBSEMPMSWHEREDPT_NO='SCOTT';13./*+INDEX_FFS(TABLEINDEX_NAME)*/对指定的表执行快速全索引扫描,而不是全表扫描的办法.例如:SELECT/*+INDEX_FFS(BSEMPMSIN_EMPNAM)*/*FROMBSEMPMSWHEREDPT_NO='TEC305';14./*+ADD_EQUALTABLEINDEX_NAM1,INDEX_NAM2,...*/提示明确进行执行规划的选择,将几个单列索引的扫描合起来.例如:SELECT/*+INDEX_FFS(BSEMPMSIN_DPTNO,IN_EMPNO,IN_SEX)*/*FROMBSEMPMSWHEREEMP_NO='SCOTT'ANDDPT_NO='TDC306';15./*+USE_CONCAT*/对查询中的WHERE后面的OR条件进行转换为UNIONALL的组合查询.例如:SELECT/*+USE_CONCAT*/*FROMBSEMPMSWHEREDPT_NO='TDC506'ANDSEX='M';16./*+NO_EXPAND*/对于WHERE后面的OR或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.例如:SELECT/*+NO_EXPAND*/*FROMBSEMPMSWHEREDPT_NO='TDC506'ANDSEX='M';17./*+NOWRITE*/禁止对查询块的查询重写操作.18./*+REWRITE*/可以将视图作为参数.19./*+MERGE(TABLE)*/能够对视图的各个查询进行相应的合并.例如:SELECT/*+MERGE(V)*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMSA(SELETDPT_NO,AVG(SAL)ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO)VWHEREA.DPT_NO=V.DPT_NOANDA.SAL>V.AVG_SAL;20./*+NO_MERGE(TABLE)*/对于有可合并的视图不再合并.例如:SELECT/*+NO_MERGE(V)*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMSA(SELECTDPT_NO,AVG(SAL)ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO)VWHEREA.DPT_NO=V.DPT_NOANDA.SAL>V.AVG_SAL;21./*+ORDERED*/根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.例如:
SELECT/*+ORDERED*/A.COL1,B.COL2,C.COL3FROMTABLE1A,TABLE2B,TABLE3CWHEREA.COL1=B.COL1ANDB.COL1=C.COL1;22./*+USE_NL(TABLE)*/将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.例如:SELECT/*+ORDEREDUSE_NL(BSEMPMS)*/BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAMFROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;23./*+USE_MERGE(TABLE)*/将指定的表与其他行源通过合并排序连接方式连接起来.例如:SELECT/*+USE_MERGE(BSEMPMS,BSDPTMS)*/*FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;24./*+USE_HASH(TABLE)*/将指定的表与其他行源通过哈希连接方式连接起来.例如:SELECT/*+USE_HASH(BSEMPMS,BSDPTMS)*/*FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;25./*+DRIVING_SITE(TABLE)*/强制与ORACLE所选择的位置不同的表进行查询执行.例如:SELECT/*+DRIVING_SITE(DEPT)*/*FROMBSEMPMS,DEPT@BSDPTMSWHEREBSEMPMS.DPT_NO=DEPT.DPT_NO;26./*+LEADING(TABLE)*/将指定的表作为连接次序中的首表.27./*+CACHE(TABLE)*/当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端例如:SELECT/*+FULL(BSEMPMS)CAHE(BSEMPMS)*/EMP_NAMFROMBSEMPMS;28./*+NOCACHE(TABLE)*/当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端例如:SELECT/*+FULL(BSEMPMS)NOCAHE(BSEMPMS)*/EMP_NAMFROMBSEMPMS;29./*+APPEND*/直接插入到表的最后,可以提高速度.insert/*+append*/intotest1select*fromtest4;30./*+NOAPPEND*/通过在插入语句生存期内停止并行模式来启动常规插入.insert/*+noappend*/intotest1select*fromtest4;
???
12:42浏览(231)评论(0)
?
缩略显示
分类:数据库2008-11-25
startwith...connectby用法简介
关键字:oracle通过STARTWITH...CONNECTBY...子句来实现SQL的层次查询.自从Oracle9i开始，可以通过SYS_CONNECT_BY_PATH函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。自从Oracle10g中，还有其他更多关于层次查询的新特性。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。在Oracle10g之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle就会报出一个错误提示：“ORA-01436:CONNECTBYloopinuserdata”。如果不删掉对父亲的引用就无法执行查询操作。而在Oracle10g中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE，如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。
Thestartwith..connectbyclausecanbeusedtoselectdatathathasahierarchicalrelationship(usuallysomesortofparent->child,boss->employeeorthing->parts).Itisalsobeingusedwhenansqlexecutionplanisexplained.
syntax:select...[startwithinitial-condition]connectby[nocycle]recurse-condition
levelWithlevelitispossibletoshowthelevelinthehierarchicalrelationofallthedata.
--oracle9isys_connect_by_pathWithsys_connect_by_pathitispossibletoshowtheentirepathfromthetopleveldowntothe'actual'child.
--oracle10gconnect_by_rootconnect_by_rootisanewoperatorthatcomeswithOracle10gandenhancestheabilitytoperformhierarchicalqueries.connect_by_is_leafconnect_by_isleafisanewoperatorthatcomeswithOracle10gandenhancestheabilitytoperform
hierarchicalqueries.connect_by_iscycleconnect_by_is_cycleisanewoperatorthatcomeswithOracle10gandenhancestheabilitytoperformhierarchicalqueries.--startwith...connectby...的处理机制Howmustastartwith...connectbyselectstatementbereadandinterpreted?IfOracleencounterssuchanSQLstatement,itproceedsasdescribedinthefollowingpseudecode.
forrecin(select*fromsome_table)loopifFULLFILLS_START_WITH_CONDITION(rec)thenRECURSE(rec,rec.child);endif;endloop;
procedureRECURSE(recinMATCHES_SELECT_STMT,new_parentINfield_type)isbeginAPPEND_RESULT_LIST(rec);forrec_recursein(select*fromsome_table)loopifFULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child,new_parent)thenRECURSE(rec_recurse,rec_recurse.child);endif;endloop;endprocedureRECURSE;
createdbyzhouwf07262006.
*******************************************************************************/
--创建测试表，增加测试数据
createtabletest(superidvarchar2(20),idvarchar2(20));
insertintotestvalues('0','1');insertintotestvalues('0','2');
insertintotestvalues('1','11');insertintotestvalues('1','12');
insertintotestvalues('2','21');insertintotestvalues('2','22');
insertintotestvalues('11','111');insertintotestvalues('11','112');
insertintotestvalues('12','121');insertintotestvalues('12','122');
insertintotestvalues('21','211');insertintotestvalues('21','212');
insertintotestvalues('22','221');insertintotestvalues('22','222');
commit;
--层次查询示例selectlevel||'层',lpad('',level*5)||ididfromteststartwithsuperid='0'connectbypriorid=superid;
selectlevel||'层',connect_by_isleaf,lpad('',level*5)||ididfromteststartwithsuperid='0'connectbypriorid=superid;
--给出两个以前在"数据库字符串分组相加之四"中的例子来理解startwith...connectby...--功能：实现按照superid分组,把id用";"连接起来--实现：以下两个例子都是通过构造2个伪列来实现connectby连接的。
/*------methodone------*/selectsuperid,ltrim(max(sys_connect_by_path(id,';')),';')from(selectsuperid,id,row_number()over(partitionbysuperidorderbysuperid)id1,row_number()over(orderbysuperid)+dense_rank()over(orderbysuperid)id2fromtest)startwithid1=1connectbypriorid2=id2-1groupbysuperidorderbysuperid;
/*------methodtwo------*/selectdistinctsuperid,ltrim(first_value(id)over(partitionbysuperidorderbyldesc),';')from(selectsuperid,levell,sys_connect_by_path(id,';')idfrom(selectsuperid,id,superid||rownumparent_rn,superid||to_char(rownum-1)rnfromtest)connectbypriorparent_rn=rn);
--下面的例子实现把一个整数的各个位上的数字相加,通过这个例子我们再次理解connectby.
createorreplacefunctionf_digit_add(innuminteger)returnnumberisoutnuminteger;beginifinnum<0thenreturn0;endif;selectsum(nm)intooutnumfrom(selectsubstr(innum,rownum,1)nmfromdualconnectbyrownum selectf_digit_add(123456)fromdual;
????
缩略显示
13:32浏览(1027)评论(0)分类:数据库2008-11-25
oracle常用函数
关键字:oracle1、add_months()用于从一个日期值增加或减少一些月份date_value:=add_months(date_value,number_of_months)例：SQL>selectadd_months(sysdate,12)"NextYear"fromdual;
NextYear---------13-11月-042、current_date()返回当前会放时区中的当前日期date_value:=current_dateSQL>columnsessiontimezonefora15SQL>selectsessiontimezone,current_datefromdual;3、current_timestamp()以timestampwithtimezone数据类型返回当前会放时区中的当前日期timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])SQL>columnsessiontimezonefora15SQL>columncurrent_timestampformata36SQL>selectsessiontimezone,current_timestampfromdual;
4、dbtimezone()返回时区varchar_value:=dbtimezoneSQL>selectdbtimezonefromdual;5、extract()找出日期或间隔值的字段值date_value:=extract(date_fieldfrom[datetime_value|interval_value])SQL>selectextract(monthfromsysdate)"ThisMonth"fromdual;
ThisMonth---------11
6、last_day()返回包含了日期参数的月份的最后一天的日期date_value:=last_day(date_value)SQL>selectlast_day(date'2000-02-01')"LeapYr?"fromdual;
LeapYr?---------29-2月-00
7、localtimestamp()返回会话中的日期和时间timestamp_value:=localtimestampSQL>columnlocaltimestampformata28SQL>selectlocaltimestampfromdual;
8、months_between()判断两个日期之间的月份数量number_value:=months_between(date_value,date_value)SQL>selectmonths_between(sysdate,date'1971-05-18')fromdual;
MONTHS_BETWEEN(SYSDATE,DATE'1971-05-18')---------------------------------------389.855143
9、next_day()给定一个日期值，返回由第二个参数指出的日子第一次出现在的日期值（应返回相应日子的名称字符串）
????
缩略显示
10:54浏览(96)评论(0)分类:数据库2008-11-19
oracle常用系统查询
关键字:oracle查索引DBA_INDEXES或ALL_INDEXES或USER_INDEXES系统统计信息
v$sysstat等待信息
v$session_wait
v$system_eventOracle标准的表空间使用情况查询：
SELECTd.status"Status",d.tablespace_name"Name",d.contents"Type",d.extent_management"ExtentManagement",TO_CHAR(NVL(a.bytes/1024/1024,0),'99,999,990.900')"Size(M)",TO_CHAR(NVL(a.bytes-NVL(f.bytes,0),0)/1024/1024,'99999999.999')||'/'||TO_CHAR(NVL(a.bytes/1024/1024,0),'99999999.999')"Used(M)",TO_CHAR(NVL((a.bytesNVL(f.bytes,0))/a.bytes*100,0),'990.00')"Used%"FROMsys.dba_tablespacesd,(selecttablespace_name,sum(bytes)bytesfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,sum(bytes)bytesfromdba_free_spacegroupbytablespace_name)fWHEREd.tablespace_name=a.tablespace_name(+)ANDd.tablespace_name=f.tablespace_name(+)ANDNOT(d.extent_managementlike'LOCAL'ANDd.contentslike'TEMPORARY')UNIONALLSELECTd.status"Status",d.tablespace_name"Name",d.contents"Type",d.extent_management"ExtentManagement",TO_CHAR(NVL(a.bytes/1024/1024,0),'99,999,990.900')"Size(M)",TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999')||'/'||TO_CHAR(NVL(a.bytes/1024/1024,0),'99999999.999')"Used(M)",TO_CHAR(NVL(t.bytes/a.bytes*100,0),'990.00')"Used%"FROMsys.dba_tablespacesd,(selecttablespace_name,sum(bytes)bytesfromdba_temp_filesgroupbytablespace_name)a,(selecttablespace_name,sum(bytes_cached)bytesfromv$temp_extent_poolgroupbytablespace_name)tWHEREd.tablespace_name=a.tablespace_name(+)ANDd.tablespace_name=t.tablespace_name(+)ANDd.extent_managementlike'LOCAL'ANDd.contentslike'TEMPORARY'
????
缩略显示
09:32浏览(128)评论(0)分类:数据库2008-11-04
使用智能优化器提高Oracle的性能极限
关键字:oracle【IT168技术文档】消耗在准备新的SQL语句的时间是OracleSQL语句执行时间的最重要的组成部分。但是通过理解Oracle内部产生执行计划的机制，你能够控制Oracle花费在评估连接顺序的时间数量，并且能在大体上提高查询性能。准备执行SQL语句当SQL语句进入Oracle的库缓存后，在该语句准备执行之前，将执行下列步骤：语法检查：检查SQL语句拼写是否正确和词序。语义分析：核实所有的与数据字典不一致的表和列的名字。轮廓存储检查：检查数据字典，以确定该SQL语句的轮廓是否已经存在。生成执行计划：使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。建立二进制代码：基于执行计划，Oracle生成二进制执行代码。
1)
2)
3)
4)
5)
一旦为执行准备好了SQL语句，以后的执行将很快发生，因为Oracle认可同一个SQL语句，并且重用那些语句的执行。然而，对于生成特殊的SQL语句，或嵌入了文字变量的SQL语句的系统，SQL执行计划的生成时间就很重要了，并且前一个执行计划通常不能够被重用。对那些连接了很多表的查询，Oracle需要花费大量的时间来检测连接这些表的适当顺序。评估表的连接顺序在SQL语句的准备过程中，花费最多的步骤是生成执行计划，特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时，它必须考虑到表之间所有可能的连接。例如：六个表的之间连接有720（6的阶乘，或6*5*4*3*2*1=720）种可能的连接线路。当一个查询中含有超过10个表的连接时，排列的问题将变得更为显著。对于15个表之间的连接，需要评估的可能查询排列将超过1万亿（准确的数字是1,307,674,368,000）种。使用optimizer_search_limit参数来设定限制通过使用optimizer_search_limit参数，你能够指定被优化器用来评估的最大的连接组合数量。使用这个参数，我们将能够防止优化器消耗不定数量的时间来评估所有可能的连接组合。如果在查询中表的数目小于optimizer_search_limit的值，优化器将检查所有可能的连接组合。例如：有五个表连接的查询将有120（5!=5*4*3*2*1=120）种可能的连接组合，因此如果optimizer_search_limit等于5（默认值），则优化器将评估所有的120种可能。optimizer_search_limit参数也控制着调用带星号的连接提示的阀值。当查询中的表的数目比optimizer_search_limit小时，带星号的提示将被优先考虑。另一个工具：参数optimizer_max_permutations
初始化参数optimizer_max_permutations定义了优化器所考虑组合数目的上限，且依赖于初始参数optimizer_search_limit。optimizer_max_permutations的默认值是80,000。参数optimizer_search_limit和optimizer_max_permutations一起来确定优化器所考虑的组合数目的上限：除非（表或组合数目）超过参数optimizer_search_limit或者optimizer_max_permutations设定的值，否则优化器将生成所有可能的连接组合。一旦优化器停止评估表的连接组合，它将选择成本最低的组合。使用ordered提示指定连接顺序你能够设定优化器所执行的评估数目的上限。但是即使采用有很高价值的排列评估，我们仍然拥有使优化器可以尽早地放弃复杂的查询的重要机会。回想一下含有15个连接查询的例子，它将有超过1万亿种的连接组合。如果优化器在评估了80,000个组合后停止，那么它才仅仅评估了0.000006%的可能组合，而且或许还没有为这个巨大的查询找到最佳的连接顺序。在OracleSQL中解决此问题的最好的方法是手工指定表的连接顺序。为了尽快创建最小的解决方案集，这里所遵循的规则是将表结合起来，通常优先使用限制最严格的WHERE子句来连接表。下面的代码是一个查询执行计划的例子，该例子在emp表的关联查询上强制执行了嵌套的循环连接。注意，我已经使用了ordered提示来直接最优化表的评估顺序，最终它们表现在WHERE子句上。
select/**//*+ordereduse_nl(bonus)parallel(e,4)*/
e.ename,
hiredate,
bm.
from
empe,
bonusb
where
e.ename=b.ename这个例子要求优化器按顺序连接在SQL语句的FROM子句中指定的表，在FROM子句中的第一个表指定了驱动表。ordered提示通常被用来与其它的提示联合起来来保证采用正确的顺序连接多个表。它的用途更多的是在扭转连接表数在四个以上的数据仓库的查询方面。另外一个例子，下面的查询使用ordered提示按照指定的顺序来连接表：emp、dept、sal，最后是bonus。
我通过指定emp到dept使用哈希连接和sal到bonus使用嵌套循环连接，来进一步精炼执行计划。
select/**//*+ordereduse_hash(emp,dept)use_nl(sal,bonus)*/
from
emp,
dept,
sal,
bonus
where...实践建议实际上，更有效率的做法是在产品环境中减小optimizer_max_permutations参数的大小，并且总是使用稳定的优化计划或存储轮廓来防止出现耗时的含有大量连接的查询。一旦找到最佳的连接顺序，您就可以通过增加ordered提示到当前的查询中，并保存它的存储轮廓，来为这些表手工指定连接顺序，从而使其持久化。当你打算使用优化器来稳定计划，则可以照下面的方法使执行计划持久化，临时将optimizer_search_limit设置为查询中的表的数目，从而允许优化器考虑所有可能的连接顺序。然后，通过重新编排WHERE子句中表的名字，并使用ordered提示，与存储轮廓一起使变更持久化，来调整查询。在查询中包含四个以上的表时，ordered提示和存储轮廓将排除耗时的评估SQL连接顺序解析的任务，从而提高查询的速度。一旦检测到最佳的连接顺序，我们就可以使用ordered提示来重载optimizer_search_limit和optimizer_max_permutations参数。ordered提示要求表按照它们出现在FROM子句中的顺序进行连接，所以优化器没有加入描述。作为一个Oracle专业人员，你应该知道在SQL语句第一次进入库缓存时可能存在重大的启动延迟。但是聪明的OracleDBA和开发人员能够改变表的搜索限制参数或者使用ordered提示来手工指定表的连接顺序，从而显著地减少优化和执行新查询所需的时间。
????
缩略显示
22:34浏览(139)评论(0)分类:数据库2008-11-04
SQL语句性能调整原则
关键字:sql
【IT168技术文档】一、问题的提出在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。二、SQL语句编写注意问题下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。1.ISNULL与ISNOTNULL不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的。2.联接列对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME)，现在要查询一个叫比尔.克林顿(BillCliton)的职工。下面是一个采用联接查询的SQL语句，
select*fromemployss
where
first_name||''||last_name='BeillCliton';上面这条语句完全可以查询出是否有BillCliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。
Select*fromemployee
where
first_name='Beill'andlast_name='Cliton';遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着BillCliton这个员工的，对于这种情况我们又如何避免全程遍历，使用索引呢?可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本:
select*fromemployee
where
first_name=SUBSTR('&;&;name'，1，INSTR('&;&;name'，'')-1)
and
last_name=SUBSTR('&;&;name'，INSTR('&;&;name’，'')+1)3.带通配符(%)的like语句同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:
select*fromemployeewherelast_namelike'%cliton%';这里由于通配符(%)在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用:
select*fromemployeewherelast_namelike'c%';4.Orderby语句ORDERBY语句决定了Oracle如何将返回的查询结果排序。Orderby语句对要排序的列没有什么特别的限制，也可以将函数加入列中(象联接或者附加等)。任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度。仔细检查orderby语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写orderby语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在orderby子句中使用表达式。
5.NOT我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:
...wherenot(status='VALID')如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于(<>)运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例:
...wherestatus<>'INVALID';再看下面这个例子:
select*fromemployeewheresalary<>3000;对这个查询，可以改写为不使用NOT:
select*fromemployeewheresalary<3000orsalary>3000;虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。6.IN和EXISTS有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。第一种格式是使用IN操作符:
...wherecolumnin(select*from...where...);第二种格式是使用EXIST操作符:
...whereexists(select'X'from...where...);我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。第二种格式中，子查询以‘select'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。
通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。同时应尽可能使用NOTEXISTS来代替NOTIN，尽管二者都使用了NOT(不能使用索引而降低速度)，NOTEXISTS要比NOTIN查询效率更高。
????
缩略显示
22:33浏览(158)评论(0)分类:数据库2008-11-04
Oracle性能调优原则
关键字:oracle【IT168技术文档】任何事情都有它的源头，要解决问题，也得从源头开始，影响ORACLE性能的源头非常多，主要包括如下方面：数据库的硬件配置:CPU、内存、网络条件。1.CPU:在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志，并且ORACLE是一个提供并行能力的数据库系统，CPU方面的要求就更高了，在如果运行队列数目超过了CPU处理的数目，性能就会下降，我们要解决的问题就是要适当增加CPU的数量了，当然我们还可以将需要许多资源的进程KILL掉;2.内存:衡量机器性能的另外一个指标就是内存的多少了，在ORACLE中内存和我们在建数据库中的交换区进行数据的交换，读数据时，磁盘I/O必须等待物理I/O操作完成，在出现ORACLE的内存瓶颈时，我们第一个要考虑的是增加内存，由于I/O的响应时间是影响ORACLE性能的主要参数，我将在这方面进行详细的讲解3.网络条件:NET*SQL负责数据在网络上的来往，大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响，还有交换机、集线器等等网络设备的性能对网络的影响很明显，建议在任何网络中不要试图用3个集线器来将网段互联。OS参数的设置下表给出了OS的参数设置及说明，DBA可以根据实际需要对这些参数进行设置内核参数名说明
bufpages对buffer空间不按静态分配，采用动态分配，bufpages值随nbuf一起对buffer空间进行动态分配。使
create_fastlinks对HFS文件系统允许快速符号链接
dbc_max_pct加大最大动态buffer空间所占物理内存的百分比，以满足应用系统的读写命中率的需要。
dbc_min_pct设置最小动态buffer空间所占物理内存的百分比
desfree提高开始交换操作的最低空闲内存下限，保障系统的稳定性，防止出现不可预见的系统崩溃(Crash)。
fs_async允许进行磁盘异步操作，提高CPU和磁盘的利用率
lotsfree提高系统解除换页操作的空闲内存的上限值，保证应用程序有足够的可用内存空间。
maxdsiz针对系统数据量大的特点，加大最大数据段的大小，保证应用的需要。(32位)
maxdsiz_64bit
maximumprocessdatasegmentsizefor64_bit
Maxssiz加大最大堆栈段的大小。(32_bit)
maxssiz_64bit加大最大堆栈段的大小。(64_bit)
Maxtsiz提高最大代码段大小，满足应用要求
maxtsiz_64bit原值过大，应调小
Minfree提高停止交换操作的自由内存的上限
Shmem允许进行内存共享，以提高内存的利用率
Shmmax设置最大共享内存段的大小，完全满足目前的需要
Timeslice由于系统的瓶颈主要反映在磁盘I/O上，因此降低时间片的大小，一方面可避免因磁盘I/O不畅造
成CPU的等待，从而提高了CPU的综合利用率。另一方面减少了进程的阻塞量。
unlockable_mem提高了不可锁内存的大小，使可用于换页和交换的内存空间扩大,用以满足系统对内存管理的要求。用户SQL质量以上讲的都是硬件方面的东西，在条件有限的条件下，我们可以调整应用程序的SQL质量:1.不要进行全表扫描(FullTableScan):全表扫描导致大量的I/O2.尽量建好和使用好索引:建索引也是有讲究的，在建索引时，也不是索引越多越好，当一个表的索引达到4个以上时，ORACLE的性能可能还是改善不了，因为OLTP系统每表超过5个索引即会降低性能，而且在一个sql中，Oracle从不能使用超过5个索引;当我们用到GROUPBY和ORDERBY时,ORACLE就会自动对数据进行排序,而ORACLE在INIT.ORA中决定了sort_area_size区的大小,当排序不能在我们给定的排序区完成时,ORACLE就会在磁盘中进行排序,也就是我们讲的临时表空间中排序,过多的磁盘排序将会令freebufferwaits的值变高,而这个区间并不只是用于排序的,对于开发人员我提出如下忠告:1)、select,update,delete语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高.
2)、索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate表,truncate命令删除表中所有记录,也删除索引
碎片.
3)、在使用索引时一定要按索引对应字段的顺序进行引用。4)、用(+)比用NOTIN更有效率。降低ORACLE的竞争:先讲几个ORACLE的几个参数，这几个参数关系到ORACLE的竞争:1)、freelists和freelist组:他们负责ORACLE的处理表和索引的空间管理;2)、pctfree及pctused:该参数决定了freelists和freelist组的行为，pctfree和pctused参数的唯一目的就是为了控制块如何在freelists中进出设置好pctfree及pctused对块在freelists的移走和读取很重要。其他参数的设置1)、包括SGA区(系统全局区):系统全局区(SGA)是一个分配给Oracle的包含一个Oracle实例的数据库的控制信息内存段。主要包括数据库高速缓存(thedatabasebuffercache)，重演日志缓存(theredologbuffer)，共享池(thesharedpool)，数据字典缓存(thedatadictionarycache)以及其它各方面的信息2)、db_block_buffers(数据高速缓冲区)访问过的数据都放在这一片内存区域，该参数越大，Oracle在内存中找到相同数据的可能性就越大，也即加快了查询速度。3)、share_pool_size(SQL共享缓冲池):该参数是库高速缓存和数据字典的高速缓存。
4)、Log_buffer(重演日志缓冲区)
5)、sort_area_size(排序区)
6)、processes(同时连接的进程数)7)、db_block_size(数据库块大小):Oracle默认块为2KB，太小了，因为如果我们有一个8KB的数据，则2KB块的数据库要读4次盘，才能读完，而8KB块的数据库只要1次就读完了，大大减少了I/O操作。数据库安装完成后，就不能再改变db_block_size的值了，只能重新建立数据库并且建库时，要选择手工
安装数据库。
8)、open_links(同时打开的链接数)
9)、dml_locks
10)、open_cursors(打开光标数)
11)、dbwr_io_slaves(后台写进程
????
缩略显示
22:25浏览(150)评论(0)分类:数据库2008-11-02
新增oracle监听器后PL/SQL显示没有监听器
关键字:pl/sqloracle的监听器启动不了，于是删除重新建了一个监听器。数据库是oracle10g。结果使用oracle的client可以连接，但是之前连接ok的PL/SQL却除了问题，database中显示的并不是注册了的信息，而是以前注册的数据库名称。登录时，报错为没有监听器。于是卸载PL/SQL，清空目录，清空注册表相关信息，然后重装。结果仍然报错。感觉是PL/SQL软件设置的问题，终于在修改了tools----preferences------oracleHome，改为Client的设置后，能够正常试用了。
????
缩略显示
23:10浏览(702)评论(0)分类:数据库2008-11-02
oracleListener
关键字:oracleOracle数据库监听配置
近段时间很多网友提出监听配置相关问题，客户终端(Client)无法连接服务器端(Server)。本文现对监听配置作一简单介绍，并提出一些客户终端无法连接服务器端的解决思路，愿对广大网友与读者有一些帮助。一、监听器(LISTENER)监听器是Oracle基于服务器端的一种网络服务，主要用于监听客户端向数据库服务器端提出的连接请求。既然是基于服务器端的服务，那么它也只存在于数据库服务器端，进行监听器的设置也是在数据库服务器端完成的。二、本地服务名(Tnsname)Oracle客户端与服务器端的连接是通过客户端发出连接请求，由服务器端监听器对客户端连接请求进行合
法检查，如果连接请求有效，则进行连接，否则拒绝该连接。本地服务名是Oracle客户端网络配置的一种，另外还有Oracle名字服务器(OracleNamesServer)等。Oracle常用的客户端配置就是采用的本地服务名，本文中介绍的也主要是基于本地服务名的配置。三、Oracle网络连接配置方法配置Oracle服务器端与客户端都可以在其自带的图形化Oracle网络管理器(OracleNetManager)里完成（强烈建议在这个图形化的工具下完成Oracle服务端或客户端的配置）。在Windows下，点击“开始/程序/Oracle-OraHome92/ConfigurationandMigrationTools/NetManager”启动Oracle网络管理器工具，在Linux/Unix下，利用netmgr命令来启动图形化Oracle网络管理器，如：$netmgrWindows下启动NetManager图形窗口如下图示：图(一)1、Oracle监听器配置(LISTENER)如图(一)示，选中树形目录中监听程序项，再点击左上侧“+”按钮添加监听程序，点击监听程序目录，默认新加的监听器名称是LISTENER(该名称也可以由任意合法字符命名)。选中该名称，选中窗口右侧栏下拉选项中的“监听位置”，点击添加地址按钮。在出现的网络地址栏的协议下拉选项中选中“TCP/IP”，主机文本框中输入主机名称或IP地址(如果主机即用作服务端也作为客户端，输入两项之一均有效；如果主机作为服务端并需要通过网络连接，建议输入IP地址)，端口文本框中输入数字端口，默认是1521，也可以自定义任意有效数字端口。配置好的监听位置如下图示：
图(二)选中窗口右侧栏下拉选项中的“数据库服务”，点击添加数据库按钮。在出现的数据库栏中输入全局数据库名，如myoracle。注意这里的全局数据库名与数据库SID有所区别，全局数据库名实际通过域名来控制在同一网段内数据库全局命名的唯一性，就如Windows下的域名控制器，如这里可以输入myoracle.192.168.1.5。Oracle主目录可以不填写，输入SID，如myoracle。完整的数据库服务配置如下图示：
图(三)保存以上配置，默认即可在Oracle安装目录下找到监听配置文件(Windows下如D:\oracle\ora92\network\admin\listener.ora，Linux/Unix下$ORACLE_HOME/network/admin/listerer.ora)。至此，Oracle服务端监听器配置已经完成。2、本地服务名配置(Tnsnames)本地服务名是基于Oracle客户端的网络配置，所以，如果客户端需要连接数据库服务器进行操作，则需要配置该客户端，其依附对象可以是任意一台欲连接数据库服务器进行操作的PC机，也可以是数据库服务器自身。如前面所介绍，可以利用Oracle自带的图形化管理工具NetManager来完成Oracle客户端的配置。选中如图(一)中的服务命名，再点击左上侧“+”按钮，弹出如下图示对话框：
图(四)输入Net服务名，如myoracle，点击下一步，进入下图示对话框：
图(五)选中TCP/IP(Internet协议)，点击下一步，如下图示：
图(六)输入主机名与端口号。注意这里的主机名与端口号必须与数据库服务器端监听器配置的主机名和端口号相同。点击下一步，如下图示：
图(七)选中(Oracle8i或更高版本)服务名，输入服务名。这里的服务名实际上就是数据库服务器端监听器配置中的全局数据库名，前者与后者必须相同。连接类型通常选专用服务器，这要视数据库服务器的配置而定，如果配置的共享数据库服务器，这里的连接类型就要选共享服务器，否则建议选专用服务器(关于专用服务器的介绍请参阅相关文档)。配置好后点击下一步，如下图示：
图(八)如果数据库服务器端相关服务启动了，可以点击测试按钮进行连接测试。Oracle默认是通过scott/tiger用户进行测试连接，由于scott用户是Oracle自带的示例用户，对于正式的业务数据库或专业测试数据库可能没有配置这个用户，所以需要更改成有效的用户登录才可能测试成功。如果这里测试连接不成功，也不要紧，先点完成按钮结束配置。回到Oracle网络管理器(OracleNetManager)主窗口，保存配置，默认即可在Oracle安装目录下找到本地服务名配置文件(Windows下如D:\oracle\ora92\network\admin\tnsnames.ora，Linux/Unix下$ORACLE_HOME/network/admin/tnsnames.ora)。配置完成的本地服务名如下图示：
图(九)树形目录下的服务命名可以通过编辑菜单里的重命名菜单更改成任意合法字符组成的服务名称，注意服务名称前不能有空格字符，否则可能无法连接数据库服务器。3、连接数据库服务器(1)启动服务器端监听器与数据库服务Linux/Unix下，启动监听器：
$lsnrctlstart关闭监听器：$lsnrctlstop查看监听状态：$lsnrctlstatus启动数据库：$sqlplus/nologSQL>connsys@myoracleassysdba或SQL>conn/assysdbaSQL>startupWindows下，启动监听器：C:\lsnrctlstart启动Oracle实例服务：C:\oradim–startup–sidmyoracle关闭Oracle实例服务：C:\oradim–shutdown–sidmyoracle以上服务必须同时启动，客户端才能连接数据库。由于默认配置的监听器名称是Listener，上述命令可以正常启动监听器，如果监听器名称是其它名称，如aListener，则需要用下列方式才能启动：Linux/Unix下：$lsnrctlstartaListenerWindows下：C:\lsnrctlstartaListener测试连接数据库服务器--这里的myoracle是前面配置的客户端本地服务名
(2)
测试的方法多种多样，可以在上面配置本地服务名时进行测试，也可以是第三方客户端工具，如PL/SQLDeveloper，最方便的是用Oracle自带的sqlplus工具，以下利用sqlplus进行测试：C:\sqlplus/nologSQL>connzgh@myoracle已连接。四、客户端连接服务器端常见问题排除方法要排除客户端与服务器端的连接问题，首先检查客户端配置是否正确(客户端配置必须与数据库服务器端监听配置一致)，再根据错误提示解决。下面列出几种常见的连接问题：1、ORA-12541:TNS:没有监听器显而易见，服务器端的监听器没有启动，另外检查客户端IP地址或端口填写是否正确。启动监听器：$lsnrctlstart或C:\lsnrctlstart2、ORA-12500:TNS:监听程序无法启动专用服务器进程对于Windows而言，没有启动Oracle实例服务。启动实例服务：C:\oradim–startup-sidmyoracle3、ORA-12535:TNS:操作超时出现这个问题的原因很多，但主要跟网络有关。解决这个问题，首先检查客户端与服务端的网络是否畅通，
如果网络连通，则检查两端的防火墙是否阻挡了连接。4、ORA-12154:TNS:无法处理服务名检查输入的服务名与配置的服务名是否一致。另外注意生成的本地服务名文件(Windows下如D:\oracle\ora92\network\admin\tnsnames.ora，Linux/Unix下$ORACLE_HOME/network/admin/tnsnames.ora)里每项服务的首行服务名称前不能有空格。5、ORA-12514:TNS:监听进程不能解析在连接描述符中给出的SERVICE_NAME打开NetManager，选中服务名称，检查服务标识栏里的服务名输入是否正确。该服务名必须与服务器端监听器配置的全局数据库名一致。6、Windows下启动监听服务提示找不到路径用命令或在服务窗口中启动监听提示找不到路径，或监听服务启动异常。打开注册表，进入HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/OracleOraHome92TNSListener项，查看ImagePath字符串项是否存在，如果没有，设定值为D:\oracle\ora92\BIN\TNSLSNR，不同的安装路径设定值做相应的更改。这种方法同样适用于Oracle实例服务，同上，找到如同HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/OracleServiceMYORACLE项，查看ImagePath字符串项是否存在，如果没有，则新建，设定值为d:\oracle\ora92\binORACLE.EXEMYORACLE。以上是Oracle客户端连接服务器端常见的一些问题，当然不能囊括所有的连接异常。解决问题的关键在于方法与思路，而不是每种问题都有固定的答案。
?????
缩略显示
查看图片附件22:20浏览(1113)评论(4)分类:数据库
2008-09-05
oraclesql优化
关键字:oracle基本的Sql编写注意事项尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。不用NOTIN操作符，可以用NOTEXISTS或者外连接+替代。Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOTEXISTS比NOTIN效率稍高。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<”or“>”代替。Where子句中出现ISNULL或者ISNOTNULL时，Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOTNULL。这样就可以用其他操作来取代判断NULL的操作。当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。
对数据类型不同的列进行比较时，会使索引失效。用“>=”替代“>”。UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNIONALL。Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。（只在采用RBO优化时有效，下文详述）OrderBy语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在OrderBy语句中使用表达式。不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。多利用内部函数提高Sql效率。当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。需要注意的是，随着Oracle的升级，查询优化器会自动对Sql语句进行优化，某些限制可能在新版本的Oracle下不再是问题。尤其是采用CBO（Cost-BasedOptimization，基于代价的优化方式）时。我们可以总结一下可能引起全表扫描的操作：在索引列上使用NOT或者“<>”;对索引列使用函数或者计算；NOTIN操作；通配符位于查询字符串的第一个字符；ISNULL或者ISNOTNULL；多列索引，但它的第一个列并没有被Where子句引用；Oracle优化器Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具。Oracle的优化器有两种优化方式：基于规则的（RBO）和基于代价的（CBO）。RBO：优化器遵循Oracle内部预定的规则。CBO：依据语句执行的代价，主要指对CPU和内存的占用。优化器在判断是否使用CBO时，要参照表和索引的统计信息。统计信息要在对表做analyze后才会有。Oracle8及以后版本，推荐用CBO方式。Oracle优化器的优化模式主要有四种：
Rule：基于规则；Choose：默认模式。根据表或索引的统计信息，如果有统计信息，则使用CBO方式；如果没有统计信息，相应列有索引，则使用RBO方式。Firstrows：与Choose类似。不同的是如果表有统计信息，它将以最快的方式返回查询的前几行，以获得最佳响应时间。Allrows：即完全基于Cost的模式。当一个表有统计信息时，以最快方式返回表所有行，以获得最大吞吐量。没有统计信息则使用RBO方式。设定优化模式的方式
Instance级别：在init.ora文件中设定OPTIMIZER_MODE；Session级别：通过SQL>ALTERSESSIONSETOPTIMIZER_MODE=;来设定。语句级别：通过SQL>SELECT/*+ALL+_ROWS*/……;来设定。可用的HINT包括/*+ALL_ROWS*/、/*+FIRST_ROWS*/、/*+CHOOSE*/、/*+RULE*/等。要注意的是，如果表有统计信息，则可能造成语句不走索引的结果。可以用SQL>ANALYZETABLEtable_nameDELETESTATISTICS;删除索引。对列和索引更新统计信息的SQL：SQL>ANALYZETABLEtable_nameCOMPUTESTATISTICS;SQL>ANALYZEINDEXindex_nameESTIMATESTATISTICS;
????
缩略显示
10:28浏览(249)评论(0)分类:数据库2007-06-18
转：sqlserver常用函数
关键字:server
SQL常用字符串函数
一、字符转换函数1、ASCII()返回字符表达式最左端字符的ASCII码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。2、CHAR()将ASCII码转换为字符。如果没有输入0~255之间的ASCII码值，CHAR（）返回NULL。3、LOWER()和UPPER()LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。4、STR()把数值型数据转换为字符型数据。STR([，length[，]])length指定返回的字符串的长度，decimal指定返回的小数位数。如果没有指定长度，缺省的length值为10，decimal缺省值为0。当length或者decimal为负值时，返回NULL；当length小于小数点左边（包括符号位）的位数时，返回length个*；先服从length，再取decimal；当返回的字符串位数小于length，左边补足空格。二、去空格函数1、LTRIM()把字符串头部的空格去掉。2、RTRIM()把字符串尾部的空格去掉。三、取子串函数1、left()
LEFT(，)返回character_expression左起integer_expression个字符。
2、RIGHT()RIGHT(，)返回character_expression右起integer_expression个字符。
3、SUBSTRING()SUBSTRING(，，length)返回从字符串左边第starting_position个字符起length个字符的部分。四、字符串比较函数1、CHARINDEX()返回字符串中某个指定的子串出现的开始位置。CHARINDEX(<’substring_expression’>，)其中substring_expression是所要查找的字符表达式，expression可为字符串也可为列名表达式。如果没有发现子串，则返回0值。此函数不能用于TEXT和IMAGE数据类型。2、PATINDEX()返回字符串中某个指定的子串出现的开始位置。PATINDEX(<’%substring_expression%’>，)其中子串表达式前后必须有百分号“%”否则返回值为0。与CHARINDEX函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、VARCHAR和TEXT数据类型。五、字符串操作函数1、QUOTENAME()返回被特定字符括起来的字符串。QUOTENAME(<’character_expression’>[，quote_character])其中quote_character标明括字符串所用的字符，缺省值为“[]”。2、REPLICATE()返回一个重复character_expression指定次数的字符串。REPLICATE(character_expressioninteger_expression)如果integer_expression值为负值，则返回NULL。
3、REVERSE()将指定的字符串的字符排列顺序颠倒。REVERSE()其中character_expression可以是字符串、常数或一个列的值。
4、REPLACE()返回被替换了指定子串的字符串。REPLACE(，，)用string_expression3替换在string_expression1中的子串string_expression2。
4、SPACE()返回一个有指定长度的空白字符串。SPACE()如果integer_expression值为负值，则返回NULL。
5、STUFF()用另一子串替换字符串指定位置、长度的子串。STUFF(，，，)如果起始位置为负或长度值为负，或者起始位置大于character_expression1的长度，则返回NULL值。如果length长度大于character_expression1中start_position以右的长度，则character_expression1只保留首字符。六、数据类型转换函数1、CAST()CAST(AS[length])
2、CONVERT()CONVERT([length]，[，style])1）data_type为SQLServer系统定义的数据类型，用户自定义的数据类型不能在此使用。2）length用于指定数据的长度，缺省值为30。3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。9）用CONVERT（）函数的style选项能以不同的格式显示日期和时间。style是将DATATIME和SMALLDATETIME数据转换为字符串时所选用的由SQLServer系统提供的转换样式编号，不同的样式编号有不同的输出格式。七、日期函数1、day(date_expression)返回date_expression中的日期值
2、month(date_expression)返回date_expression中的月份值
3、year(date_expression)返回date_expression中的年份值
4、DATEADD()DATEADD(，，)返回指定日期date加上指定的额外日期间隔number产生的新日期。
5、DATEDIFF()DATEDIFF(，，)返回两个指定日期在datepart方面的不同之处，即date2超过date1的差距值，其结果值是一个带有正负号的整数值。
6、DATENAME()DATENAME(，)以字符串的形式返回日期的指定部分此部分。由datepart来指定。
7、DATEPART()DATEPART(，)以整数值的形式返回日期的指定部分。此部分由datepart来指定。DATEPART(dd，date)等同于DAY(date)DATEPART(mm，date)等同于MONTH(date)DATEPART(yy，date)等同于YEAR(date)
8、GETDATE()以DATETIME的缺省格式返回系统当前的日期和时间
????
缩略显示
22:48浏览(1090)评论(0)分类:数据库2007-06-18
转sqlserver字段类型
关键字:sqlserver
SQLSERVER中字段类型及说明
格式说明:数据类型说明同义Bit1位，值为0或1IntInteger4字节，值为-2^31~2^31-1Smallint2字节，值为-2^15~2^15-1Tinyint1字节，值为0~255Decimal(p,s)数字数据，固定精度为P，宽度为S
NumericMoney8字节，存放货币类型，值为-2^63~2^63-1Smallmoney4字节，存放货币类型，值为-214748.3648~+214748.3647近似数值数据类型Float(n)N在1~24之间，4字节，7位精度N=1~7为realN在25~53之间，8字节，15位精度=8~15为floatDatetime8字节，描述某天的日期和时刻，值的精确度为1/300秒Smalldatetime4字节，描述某天的日期和时刻，精度为分钟Cursor对游标的引用Timestamp8字节，存放在数据库内唯一的数据Uniqueidentifier16字节，存放全局唯一标识(GUID)Char(n)非unicode字符串的固定长度，n=1~8000Character(n)Varchar(n)可变长度，非unicode字符串n=1~8000Charvarying(n)Text服务器代码页中可变长度非unicode数据。最大长度为231-1个字符Nchar固定长度unicode字符串n=1~4000Nationalcharacter(n),Nationalchar(n)Nvarchar固定长度unicode字符串n=1~4000Nationalcharactervarying(n)Ntext可变长度unicode数据，最大长度为230-1个字符NationaltextBinary(n)固定长度二进制数据，n在1~8000之间，存储空间为n+4字节Varbinary(n)可变长度二进制数据，n=1~8000Binaryvarying(n)Tmage
可变长度二进制数据，大小为0~231-1注意:1)对于数值型数据类型，宽度(scale)是指存储在小数点后的数字位数，而精度(precision)是指能存储的包含小数点在内的所有数字位数。2)money和smallmoney的存储宽度为4。3)时间戳列值在每一行更新时系统自动更新，时间戳列不能是关键字或关键字的一部分。4)唯一标识数据类型不能使用算术操作符(如+、-等)，这种数据类型只能使用相等比较操作。Unicode是所有字符集的一致存储数据的标准。它要使用两倍于非Unicode数据存储的存储空间。
