Oracle编程建议.docx
《Oracle编程建议.docx》由会员分享,可在线阅读,更多相关《Oracle编程建议.docx(22页珍藏版)》请在冰豆网上搜索。
![Oracle编程建议.docx](https://file1.bdocx.com/fileroot1/2023-2/3/bc5a5fa3-d87d-4726-aa55-0b57fe5c0443/bc5a5fa3-d87d-4726-aa55-0b57fe5c04431.gif)
Oracle编程建议
绑定变量
在Oracle数据库管理系统中,对于一个提交的SQL语句,有两种可选的解析过程:
硬解析和软解析。
当我们提交了一个SQL语句后,Oracle会在librarycache中查询是否存在完全相同的语句。
如果存在相同的语句,则执行软解析,使用已有的解析树和执行计划。
如果不存在相同的语句,则执行硬解析,需要对语句进行解析,创建解析树,生成执行计划。
硬解析不仅耗费大量的CPU资源,而且会占用重要的闩锁(latch,为了顺序访问以及修改一些内存区域而设置的,这些内存区域是不能被同时修改。
)资源。
绑定变量是用于替代SQL语句中的常量的替代变量。
唯一使得Oracle能够重复利用执行计划的方法就是采用绑定变量。
绑定变量能够使得每次提交的SQL语句都完全一样。
值得注意的是,Oracle会自动将过程、函数、包中语句使用的变量作为绑定变量处理,我们需要特别关注C、JAVA、PHP等语言编写的外部应用,以及动态SQL语句。
COPY命令的相关参数设置
ARRAYSIZE参数
该参数用于设置SQL*PLUS一次从数据库获取的行数,默认值为15,有效值为1至5000。
较大的值可提高查询和子查询的性能,可获取更多的行,同时也需要更多的内存。
当超过1000时,其效果不大。
COPYCOMMIT参数
该参数控制COPY命令提交对数据库修改的批数。
即每次拷贝n批后,将提交到目标数据库。
可用ARRAYSIZE参数设置一批的大小。
COPYCOMMIT参数的默认值为0,有效值为0到5000。
如果置COPYCOMMIT为0,则仅在COPY操作结束时执行一次提交。
TRUNCATETABLE
DROPSTORAGE
数据部分所使用的extent空间会被释放(释放回收到minextents个extent,NEXT_EXTENT设置MINEXTENTS之后的EXTENT),释放出来的空间可以供其它segment使用。
表的index部分会数据删除,extent部分也被释放,剩下第一个extent。
会将HWM(高水平线)重新设置到第一个Block的位置(HWM会改变)。
REUSESTORAGE
数据部分所在的extent空间不会被回收,仅仅数据会被删除,数据删除之后的freespace空间只能供本表使用,不可以供其它segment使用。
index部分会数据删除,但是保留extent部分。
会将HWM重新设置到第一个Block的位置(HWM会改变)。
当使用DROPSTORAGE时将缩短表和表索引,并重新设置NEXT参数。
使用REUSESTORAGE时不会缩短表或者调整NEXT参数,可以减少对表及数据字典的锁定时间。
应当注意的是DROPSTORAGE是TRUNCATETABLA语句的默认选项,当我们要整理表的碎片时,应使用REUSESTORAGE选项。
批量操作
批量绑定(Bulkbinds)通过减少PL/SQL和SQL引擎之间的上下文切换(contextswitches)提高性能。
批量绑定(Bulkbinds)包括:
✧Inputcollections:
使用usetheFORALL语句,用来改善DML(INSERT、UPDATE和DELETE)操作的性能
✧Outputcollections:
使用BULKCOLLECT子句,一般用来提高查询(SELECT)操作的性能
BULKCOLLECT子句
用于批量取得数据,适用于selectinto、fetchinto,及DML语句的returninginto返回子句。
批量查询BULKCOLLECT
使用bulkcollect可以将查询结果一次性地加载到collections中。
FORALL语句
✧indexINcollection.lower_bound..collection.upper_bound
✧indexININDICESOFcollection.lower_bound..collection.upper_bound
✧indexinvaluesofcollection
需要注意的是,FORALL语句中不能使用记录类型的集合变量
%TYPE
在很多情况下,PL/SQL变量是用来存储数据库表中的数据。
在这种情况下,变量应该拥有与表列相同的类型。
此时,使用“%TYPE”属性而不是将变量类型硬性编码,可以使PL/SQL代码更加灵活,避免数据库更新对PL/SQL代码的影响。
其他
WHERE子句中变量、常量的数据类型要与表列的定义保持一致,这样才能确保正确使用索引。
WHERE子句中使用的表列上应避免使用函数,确保正确使用索引。
除非基于该表列建立了函数索引。
处理较大数据的应用应尽量并行。
大数据量表关联查询操作尽量拆分为一系列基于单表的查询语句
基于本地索引(分区)查询分区表时,where条件中必须包含分区键
示例
使用分区索引查询时WHERE条件中必须包含分区项
SELECTCOUNT(a.user_id)
FROMbb_device_rent_info_ta,bb_service_relation_tb,bb_customer_info_tc
WHEREa.service_id=:
1
ANDa.service_kind=:
2
ANDa.city_code=:
3
ANDa.user_id=b.user_id
ANDb.customer_id=c.customer_id
使用分区索引查询而WHERE条件中不包含分区项时,Oracle将扫描分区表的所有分区,查询符合条件的记录。
SELECTCOUNT(a.user_id)
FROMbb_device_rent_info_ta,bb_service_relation_tb,bb_customer_info_tc
WHEREa.service_id=:
1
ANDa.service_kind=:
2
ANDa.city_code=:
3
ANDa.user_id=b.user_id
andb.city_code=:
3
ANDb.customer_id=c.customer_id
andc.city_code=:
3
WHERE子句中变量、常量的数据类型要与表列的定义保持一致
SELECTCOUNT
(1)
FROM(SELECT1
FROMbb_batch_accept_record_ta,bb_batch_accept_info_tb
WHEREa.batch_reg_no=b.batch_reg_no
AND1=1
ANDb.city_code='187'
ANDb.batch_reg_no=114559424
UNIONALL
SELECT1
FROMbb_batch_accept_record_his_ta,bb_batch_accept_info_his_tb
WHEREa.batch_reg_no=b.batch_reg_no
AND1=1
ANDb.city_code='187'
ANDb.batch_reg_no=114559424
UNIONALL
SELECT1
FROMbb_batch_accept_record_error_ta,bb_batch_accept_info_tb
WHEREa.batch_reg_no=b.batch_reg_no
AND1=1
ANDb.city_code='187'
ANDb.batch_reg_no=114559424
UNIONALL
SELECT1
FROMbb_batch_accept_record_error_ta,bb_batch_accept_info_his_tb
WHEREa.batch_reg_no=b.batch_reg_no
AND1=1
ANDb.city_code='187'
ANDb.batch_reg_no=114559424)
bb_batch_accept_info_t表的batch_reg_no列是VARCHAR2类型的。
而上述语句中,该表列的查询条件中的查询常量为NUMBER型,与该表列的数据类型不一致,导致不能正确使用索引,最终导致全表扫描。
exists->in
UPDATEcm_srv_channel_ts
SETf_inactive_date=SYSDATE,
f_work_no=:
b4,
f_oper_channel_id=:
b3,
f_oper_organ_id=:
b2
WHEREf_inactive_dateISNULL
ANDf_channel_type=:
b1
ANDEXISTS(
SELECT1
FROMcm_hcust_srv_th
WHEREf_group_cust_id=:
b5
ANDh.f_service_no=s.f_service_no
ANDh.f_part_city=s.f_part_city
ANDh.f_part_cust_id=s.f_part_cust_id
ANDf_hcust_kind<>5
ANDh.f_logout_dateISNULL
ANDROWNUM=1)
UPDATEcm_srv_channel_ts
SETf_inactive_date=SYSDATE,
f_work_no=:
b4,
f_oper_channel_id=:
b3,
f_oper_organ_id=:
b2
WHEREf_inactive_dateISNULL
ANDf_channel_type=:
b1
AND(s.f_service_no,s.f_part_city,s.f_part_cust_id)in(
SELECTh.f_service_no,h.f_part_city,h.f_part_cust_id
FROMcm_hcust_srv_th
WHEREf_group_cust_id=:
b5
ANDf_hcust_kind<>5
ANDh.f_logout_dateISNULL
ANDROWNUM=1)
Exists->=
UPDATEcm_srv_channel_ts
SETf_inactive_date=SYSDATE,
f_work_no=:
b4,
f_oper_channel_id=:
b3,
f_oper_organ_id=:
b2
WHEREf_inactive_dateISNULL
ANDf_channel_type=:
b1
ANDEXISTS(
SELECT1
FROMcm_hcust_srv_th
WHEREf_group_cust_id=:
b5
ANDh.f_service_no=s.f_service_no
ANDh.f_part_city=s.f_part_city
ANDh.f_part_cust_id=s.f_part_cust_id
ANDf_hcust_kind<>5
ANDh.f_logout_dateISNULL
ANDROWNUM=1)
UPDATEcm_srv_channel_ts
SETf_inactive_date=SYSDATE,
f_work_no=:
b4,
f_oper_channel_id=:
b3,
f_oper_organ_id=:
b2
WHEREf_inactive_dateISNULL
ANDf_channel_type=:
b1
AND(s.f_service_no,s.f_part_city,s.f_part_cust_id)=(
SELECTh.f_service_no,h.f_part_city,h.f_part_cust_id
FROMcm_hcust_srv_th
WHEREf_group_cust_id=:
b5
ANDf_hcust_kind<>5
ANDh.f_logout_dateISNULL
ANDROWNUM=1)
关联查询->子查询
SELECTa.service_id,a.service_kind,a.device_no,a.guarantee_name,
a.city_code,a.grt_identity_code,a.grt_contact_phone,
a.grt_contact_address,a.service_favour_id,a.consume_fee,
a.confirm_price,a.real_price,a.cost_price,a.sale_cost_price,
a.retail_price,a.imprest_fee,a.deposit_fee,a.present_fee,
a.balance_fee,a.first_trans_fee,a.trans_type,a.return_rate,
a.month_present_limit,
(SELECTlimit_desc
FROMbb_month_present_limit_t
WHEREmonth_present_limit=a.month_present_limit)limit_desc,
(SELECTtrans_desc
FROMbb_trans_type_t
WHEREtrans_type=a.trans_type)trans_desc,
DECODE(a.consume_kind,
0,'不区分',
1,'金额',
2,'时间'
)consume_kind,a.consume_amount,a.unit_fee,a.user_id,
TO_CHAR(a.consum_num)consum_num_str,
TO_CHAR(a.begin_date,'YYYY-MM-DDhh24:
mi:
ss')begindate,
DECODE(if_valid,0,'到期结束使用',1,'正在使用')if_valid,
TO_CHAR(end_date,'YYYY-MM-DDhh24:
mi:
ss')enddate,
bb_dvc_get_info_detail_f(892,
a.service_kind,
a.city_code,
'',
a.manufacturer
)manufacturerdesc,
bb_dvc_get_info_detail_f(894,
a.service_kind,
a.city_code,
'',
a.rent_kind
)rent_kind_desc,
bb_get_info_detail_f(63,
a.service_kind,
a.city_code,
'',
a.grt_identity_kind
)grt_identity_kind_desc,
bb_dvc_get_info_detail_f(890,
a.service_kind,
a.city_code,
'',
a.fee_divide
)fee_divide_desc,
bb_dvc_get_info_detail_f(899,
a.service_kind,
a.city_code,
'',
a.attach_kind
)attach_kind_desc,
bb_dvc_get_info_detail_f(893,
a.service_kind,
a.city_code,
a.manufacturer,
a.device_type
)device_type_desc,
bb_get_info_detail_f(166,
a.service_kind,
a.city_code,
'',
a.service_favour_id
)service_favour_name,
bb_get_info_detail_f(184,
a.service_kind,
a.city_code,
'',
a.sales_mode
)bus_favour_name,
b.via_person
FROMbb_device_rent_info_ta,bb_bus_info_tb
WHEREa.register_number=b.register_number(+)ANDa.device_no=:
1
ANDa.user_id=:
2
SELECTa.service_id,a.service_kind,a.device_no,a.guarantee_name,
a.city_code,a.grt_identity_code,a.grt_contact_phone,
a.grt_contact_address,a.service_favour_id,a.consume_fee,
a.confirm_price,a.real_price,a.cost_price,a.sale_cost_price,
a.retail_price,a.imprest_fee,a.deposit_fee,a.present_fee,
a.balance_fee,a.first_trans_fee,a.trans_type,a.return_rate,
a.month_present_limit,
(SELECTlimit_desc
FROMbb_month_present_limit_t
WHEREmonth_present_limit=a.month_present_limit)limit_desc,
(SELECTtrans_desc
FROMbb_trans_type_t
WHEREtrans_type=a.trans_type)trans_desc,
DECODE(a.consume_kind,
0,'不区分',
1,'金额',
2,'时间'
)consume_kind,a.consume_amount,a.unit_fee,a.user_id,
TO_CHAR(a.consum_num)consum_num_str,
TO_CHAR(a.begin_date,'YYYY-MM-DDhh24:
mi:
ss')begindate,
DECODE(if_valid,0,'到期结束使用',1,'正在使用')if_valid,
TO_CHAR(end_date,'YYYY-MM-DDhh24:
mi:
ss')enddate,
bb_dvc_get_info_detail_f(892,
a.service_kind,
a.city_code,
'',
a.manufacturer
)manufacturerdesc,
bb_dvc_get_info_detail_f(894,
a.service_kind,
a.city_code,
'',
a.rent_kind
)rent_kind_desc,
bb_get_info_detail_f(63,
a.service_kind,
a.city_code,
'',
a.grt_identity_kind
)grt_identity_kind_desc,
bb_dvc_get_info_detail_f(890,
a.service_kind,
a.city_code,
'',
a.fee_divide
)fee_divide_desc,
bb_dvc_get_info_detail_f(899,
a.service_kind,
a.city_code,
'',
a.attach_kind
)attach_kind_desc,
bb_dvc_get_info_detail_f(893,
a.service_kind,
a.city_code,
a.manufacturer,
a.device_type
)device_type_desc,
bb_get_info_detail_f(166,
a.service_kind,
a.city_code,
'',
a.service_favour_id
)service_favour_name,
bb_get_info_detail_f(184,
a.service_kind,
a.city_code,
'',
a.sales_mode