UNION ALL versus CONNECT BY LEVEL for generating rows

Question

I was wondering which is a better/faster/more efficient way of turning arbitrary strings into columns:

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
     ),
     ...

I'm inclined to go with the UNION ALL version if only because it makes the outermost SELECT simpler: I don't have to do a second CASE statement to get the desired string values. It also is more readable to see WHEN my_field = 'str1' rather than WHEN rowno = 1. The only reason I ask about the CONNECT BY LEVEL version is because it was suggested in Example of Data Pivots in SQL (rows to columns and columns to rows) (see the "From Two rows to Six rows (a column to row pivot)" section).

I have only SELECT access to the Oracle database I'm using, so I cannot run EXPLAIN PLAN. I have also tried to use WITH ... AS before, too, without luck.

Answer 1

I would use connect by for any but the most trivial number of rows. Not having explain plan is a pain though ... you're really having your hands tied there. I'd be really keen on knowing what the optimiser's estimate of cardinality is.

Answer 2

I think you're confusing the purposed UNION ALL and CONNECT BY methods used in "Example of Data Pivots in SQL (rows to columns and columns to rows)"

The UNION ALL in your question is used to transform multiple rows with a single column into a single row with multiple columns:

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

into

label, val1, val2, val3

The CONNECT BY sub-query is used to transform a single row with multiple columns into mutiple rows with single column, so it uses as generator sub-query to multiply the existing data set:

label, val1, val2, val3 
+
1
2
3

result into:

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

transformed into:

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