best time to SELECT value in Oracle nested query

Question

From an optimization standpoint, I want to know when it's best to SELECT some arbitrary data as columns. Specifically, I have this query:

Where do I put this part?

SELECT 'String 1' AS segment FROM DUAL
UNION ALL
SELECT 'String 2' AS segment FROM DUAL
UNION ALL
SELECT 'String 3' AS segment FROM DUAL

In Outermost Query

I need access to this segment column in my outermost SELECT and so it seems like I should SELECT it only when it it's necessary, i.e.

SELECT ...,
       segment,
       CASE WHEN segment = 'String 1' THEN ... END,
       ...
FROM (
       SELECT 'String 1' AS segment FROM DUAL
       UNION ALL
       SELECT 'String 2' AS segment FROM DUAL
       UNION ALL
       SELECT 'String 3' AS segment FROM DUAL
     ),
     (
       SELECT ...
       FROM ...
       GROUP BY ...
     )
WHERE ...

Versus In Innermost Query

However, is that better than SELECTing my arbitrary strings in the innermost query?:

SELECT ...,
       segment,
       CASE WHEN segment = 'String 1' THEN ... END,
       ...
FROM (
       SELECT ...,
              segment
       FROM (
              SELECT 'String 1' AS segment FROM DUAL
              UNION ALL
              SELECT 'String 2' AS segment FROM DUAL
              UNION ALL
              SELECT 'String 3' AS segment FROM DUAL
            ),
            ...
       GROUP BY ..., segment
     )
WHERE ...

Since the outermost query limits the data it uses from the innermost query (via WHERE), it seems like selecting those strings in the innermost query might be better because then the data gets pared down earlier. Is there any kind of best-practice guideline about when to SELECT data like this?

Edit: based on Example of Data Pivots in SQL (rows to columns and columns to rows) (see his section titled "From Two rows to Six rows (a column to row pivot):"), I was able to remove this:

SELECT 'String 1' AS segment FROM DUAL
UNION ALL
SELECT 'String 2' AS segment FROM DUAL
UNION ALL
SELECT 'String 3' AS segment FROM DUAL

Instead, I do:

SELECT ROWNUM rowno
FROM DUAL
CONNECT BY LEVEL <= 3

And then in my outermost query, I do:

CASE WHEN rowno = 1
     THEN 'String 1'
     WHEN rowno = 2
     THEN 'String 2'
     WHEN rowno = 3
     THEN 'String 3'
END AS segment

However, this still doesn't answer my question of where to put the inline view for SELECTing rowno: innermost or outermost query?

Answer 1

As I understand, if you do not use rownum in your inner query, then it should not matter where you put it as optimizer will just use it in the best way it can. That leaves you to decide which version is more readable to you.