title: Oracle SQL Reuse sub query inside VIEW
body: <p>I am trying to set up a view which could potentially contain 1000's of records. Some of the fields requires subqueries which return the exact same data. I was wondering if I can query this once, within the view, instead of running it each time. </p>
  
  <p>Below is some sample tables / data:</p>
  
  <pre><code>DROP VIEW MYVIEW;
  DROP TABLE MYTABLE;
  DROP TABLE MYTABLE_PARENT;
  
  -- TABLES FOR VIEW
  CREATE TABLE MYTABLE_PARENT ( PRIMARY_KEY NUMBER PRIMARY KEY );
  CREATE TABLE MYTABLE ( PRIMARY_KEY NUMBER, MYVAL VARCHAR(255), PARENT_PRIMARY_KEY NUMBER);
  INSERT INTO MYTABLE_PARENT VALUES (1);
  INSERT INTO MYTABLE_PARENT VALUES (2);
  INSERT INTO MYTABLE VALUES (1, 'MYVAL1-1', 1);
  INSERT INTO MYTABLE VALUES (2, 'MYVAL1-2', 1);
  INSERT INTO MYTABLE VALUES (3, 'MYVAL2-1', 2);
  
  -- VIEW
  CREATE OR REPLACE FORCE VIEW MYVIEW AS 
    SELECT CS.PRIMARY_KEY AS PARENT_PK,
  
    -- THE BELOW STATEMENTS REUSE THE SAME INNER SUBQUERY, IF THE 
    -- VIEW CONTAINS 1000 ROWS, THE INNER SUBQUERY WILL BE EXECUTED 
    -- 1000 TIMES, RETURNING THE SAME DATA EACH TIME.
    (SELECT PRIMARY_KEY FROM MYTABLE WHERE MYVAL = 'MYVAL1-1' AND 
    PARENT_PRIMARY_KEY = (SELECT PRIMARY_KEY FROM MYTABLE_PARENT 
    WHERE PRIMARY_KEY = CS.PRIMARY_KEY)) AS OUTPUT_VAL_1,
  
    (SELECT PRIMARY_KEY FROM MYTABLE WHERE MYVAL = 'MYVAL1-2' AND 
    PARENT_PRIMARY_KEY = (SELECT PRIMARY_KEY FROM MYTABLE_PARENT 
    WHERE PRIMARY_KEY = CS.PRIMARY_KEY)) AS OUTPUT_VAL_2
  -- DEFINE CS
    FROM MYTABLE_PARENT CS;
  
  SELECT * FROM MYVIEW;  
  </code></pre>
  
  <p>The result from the select statement is the following:</p>
  
  <pre><code>PARENT_PK              OUTPUT_VAL_1           OUTPUT_VAL_2           
  ---------------------- ---------------------- ---------------------- 
  1                      1                      2                      
  2                                                                    
  </code></pre>
  
  <p>So, in the above query, i want to run the following query only once for each row in the view: </p>
  
  <pre><code>(SELECT PRIMARY_KEY FROM MYTABLE_PARENT WHERE PRIMARY_KEY = CS.PRIMARY_KEY)
  </code></pre>
  
  <p>Is there any way of optimizing the inner subqueries within the view? </p>