用于生成行的UNION ALL与CONNECT BY LEVEL

Question

我想知道哪种方法更好/更快/更有效地将任意字符串转换为列：

UNION ALL

SELECT my_field,
       CASE WHEN my_field = 'str1'
            THEN ...
            ...
       END,
       ...
FROM (
       SELECT 'str1' AS my_field FROM DUAL
       UNION ALL
       SELECT 'str2' AS my_field FROM DUAL
       UNION ALL
       SELECT 'str3' AS my_field FROM DUAL
     ),
     ...

CONNECT BY LEVEL

SELECT CASE WHEN rowno = 1
            THEN 'str1'
            ...
       END AS my_field,
       CASE WHEN rowno = 1
            THEN ...
            ...
       END,
       ...
FROM (
       SELECT ROWNUM rowno
       FROM DUAL
       CONNECT BY LEVEL <= 3
     ),
     ...

我倾向于使用UNION ALL版本，因为它使最外层的SELECT更简单:我不需要执行第二个CASE语句就可以获得所需的字符串值。与WHEN rowno = 1相比，看到WHEN my_field = 'str1'也更具可读性。我询问CONNECT BY LEVEL版本的唯一原因是因为它是在Example of Data Pivots in SQL (rows to columns and columns to rows)中建议的(请参阅“从两行到六行(一列到行轴)”一节)。

我只能通过SELECT访问我正在使用的Oracle数据库，所以我不能运行EXPLAIN PLAN。我以前也尝试过使用WITH ... AS，但没有成功。

Answer 1

除了最微不足道的行数，我会使用connect by。然而，没有解释计划是一件痛苦的事情。你真的是束手无策了。我真的很想知道优化器对基数的估计是多少。

Answer 2

我认为您混淆了“SQL中的数据透视示例(行到列和列到行)”中使用的UNION ALL和CONNECT BY方法。

问题中的UNION ALL用于将单列的多行转换为单列的单行：

label, 1, val1
label, 2, val2
label, 3, val3

转到

label, val1, val2, val3

CONNECT BY子查询用于将具有多列的单行转换为具有单列的多行，因此它使用生成子查询来乘以现有数据集：

label, val1, val2, val3 
+
1
2
3

结果为：

label, 1, val1, val2, val3
label, 2, val1, val2, val3
label, 3, val1, val2, val3

转换为：

label, 1, val1
label, 2, val2
label, 3, val3