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

最新推荐文章于 2024-08-01 11:05:55 发布

原创最新推荐文章于 2024-08-01 11:05:55 发布 · 1w 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#优化 #db2 #date #sql

数据库专栏收录该内容

9 篇文章

订阅专栏

本文探讨了在SQL查询中使用exists与in时索引的有效性问题，并通过具体案例对比了不同场景下索引的表现，最后提供了一种有效的SQL优化方案。

对于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:

执行计划图:

准备优化的原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
            )
                    )
        执行计划图: