Oracle編程建議.doc

綁定變量在Oracle數(shù)據(jù)庫(kù)管理系統(tǒng)中，對(duì)于一個(gè)提交的SQL語(yǔ)句，有兩種可選的解析過(guò)程：硬解析和軟解析。當(dāng)我們提交了一個(gè)SQL語(yǔ)句后，Oracle會(huì)在library cache中查詢是否存在完全相同的語(yǔ)句。如果存在相同的語(yǔ)句，則執(zhí)行軟解析，使用已有的解析樹(shù)和執(zhí)行計(jì)劃。如果不存在相同的語(yǔ)句，則執(zhí)行硬解析，需要對(duì)語(yǔ)句進(jìn)行解析，創(chuàng)建解析樹(shù)，生成執(zhí)行計(jì)劃。硬解析不僅耗費(fèi)大量的CPU資源，而且會(huì)占用重要的閂鎖（latch，為了順序訪問(wèn)以及修改一些內(nèi)存區(qū)域而設(shè)置的，這些內(nèi)存區(qū)域是不能被同時(shí)修改。）資源。綁定變量是用于替代SQL語(yǔ)句中的常量的替代變量。唯一使得Oracle能夠重復(fù)利用執(zhí)行計(jì)劃的方法就是采用綁定變量。綁定變量能夠使得每次提交的SQL語(yǔ)句都完全一樣。值得注意的是，Oracle會(huì)自動(dòng)將過(guò)程、函數(shù)、包中語(yǔ)句使用的變量作為綁定變量處理，我們需要特別關(guān)注C、JAVA、PHP等語(yǔ)言編寫(xiě)的外部應(yīng)用，以及動(dòng)態(tài)SQL語(yǔ)句。COPY命令的相關(guān)參數(shù)設(shè)置ARRAYSIZE參數(shù)該參數(shù)用于設(shè)置SQL*PLUS一次從數(shù)據(jù)庫(kù)獲取的行數(shù)，默認(rèn)值為15，有效值為1至5000。較大的值可提高查詢和子查詢的性能，可獲取更多的行，同時(shí)也需要更多的內(nèi)存。當(dāng)超過(guò)1000時(shí)，其效果不大。COPYCOMMIT參數(shù)該參數(shù)控制COPY命令提交對(duì)數(shù)據(jù)庫(kù)修改的批數(shù)。即每次拷貝n批后，將提交到目標(biāo)數(shù)據(jù)庫(kù)?？捎肁RRAYSIZE參數(shù)設(shè)置一批的大小。COPYCOMMIT參數(shù)的默認(rèn)值為0，有效值為0到5000。如果置COPYCOMMIT為0，則僅在COPY操作結(jié)束時(shí)執(zhí)行一次提交。TRUNCATE TABLEDROP STORAGE數(shù)據(jù)部分所使用的extent 空間會(huì)被釋放（釋放回收到 minextents個(gè)extent，NEXT_EXTENT 設(shè)置MINEXTENTS 之后的EXTENT），釋放出來(lái)的空間可以供其它segment 使用 。表的index 部分會(huì)數(shù)據(jù)刪除，extent 部分也被釋放，剩下第一個(gè)extent 。會(huì)將HWM（高水平線） 重新設(shè)置到第一個(gè)Block 的位置(HWM會(huì)改變)。REUSE STORAGE數(shù)據(jù)部分所在的extent 空間會(huì)被回收，僅僅數(shù)據(jù)會(huì)被刪除，數(shù)據(jù)刪除之后的freespace 空間只能供本表使用，可以供其它 segment 使用 。index 部分會(huì)數(shù)據(jù)刪除，但是保extent 部分。會(huì)將HWM 重新設(shè)置到第一個(gè)Block 的位置(HWM 會(huì)改變)。當(dāng)使用DROP STORAGE時(shí)將縮短表和表引，并重新設(shè)置NEXT 參數(shù)。 使用REUSE STORAGE時(shí)會(huì)縮短表或者調(diào)整NEXT 參數(shù)，可以減少對(duì)表及數(shù)據(jù)字典的鎖定時(shí)間。應(yīng)當(dāng)注意的是DROP STORAGE是TRUNCATE TABLA語(yǔ)句的默認(rèn)選項(xiàng)，當(dāng)我們要整理表的碎片時(shí)，應(yīng)使用REUSE STORAGE選項(xiàng)。批量操作批量綁定（Bulk binds）通過(guò)減少PL/SQL和SQL引擎之間的上下文切換(context switches )提高性能。批量綁定（Bulk binds）包括： Input collections：使用use the FORALL 語(yǔ)句，用來(lái)改善DML（INSERT、UPDATE和DELETE) 操作的性能 Output collections：使用BULK COLLECT 子句，一般用來(lái)提高查詢（SELECT）操作的性能BULK COLLECT子句用于批量取得數(shù)據(jù)，適用于select into、fetch into，及DML語(yǔ)句的returning into返回子句。批量查詢BULK COLLECT使用bulk collect可以將查詢結(jié)果一次性地加載到collections中。FORALL語(yǔ)句 index IN collection.lower_bound. collection.upper_bound index IN INDICES OF collection.lower_bound. collection.upper_bound index in values of collection需要注意的是，F(xiàn)ORALL語(yǔ)句中不能使用記錄類型的集合變量%TYPE在很多情況下，PL/SQL變量是用來(lái)存儲(chǔ)數(shù)據(jù)庫(kù)表中的數(shù)據(jù)。在這種情況下，變量應(yīng)該擁有與表列相同的類型。此時(shí)，使用“%TYPE”屬性而不是將變量類型硬性編碼，可以使PL/SQL代碼更加靈活，避免數(shù)據(jù)庫(kù)更新對(duì)PL/SQL代碼的影響。其他WHERE子句中變量、常量的數(shù)據(jù)類型要與表列的定義保持一致，這樣才能確保正確使用索引。WHERE子句中使用的表列上應(yīng)避免使用函數(shù)，確保正確使用索引。除非基于該表列建立了函數(shù)索引。處理較大數(shù)據(jù)的應(yīng)用應(yīng)盡量并行。大數(shù)據(jù)量表關(guān)聯(lián)查詢操作盡量拆分為一系列基于單表的查詢語(yǔ)句基于本地索引（分區(qū)）查詢分區(qū)表時(shí)，where條件中必須包含分區(qū)鍵示例使用分區(qū)索引查詢時(shí)WHERE條件中必須包含分區(qū)項(xiàng)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使用分區(qū)索引查詢而WHERE條件中不包含分區(qū)項(xiàng)時(shí)，Oracle將掃描分區(qū)表的所有分區(qū)，查詢符合條件的記錄。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子句中變量、常量的數(shù)據(jù)類型要與表列的定義保持一致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類型的。而上述語(yǔ)句中，該表列的查詢條件中的查詢常量為NUMBER型，與該表列的數(shù)據(jù)類型不一致，導(dǎo)致不能正確使用索引，最終導(dǎo)致全表掃描。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) 關(guān)聯(lián)查詢 - 子查詢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, 不區(qū)分, 1, 金額, 2, 時(shí)間 ) 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, 到期結(jié)束使用, 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, 不區(qū)分, 1, 金額, 2, 時(shí)間 ) 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, 到期結(jié)束使用, 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臨時(shí)表&批量操作CREATE OR REPLACE PROCEDURE MVCRM.trans_product_info_check_p(is_city_code VARCHAR2,is_total_thread number,is_thread_id number)AS-定義項(xiàng)目的游標(biāo)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;-定義項(xiàng)目的游標(biāo) 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