Oracle case when改寫SQL
Oracle case when 改寫 SQL
--- 說明:案例來自《 收穫,不止SQL 最佳化 》
建立測試資料:
SQL > drop table t1 purge ;
SQL > drop table t2 purge ;
SQL > create table t1 as select * from dba_objects ;
SQL > create table t2 as select * from dba_objects ;
SQL > update t2 set status = 'INVALID' WHERE ROWNUM <= 10000 ;
SQL > update t2 set generated = 'Y' WHERE ROWNUM <= 10000 ;
SQL > update t2 set temporary = 'Y' WHERE ROWNUM <= 10000 ;
SQL > update t2 set temporary = 'M' WHERE temporary <> 'Y' ;
SQL > update t2 set temporary = 'Q' WHERE temporary <> 'Y' or temporary <> 'M' ;
SQL > COMMIT ;
SQL > set autotrace traceonly
SQL > set linesize 1000
原 SQL :
SQL> select t1.object_name, t1.object_id, (select count(*) from t2 where temporary = 'Y' and t2.object_id = t1.object_id) CNT_TEMPORARY_Y, (select count(*) from t2 where created >= sysdate - 365 and t2.object_id = t1.object_id) CNT_CREATED_NEW, (select sum(object_id) from t2 where status <> 'VALUD' and t2.object_id = t1.object_id) SUM_OBJID_STATUS_V, (select sum(object_id) from t2 where generated = 'Y' and t2.object_id = t1.object_id) SUM_OBJID_GENERATED_Y, (select sum(object_id) from t2 where generated = 'M' and t2.object_id = t1.object_id) SUM_OBJID_GENERATED_M, (select sum(object_id) from t2 where generated = 'Q' and t2.object_id = t1.object_id) SUM_OBJID_GENERATED_Q from t1 where t1.object_id <= 50;
case when改造 後的 SQL :
with w_t2 as (select t2.object_id, count(case when t2.temporary='Y' then 1 end ) CNT_TEMPORARY_Y, count(case when created >=sysdate-365 then 1 end ) CNT_CREATED_NEW, sum(case when t2.status<>'VALID' then t2.object_id end ) SUM_OBJID_STATUS_V, sum(case when t2.generated = 'Y' then t2.object_id end ) SUM_OBJID_GENERATED_Y, sum(case when t2.generated = 'M' then t2.object_id end ) SUM_OBJID_GENERATED_M, sum(case when t2.generated = 'Q' then t2.object_id end ) SUM_OBJID_GENERATED_Q from t2 group by t2.object_id) select t1.object_name,t1.object_id,w_t2.* from t1,w_t2 where t1.object_id=w_t2.object_id and t1.object_id<=50;
結論: SQL 改寫後 T2 表訪問次數由 6 次降到 1 次,邏輯讀 consistent gets 由 320100 降到 2580 ,效能有所提升。
歡迎關注我的微信公眾號"IT小Chen",共同學習,共同成長!!!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29785807/viewspace-2693912/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Case WhenSQL
- Oracle Case WhenOracle
- ORACLE CASE WHEN 及 SELECT CASE WHEN的用法Oracle
- ORACLE SQL開發where子句之case-whenOracleSQL
- SQL中的CASE WHEN使用SQL
- PL/SQL Case when應用SQL
- sql case when, Exist ,group by ,聚合SQL
- Oracle的order by case whenOracle
- oracle plsql case when_end case小記OracleSQL
- SQL Case when 的使用方法SQL
- sql中case when的小學SQL
- ORACLE多欄位CASE WHENOracle
- SQL中的case when then else end用法SQL
- sql server select case when的用法SQLServer
- 案例:oracle中case when的用法Oracle
- mysql case when then 使用MySql
- Oracle vs PostgreSQL Develop(30) - Index&Case whenOracleSQLdevIndex
- oracle面試題[關於case when的用法]Oracle面試題
- Case when 支援變數變數
- case when遇上null值Null
- ORA-00937——Oracle中GROUP BY搭配CASE WHEN的一則SQL報錯OracleSQL
- SQL語句case when外用sum與count的區別SQL
- SQL中的case when then else end用法 【詳細】轉載SQL
- Oracle 條件索引 case when 報錯解決方案Oracle索引
- sqlserver與oracle case when else ,isnull語法差別SQLServerOracleNull
- mysql中case when的使用MySql
- mysql中的case when 與if()MySql
- oracle sql tunning 15 --常用改寫OracleSQL
- tcbs_批量儲存過程_sql_case when_示例儲存過程SQL
- sql改寫SQL
- plsql_case when_end case學習小例SQL
- SQLServer使用case when中的order bySQLServer
- plsql_case when_if else endifSQL
- 關於sql server case when ..else ..end結果型別的小議SQLServer型別
- Oracle group by與case when統一單位後統計數量Oracle
- SQL改寫優化SQL優化
- MySQL 的CASE WHEN 語句使用說明MySql
- DB2 SQL改寫DB2SQL