在hana db上使用union select和filters时如何获得更好的性能

agxfikkp  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(353)

当我声明where条件时,hana db是否对查询进行优化?例如,我想知道在哪里可以获得更好的性能,或者两个选项是否相同:
如果where条件相同,它应该在每个union select中,还是可以在查询结束时使用它?
方案1:

SELECT DATA.TABLE_NAME, DATA.DATE FROM (
SELECT
    'TABLE1' AS TABLE_NAME,
    DATE
    FROM "SCHEMA"."TABLE1" TABLE1
    WHERE DATE >= '2018-12-01' AND DATE <= '2018-12-31'
UNION SELECT
    'TABLE2' AS TABLE_NAME,
    DATE
    FROM "SCHEMA"."TABLE2" TABLE2
    WHERE DATE >= '2018-12-01' AND DATE <= '2018-12-31'
UNION SELECT
    'TABLE3' AS TABLE_NAME,
    DATE
    FROM "SCHEMA"."TABLE3" TABLE3
    WHERE DATE >= '2018-12-01' AND DATE <= '2018-12-31'
) DATA
WHERE DATA.DATE >= '2018-12-01' AND DATA.DATE <= '2018-12-31'

数据库是否使用where条件优化查询,或者我应该在每个select上提到where条件?

uqzxnwby

uqzxnwby1#

假设您的表没有重复项,您可以使用 UNION ALL . 子查询也是不必要的:

SELECT 'TABLE1' AS TABLE_NAME, DATE
FROM "SCHEMA"."TABLE1" TABLE1
WHERE DATE >= '2018-12-01' AND DATE <= '2018-12-31'
UNION ALL
SELECT 'TABLE2' AS TABLE_NAME, DATE
FROM "SCHEMA"."TABLE2" TABLE2
WHERE DATE >= '2018-12-01' AND DATE <= '2018-12-31'
UNION ALL
SELECT 'TABLE3' AS TABLE_NAME, DATE
FROM "SCHEMA"."TABLE3" TABLE3
WHERE DATE >= '2018-12-01' AND DATE <= '2018-12-31';
``` `UNION` 删除重复项会产生开销。由于第一列的原因,表之间没有重复项,因此请使用 `UNION ALL` 相反。
如果您的表确实有重复项,则在上添加索引 `DATE` 并使用 `SELECT DISTINCT` 与 `UNION ALL` :

SELECT DISTINCT 'TABLE1' AS TABLE_NAME, DATE
FROM "SCHEMA"."TABLE1" TABLE1
WHERE DATE >= '2018-12-01' AND DATE <= '2018-12-31'
UNION ALL
SELECT DISTINCT 'TABLE2' AS TABLE_NAME, DATE
FROM "SCHEMA"."TABLE2" TABLE2
WHERE DATE >= '2018-12-01' AND DATE <= '2018-12-31'
UNION ALL
SELECT DISTINCT 'TABLE3' AS TABLE_NAME, DATE
FROM "SCHEMA"."TABLE3" TABLE3
WHERE DATE >= '2018-12-01' AND DATE <= '2018-12-31';

索引将用于 `SELECT DISTINCT` 而且,删除表之间不存在的重复项也不会产生开销。
n3ipq98p

n3ipq98p2#

回答这个问题:是的,saphana优化了查询,如果可能的话,它会尝试下推外部where条件。
最简单的方法就是使用 EXPLAIN PLAN 并检查是否在处理联合之前或之后应用了筛选器。

相关问题