欢迎来到天天文库
浏览记录
ID:48062536
大小:599.50 KB
页数:23页
时间:2020-01-13
《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
此文档下载收益归作者所有