StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PObest time to SELECT value in Oracle nested query
text
Body
copied!<p>From an optimization standpoint, I want to know when it's best to <code>SELECT</code> some arbitrary data as columns. Specifically, I have this query:</p> <h2>Where do I put this part?</h2> <pre><code>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 </code></pre> <h2>In Outermost Query</h2> <p>I need access to this <code>segment</code> column in my outermost <code>SELECT</code> and so it seems like I should <code>SELECT</code> it only when it it's necessary, i.e.</p> <pre><code>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 ... </code></pre> <h2>Versus In Innermost Query</h2> <p>However, is that better than <code>SELECT</code>ing my arbitrary strings in the innermost query?:</p> <pre><code>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 ... </code></pre> <p>Since the outermost query limits the data it uses from the innermost query (via <code>WHERE</code>), 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 <code>SELECT</code> data like this?</p> <p><strong>Edit:</strong> based on <a href="http://www.orafaq.com/node/1871" rel="nofollow noreferrer">Example of Data Pivots in SQL (rows to columns and columns to rows)</a> (see his section titled "From Two rows to Six rows (a column to row pivot):"), I was able to remove this:</p> <pre><code>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 </code></pre> <p>Instead, I do:</p> <pre><code>SELECT ROWNUM rowno FROM DUAL CONNECT BY LEVEL <= 3 </code></pre> <p>And then in my outermost query, I do:</p> <pre><code>CASE WHEN rowno = 1 THEN 'String 1' WHEN rowno = 2 THEN 'String 2' WHEN rowno = 3 THEN 'String 3' END AS segment </code></pre> <p>However, this still doesn't answer my question of where to put the inline view for <code>SELECT</code>ing <code>rowno</code>: innermost or outermost query?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload