Oracle编程建议.docx

Oracle编程建议.docx

《Oracle编程建议.docx》由会员分享，可在线阅读，更多相关《Oracle编程建议.docx（22页珍藏版）》请在冰豆网上搜索。

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