db2 exists 和in联合使用优化方法.

对于exists和in,当exists里面嵌套in的时候,如果in语句里面只有一个元素的情况下,这时候索引是有效的.如果多于一个元素,这种情况下索引会失效.下面写一个简单的例子来验证以上说法.

 

sql1:

select   1 from pm_measurement_recalculation reca
where
exists (
select
operating_date
from
PM_MEASUREMENT_READING reading
where
reading.measurement_id in(
– 10000828, 10000670, 10000671,215001368
10000828,10000670,10000671
)
)

执行计划图:(索引失效)

sql2:

select   1 from pm_measurement_recalculation reca
where
exists (
select
operating_date
from
PM_MEASUREMENT_READING reading
where
reading.measurement_id in(
– 10000828, 10000670, 10000671,215001368
10000671
)
)

执行计划图:

 

 

 

 

 

 

 

准备优化的原sql:

update
pm_measurement_recalculation reca
set
reca.from_operating_date=(    select
min(tempTable.operating_date) as operating_date
from
(    select
min(r.operating_date) as operating_date
from
pm_measurement_reading r
where
r.measurement_id in (
10000828, 10000670, 10000671, 215001368
)
group by
r.operating_date   ) as tempTable  )
where
reca.measurement_id in (
10000828, 10000670, 10000671,215001368
)
and
exists  (
select operating_date from PM_MEASUREMENT_READING reading
where reading.measurement_id in(
10000828, 10000670, 10000671,215001368
)
)

执行计划图

 

优化后的sql:

update
pm_measurement_recalculation reca
set
reca.from_operating_date=(    select
min(tempTable.operating_date) as operating_date
from
(    select
min(r.operating_date) as operating_date
from
pm_measurement_reading r
where
r.measurement_id in (
10000828, 10000670, 10000671, 215001368
)
group by
r.operating_date   ) as tempTable  )
where
reca.measurement_id in (
10000828, 10000670, 10000671,215001368
)
and
exists  (
select operating_date from PM_MEASUREMENT_READING reading
where reading.measurement_id in(
select
reading.measurement_id
from
PM_MEASUREMENT_READING reading
where
reading.measurement_id in(
10000828, 10000670, 10000671,215001368
)
group by reading.measurement_id
fetch first 1 rows only
)
)
执行计划图: