title: SQL Performance on joining 2 scripts ORACLE
body: <p>I have 2 scripts which are quite complicated, one was written by me personally, another was done 10 years ago. The first script gets necessary id's and executes in around 30 sec, example:</p>
  
  <pre><code>| ID | some other info ...
  +----+--------------------
  | 1  |       ...
  | 2  |       ...
  | 3  |       ...
  | 4  |       ...
  </code></pre>
  
  <p>The second script get's some more complicated data, which is calculated through many sub queries, and executes in around 30 sec, ex:</p>
  
  <pre><code>| ID | Computed Info
  +----+--------------------
  | 1  |       111
  | 2  |       222
  | 3  |       333
  | 4  |       444
  </code></pre>
  
  <p>Now my script1 needs to include some partial results from script2, because the script2 is very complicated it is quite hard to break down the necessary parts, that is why I have tried to left join results of the script2 to the script1 using ID's</p>
  
  <pre><code>SELECT TABLE1.*, TABLE2.COMPUTED_INFO FROM SCRIPT1 TABLE1 LEFT JOIN SCRIPT2 TABLE2 ON TABLE2.ID = TABLE1.ID
  </code></pre>
  
  <p>The result I got and also the expected result is:</p>
  
  <pre><code>| ID | some other info ... | Computed Info
  +----+---------------------+---------------
  | 1  |       ...           |  111
  | 2  |       ...           |  222
  | 3  |       ...           |  333
  | 4  |       ...           |  444
  </code></pre>
  
  <p>The problem is that after joining both of them the time of execution is now 20+ min.</p>
  
  <p>I have also tried </p>
  
  <pre><code>with table1 as 
               (script1), 
       table2 as 
               (script2) 
  select t1.*, t2.computed_data 
  from table1 t1 left join table2 t2 on t2.id = t1.id
  </code></pre>
  
  <p>Which resulted in 10+ min. 
  I am wondering why such thing occurs, when definitely script1 and script2 separately run in around 30 sec each, but when run together go as much as 10+
  Is there another way to accomplish that?</p>