Testing SQL query on Oracle which includes a remote database

Question

Our development databases (Oracle 9i) use a remote database link to a remote shared database.

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.

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.

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.

e.g. (a simplified case):

   select a.col, b.col
   from a, b
   where a.b_id = b.id

• a is on the local database
• b is on the remove database
• I have a synonymn on the locale DB so that 'b' actually points at b@remotedb.

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).

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.

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?

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.

Answer 1

Copy the relevant data into your development database and create the tables locally.

Ideally, just build a test case which tells you:

1. The SQL is correct (it parses)
2. It operates correctly with a few rows of test data

Don't fall for the "let's copy everything" because that means you'll have no idea what you're testing anymore (and what you're missing).

If in doubt, create a table b with just a single record. If you get an error in this area, add more rows as you learn where it can fail.

If you want to take this to the edge, create the test table (with all data) in a unit test. This way, you can document the test data you're using.

[EDIT] What you need is a test database. Don't run tests against a database which can change. Ideally, the tests should tear down the whole database and recreate it from scratch (tables, indexes, data, everything) as the first step.

In this test database, only keep well defined test data that only changes by defining new tests (and not by someone "just doing something"). If you can, try to run your tests against an in-memory database.

Answer 2

I would suggest materialized views. These are views that store remote data locally.

Answer 3

You should create exact copies of all the schema you need from production on development but without all the data. You should populate the schema with enough data so you can do a proper test. You can also manipulate the optimizer to behave on the test system to be like production by exporting the statistics from the production server and importing them to the development database for the schemas you are duplicating. That way the query will run with the data set you've made but the query will optimize with plans that is similar to that of production. Then you can estimate theoretically how it will scale on production.

Answer 4

Hi A_M,

In theory to do the unit-testing you can work with any set of controlled data created and designed based on your test-cases. It doesn't have to be your live or development system. That's assuming your unit is portable enough. You would test it with your current databases/application when you come to integration testing, which might as well be on the live system anyway (so no db links will be required - I understand your live databases are in one place).

What I'm trying to say, is that you can/should test your unit (i.e. your component, query or whatever you define as a unit) on a controlled set of data that would simulate different 'use cases' and once you complete your testing to satisfactory results, then you can proceed to integration + running integration tests.

Integration tests - you could run this in the live environment, but only after you've proved by unit-testing that your component is 'bullet-proof' (if that's OK with your company's approach/philosophy :) - sys admin's reaction:"Are you flippin creazy?!")

If you are trying to go back in time and test already implemented units, then why bother? If they've been in a production use for some time without any incidents then I would argue that they're OK. However, there's always a chance that your unit/query might have some 'slowly ticking time bomb' effect on the side (cumulative effect over time). Well, analyse the impact is the answer.

Anyway, sorry if I'm being a smartass - that's not my intention. And I hope this will be of any use to your work.

Have a good day! Damo