Oracle编程建议.doc

绑定变量在Oracle数据库管理系统中，对于一个提交的SQL语句，有两种可选的解析过程：硬解析和软解析。当我们提交了一个SQL语句后，Oracle会在library cache中查询是否存在完全相同的语句。如果存在相同的语句，则执行软解析，使用已有的解析树和执行计划。如果不存在相同的语句，则执行硬解析，需要对语句进行解析，创建解析树，生成执行计划。硬解析不仅耗费大量的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操作结束时执行一次提交。TRUNCATE TABLEDROP STORAGE数据部分所使用的extent 空间会被释放（释放回收到 minextents个extent，NEXT_EXTENT 设置MINEXTENTS 之后的EXTENT），释放出来的空间可以供其它segment 使用 。表的index 部分会数据删除，extent 部分也被释放，剩下第一个extent 。会将HWM（高水平线） 重新设置到第一个Block 的位置(HWM会改变)。REUSE STORAGE数据部分所在的extent 空间会被回收，仅仅数据会被删除，数据删除之后的freespace 空间只能供本表使用，可以供其它 segment 使用 。index 部分会数据删除，但是保extent 部分。会将HWM 重新设置到第一个Block 的位置(HWM 会改变)。当使用DROP STORAGE时将缩短表和表引，并重新设置NEXT 参数。 使用REUSE STORAGE时会缩短表或者调整NEXT 参数，可以减少对表及数据字典的锁定时间。应当注意的是DROP STORAGE是TRUNCATE TABLA语句的默认选项，当我们要整理表的碎片时，应使用REUSE STORAGE选项。批量操作批量绑定（Bulk binds）通过减少PL/SQL和SQL引擎之间的上下文切换(context switches )提高性能。批量绑定（Bulk binds）包括： Input collections：使用use the FORALL 语句，用来改善DML（INSERT、UPDATE和DELETE) 操作的性能 Output collections：使用BULK COLLECT 子句，一般用来提高查询（SELECT）操作的性能BULK COLLECT子句用于批量取得数据，适用于select into、fetch into，及DML语句的returning into返回子句。批量查询BULK COLLECT使用bulk collect可以将查询结果一次性地加载到collections中。FORALL语句 index IN collection.lower_bound. collection.upper_bound index IN INDICES OF collection.lower_bound. collection.upper_bound index in values of collection需要注意的是，FORALL语句中不能使用记录类型的集合变量%TYPE在很多情况下，PL/SQL变量是用来存储数据库表中的数据。在这种情况下，变量应该拥有与表列相同的类型。此时，使用“%TYPE”属性而不是将变量类型硬性编码，可以使PL/SQL代码更加灵活，避免数据库更新对PL/SQL代码的影响。其他WHERE子句中变量、常量的数据类型要与表列的定义保持一致，这样才能确保正确使用索引。WHERE子句中使用的表列上应避免使用函数，确保正确使用索引。除非基于该表列建立了函数索引。处理较大数据的应用应尽量并行。大数据量表关联查询操作尽量拆分为一系列基于单表的查询语句基于本地索引（分区）查询分区表时，where条件中必须包含分区键示例使用分区索引查询时WHERE条件中必须包含分区项SELECT COUNT (a.user_id) FROM bb_device_rent_info_t a, bb_service_relation_t b, bb_customer_info_t c WHERE a.service_id = :1 AND a.service_kind = :2 AND a.city_code = :3 AND a.user_id = b.user_id AND b.customer_id = c.customer_id使用分区索引查询而WHERE条件中不包含分区项时，Oracle将扫描分区表的所有分区，查询符合条件的记录。SELECT COUNT (a.user_id) FROM bb_device_rent_info_t a, bb_service_relation_t b, bb_customer_info_t c WHERE a.service_id = :1 AND a.service_kind = :2 AND a.city_code = :3 AND a.user_id = b.user_id and b.city_code = :3 AND b.customer_id = c.customer_id and c.city_code = :3WHERE子句中变量、常量的数据类型要与表列的定义保持一致SELECT COUNT (1) FROM (SELECT 1 FROM bb_batch_accept_record_t a, bb_batch_accept_info_t b WHERE a.batch_reg_no = b.batch_reg_no AND 1 = 1 AND b.city_code = 187 AND b.batch_reg_no = 114559424 UNION ALL SELECT 1 FROM bb_batch_accept_record_his_t a, bb_batch_accept_info_his_t b WHERE a.batch_reg_no = b.batch_reg_no AND 1 = 1 AND b.city_code = 187 AND b.batch_reg_no = 114559424 UNION ALL SELECT 1 FROM bb_batch_accept_record_error_t a, bb_batch_accept_info_t b WHERE a.batch_reg_no = b.batch_reg_no AND 1 = 1 AND b.city_code = 187 AND b.batch_reg_no = 114559424 UNION ALL SELECT 1 FROM bb_batch_accept_record_error_t a, bb_batch_accept_info_his_t b WHERE a.batch_reg_no = b.batch_reg_no AND 1 = 1 AND b.city_code = 187 AND b.batch_reg_no = 114559424)bb_batch_accept_info_t表的batch_reg_no列是VARCHAR2类型的。而上述语句中，该表列的查询条件中的查询常量为NUMBER型，与该表列的数据类型不一致，导致不能正确使用索引，最终导致全表扫描。exists - inUPDATE cm_srv_channel_t s SET f_inactive_date = SYSDATE, f_work_no = :b4, f_oper_channel_id = :b3, f_oper_organ_id = :b2WHERE f_inactive_date IS NULL AND f_channel_type = :b1 AND EXISTS ( SELECT 1 FROM cm_hcust_srv_t h WHERE f_group_cust_id = :b5 AND h.f_service_no = s.f_service_no AND h.f_part_city = s.f_part_city AND h.f_part_cust_id = s.f_part_cust_id AND f_hcust_kind 5 AND h.f_logout_date IS NULL AND ROWNUM = 1)UPDATE cm_srv_channel_t s SET f_inactive_date = SYSDATE, f_work_no = :b4, f_oper_channel_id = :b3, f_oper_organ_id = :b2WHERE f_inactive_date IS NULL AND f_channel_type = :b1 AND (s.f_service_no,s.f_part_city,s.f_part_cust_id) in ( SELECT h.f_service_no,h.f_part_city,h.f_part_cust_id FROM cm_hcust_srv_t h WHERE f_group_cust_id = :b5 AND f_hcust_kind 5 AND h.f_logout_date IS NULL AND ROWNUM = 1) Exists - =UPDATE cm_srv_channel_t s SET f_inactive_date = SYSDATE, f_work_no = :b4, f_oper_channel_id = :b3, f_oper_organ_id = :b2WHERE f_inactive_date IS NULL AND f_channel_type = :b1 AND EXISTS ( SELECT 1 FROM cm_hcust_srv_t h WHERE f_group_cust_id = :b5 AND h.f_service_no = s.f_service_no AND h.f_part_city = s.f_part_city AND h.f_part_cust_id = s.f_part_cust_id AND f_hcust_kind 5 AND h.f_logout_date IS NULL AND ROWNUM = 1)UPDATE cm_srv_channel_t s SET f_inactive_date = SYSDATE, f_work_no = :b4, f_oper_channel_id = :b3, f_oper_organ_id = :b2WHERE f_inactive_date IS NULL AND f_channel_type = :b1 AND (s.f_service_no,s.f_part_city,s.f_part_cust_id) = ( SELECT h.f_service_no,h.f_part_city,h.f_part_cust_id FROM cm_hcust_srv_t h WHERE f_group_cust_id = :b5 AND f_hcust_kind 5 AND h.f_logout_date IS NULL AND ROWNUM = 1) 关联查询 - 子查询SELECT a.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, (SELECT limit_desc FROM bb_month_present_limit_t WHERE month_present_limit = a.month_present_limit) limit_desc, (SELECT trans_desc FROM bb_trans_type_t WHERE trans_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-DD hh24:mi:ss) begindate, DECODE (if_valid, 0, 到期结束使用, 1, 正在使用) if_valid, TO_CHAR (end_date, YYYY-MM-DD hh24: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 FROM bb_device_rent_info_t a, bb_bus_info_t b WHERE a.register_number = b.register_number(+) AND a.device_no = :1 AND a.user_id = :2SELECT a.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, (SELECT limit_desc FROM bb_month_present_limit_t WHERE month_present_limit = a.month_present_limit) limit_desc, (SELECT trans_desc FROM bb_trans_type_t WHERE trans_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-DD hh24:mi:ss) begindate, DECODE (if_valid, 0, 到期结束使用, 1, 正在使用) if_valid, TO_CHAR (end_date, YYYY-MM-DD hh24: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, (SELECT b.via_person FROM bb_bus_info_t b WHERE b.register_number = a.register_number) FROM bb_device_rent_info_t a WHERE a.device_no = :1 AND a.user_id = :2临时表&批量操作CREATE OR REPLACE PROCEDURE MVCRM.trans_product_info_check_p(is_city_code VARCHAR2,is_total_thread number,is_thread_id number)AS-定义项目的游标CURSOR get_old_item IS select f_service_kind , f_old_item_id from mvcrm.conv_subscription_check_t where f_city_code=is_city_code; -and mod(f_old_item_id,is_total_thread)=is_thread_id-1;- for update;vn_count1 number(8);vn_count2 number(8);vn_count3 number(8);vn_service_kind number;vn_old_item_id number;BEGIN open get_old_item; loop BEGIN fetch get_old_item into vn_service_kind,vn_old_item_id; exit when get_old_item%notfound; -FOR c_get_old_item IN get_old_item LOOP vn_count1:=0; vn_count2:=0; vn_count3:=0; select count(*) into vn_count1 from bb_user_product_info_t a where exists( select 1 from CONV_FAVOUR_COMB_TEMP_T b where b.F_NEW_PROD_COMP_ID=a.f_comp_id and b.f_city_code=is_city_code and b.f_old_item_id=vn_old_item_id -and b.f_city_code=c_get_old_item.f_city_code -and b.f_service_kind=c_get_old_item.f_service_kind )and f_city_code=is_city_code; select count(*) into vn_count2 from bb_user_product_info_t a where exists( select 1 from CONV_MISS_COMB_TEMP_T b where b.F_NEW_PROD_COMP_ID=a.f_comp_id and b.f_city_code=is_city_code and b.f_old_item_id=vn_old_item_id -and b.f_city_code=c_get_old_item.f_city_code -and b.f_service_kind=c_get_old_item.f_service_kind )and f_city_code=is_city_code; select count(*) into vn_count3 from bb_user_product_info_t a where exists( select 1 from conv_rel_prod_temp_t b where b.F_NEW_PROD_COMP_ID=a.f_comp_id and b.f_city_code=is_city_code and b.f_old_item_id=vn_old_item_id -and b.f_city_code=c_get_old_item.f_city_code -and b.f_service_kind=c_get_old_item.f_service_kind )and f_city_code=is_city_code; update conv_subscription_check_t set f_new_comp_cnt=vn_count1, f_new_np_cnt=vn_count2, f_new_ss_cnt=vn_count3, f_new_pub_cnt=vn_count1+vn_count2+vn_count3 where f_city_code=is_city_code and f_old_item_id=vn_old_item_id and f_service_kind = vn_service_kind; END; END LOOP; close get_old_item; COMMIT;END;/-drop table conv_user_product_temp_t1 ;CREATE GLOBAL TEMPORARY TABLE mvcrm.conv_user_product_temp_t1(f_comp_id VARCHAR2(12) ,f_city_codevarchar2(8) ,f_cntnumber(8,0)ON COMMIT delete ROWS;CREATE OR REPLACE PROCEDURE mvcrm.trans_product_info_check_p3 ( is_city_code VARCHAR2)AS TYPE tab_service_kind_type IS TABLE OF conv_subscription_check_t.f_service_kind%TYPE; TYPE tab_old_item_id_type IS TABLE OF conv_subscription_check_t.f_old_item_id%TYPE; TYPE tab_rowid_type IS TABLE OF ROWID; tab_service_kind tab_service_kind_type; tab_old_item_id tab_old_item_id_type; tab_rowid tab_rowid_type;-定义项目的游标 CURSOR get_old_item IS SELECT f_service_kind, f_old_item_id, ROWID FROM mvcrm.conv_subscription_check_t WHERE f_city_code = is_city_code; vn_fetch_limit PLS_INTEGER := 5000; TYPE tab_cnt1_type IS TABLE OF PLS_INTEGER; TYPE tab_cnt2_type IS TABLE OF PLS_INTEGER; TYPE tab_cnt3_type IS TABLE OF PLS_INTEGER; vn_count1 tab_cnt1_type := tab_cnt1_type (); vn_count2 tab_cnt2_type := tab_cnt2_type (); vn_count3 tab_cnt3_type := tab_cnt3_type ();BEGIN vn_count1.EXTEND (5000); vn_count2.EXTEND (5000); vn_count3.EXTEND (5000); INSERT INTO conv_user_product_temp_t1 (f_comp_id, f_city_code, f_cnt) SELECT f_comp_id, is_city_code, COUNT (1) cnt FROM bb_user_product_info_t WHERE f_city_code = is_city_code GROUP BY f_comp_id; OPEN get_old_item; LOOP BEGIN