title: Testing SQL query on Oracle which includes a remote database
body: <p>Our development databases (Oracle 9i) use a remote database link to a remote shared database.</p>
  
  <p>This decision was made years ago when it wasn't practical to put some of the database schemas on a development machine - they were too big.</p>
  
  <p>We have certain schemas on the development machines and we make the remote schemas look local by using Oracle's database links, together with some synonyms on the development machines. </p>
  
  <p>The problem I have is that I would like to test a piece of SQL which joins tables in schemas on either side of the database link.</p>
  
  <p>e.g. (a simplified case):</p>
  
  <pre><code>   select a.col, b.col
     from a, b
     where a.b_id = b.id
  </code></pre>
  
  <ul>
  <li>a is on the local database</li>
  <li>b is on the remove database</li>
  <li>I have a synonymn on the locale DB so that 'b' actually points at b@remotedb.</li>
  </ul>
  
  <p>Running the query takes ages in the development environment because of the link. The queries run fine in production (I don't think the Oracle cost based optimiser can cope very well with database links).</p>
  
  <p>We have not been very good at writing unit tests for these types of queries in the past - probably due to the due to the poor performance - so I'd like to start creating some tests for them.</p>
  
  <p>Does anyone have any strategies for writing a unit test for such a query, so as to avoid the performance problems of using the database link?</p>
  
  <p>I'd normally be looking at ways of trying to mock out remote service, but since all this is in a SQL query, I can't see anyway of easily mocking out the remove database. </p>