Oracle SQL优化案例.ppt

《Oracle SQL优化案例.ppt》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、OracleSQL优化案例使用case–优化前SELECTCOUNT(*)FROMproductsWHEREprice<13;COUNT(*)----------2SELECTCOUNT(*)FROMproductsWHEREpriceBETWEEN13AND15;COUNT(*)----------5SELECTCOUNT(*)FROMproductsWHEREprice>15;COUNT(*)----------5使用case–优化后SELECTCOUNT(CASEWHENprice<13THEN1ELSEnullEND)low,COUNT(

2、CASEWHENpriceBETWEEN13AND15THEN1ELSEnullEND)med,COUNT(CASEWHENprice>15THEN1ELSEnullEND)highFROMproducts;LOWMEDHIGH------------------------------255尽量用where代替havingSELECTproduct_type_id,AVG(price)FROMproductsGROUPBYproduct_type_idHAVINGproduct_type_idIN(1,2);PRODUCT_TYPE_IDAVG

3、(PRICE)-------------------------124.975226.22尽量用where代替having-优化后SELECTproduct_type_id,AVG(price)FROMproductsWHEREproduct_type_idIN(1,2)GROUPBYproduct_type_id;PRODUCT_TYPE_IDAVG(PRICE)-------------------------124.975226.22Union和UnionALL优化前：SELECTacct_num,balance_amtFROMdebit_

4、transactionsWHEREtran_date='31-DEC-95'UNIONSELECTacct_num,balance_amtFROMcredit_transactionsWHEREtran_date='31-DEC-95'Union和UnionALL的优化SELECTacct_num,balance_amtFROMdebit_transactionsWHEREtran_date='31-DEC-95'UNIONALLSELECTacct_num,balance_amtFROMcredit_transactionsWHEREtran_

5、date='31-DEC-95'HighWaterMark高水位问题SELECTCOUNT(*)FROMYYYY;------151070real:5378SELECTCOUNT(*)FROMYYYYWHERESTATE='VIC';------151070real:16884DELETEFROMYYYY;real:55277SELECTCOUNT(*)FROMYYYYWHERESTATE='VIC';------0real:16029使用exist代替inSELECTproduct_id,nameFROMproductsWHEREproduct

6、_idIN(SELECTproduct_idFROMpurchases);SELECTproduct_id,nameFROMproductsouterWHEREEXISTS(SELECT1FROMpurchasesinnerWHEREinner.product_id=outer.product_id);使用exist代替distinctSELECTDISTINCTpr.product_id,pr.nameFROMproductspr,purchasespuWHEREpr.product_id=pu.product_id;PRODUCT_IDNAM

7、E---------------------------------------1ModernScience2Chemistry3Supernova使用exist代替distinct–优化后SELECTproduct_id,nameFROMproductsouterWHEREEXISTS(SELECT1FROMpurchasesinnerWHEREinner.product_id=outer.product_id)PRODUCT_IDNAME---------------------------------------1ModernScience

8、2Chemistry3Supernova;优化“or”select*fromsys_userwhereuser_code=‘liangt