oracle 避免多次查找并提高性能

qvsjd97n 于 2023-06-29 发布在 Oracle

关注(0)|答案(1)|浏览(139)

我有一个案例，我需要做多个连接（查找）如下查询。给出了示例场景。
我有大约200个CAT_CODE。我想了几个解决办法，就把它作为案例列了下来。是否有其他方法可以编写SQL查询以获得更好的性能？或者有更好的ETL工具吗？

主表（PRIM）：

NUM     CAT1_CODE   CAT2_CODE   CAT3_CODE
A          1           y           q     
B          2           e           a     
C          3           s           z

辅助表值列表：

CATEGORY    COLUMN_LKP        EXT_CODE
CAT1_CODE       1                AB
CAT1_CODE       2                CD
CAT1_CODE       3                HI
CAT2_CODE       y                JL
CAT2_CODE       e                QD
CAT2_CODE       s                AH
CAT3_CODE       q                CD
CAT3_CODE       a                MS
CAT3_CODE       z                EJ

CASE-1：通过SQL：

我编写了一个简单的查询来完成此任务。你认为这是正确的方法吗？还有其他方法来改进这个查询吗？现在，我正在使用Oracle和Postgres。

SELECT 
NUM,
(SELECT EXT_CODE FROM TEST_LOV 
WHERE CATEGRY='CAT1_CODE' AND COLUMN_LKP=A.CAT1_CODE) CAT1,
(SELECT EXT_CODE FROM TEST_LOV 
WHERE CATEGRY='CAT2_CODE' AND COLUMN_LKP=A.CAT2_CODE) CAT2,
(SELECT EXT_CODE FROM TEST_LOV 
WHERE CATEGRY='CAT3_CODE' AND COLUMN_LKP=A.CAT3_CODE) CAT3 
FROM 
TEST_PRIM A

必填输出：

NUM CAT1    CAT2    CAT3
A    AB      JL      CD
B    CD      QD      MS
C    HI      AH      EJ

CASE-2：ETL：

同样的情况也可以通过ETL来实现。我们需要使用查找来完成这一点。

场景一：

LOV(CAT1_CODE)  LOV(CAT2_CODE)   LOV(CAT3_CODE)
           |                |                  |
           |                |                  |
PRIM---->LOOKUP---------->LOOKUP------------>LOOKUP-------->TARGET

我不认为这是正确的方法。我们有200个代码，我们不能使用200个查找。在ETL（Datastage，Talend，BODS）中有没有更好的方法来处理这个问题，并且性能更好？

场景二：

像下面这样旋转PRIM（将CAT1_CODE、CAT2_CODE、CAT3_CODE列转换为行）并执行一次查找。但是旋转将花费很多时间，因为我们有大约6亿200列的数据。

NUM     CATGRY           CODE
A       CAT1_CODE          1
A       CAT1_CODE          y
A       CAT1_CODE          q
B       CAT2_CODE          2
B       CAT2_CODE          e
B       CAT2_CODE          a 
C       CAT3_CODE          3
C       CAT3_CODE          s
C       CAT3_CODE          z

请给我一些最好的方法来处理这种方法。它可以通过ETL或SQL。先谢谢你了。

oracle

来源：https://stackoverflow.com/questions/36355251/avoiding-multiple-look-ups-and-improving-the-performance

1条答案

按热度按时间

wz8daaqr1#

您可以使用LATERAL关键字来实现您想要的魔法。
下面的代码可能会有所帮助：

SELECT 
  NUM, 
  MAX(ext_code) FILTER (WHERE c.CATEGORY='CAT1_CODE') AS CAT1,
  MAX(ext_code) FILTER (WHERE c.CATEGORY='CAT2_CODE') AS CAT2,
  MAX(ext_code) FILTER (WHERE c.CATEGORY='CAT3_CODE') AS CAT3
FROM TEST_PRIM a
  CROSS JOIN LATERAL (
    SELECT * 
    FROM TEST_LOV b 
    WHERE 
      (a.CAT1_CODE=b.COLUMN_LKP AND B.CATEGORY = 'CAT1_CODE')
      OR (a.CAT2_CODE=b.COLUMN_LKP AND B.CATEGORY = 'CAT2_CODE')
      OR (a.CAT3_CODE=b.COLUMN_LKP AND B.CATEGORY = 'CAT3_CODE')
    ) c
 GROUP BY NUM
 ORDER BY NUM;

输出 *

num | cat1 | cat2 | cat3
-----+------+------+------
 A   | AB   | JL   | CD
 B   | CD   | QD   | MS
 C   | HI   | AH   | EJ

赞(0）回复(0）举报 2023-06-29

我来回答

oracle 避免多次查找并提高性能

1条答案

相关问题

热门标签

最新问答