sql case oracle,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"，共同学习，共同成长！！！