design options to query from two different oracle databases

Question

We have a transactional database with just 2 weeks of data and another archive database which holds data older than 2 weeks. Both DBs share the same schema structure and are in separate servers. We have a reporting application which queries data from both these databases where the user selects which database he wants to query by using a dropdown selection. In order to improve user experience we are thinking to do away with the dropdown selection by making the DB selection transparent in the background. Below are the few options we had in mind

1. Use UNION for the 2 select queries via DB links
2. Query DB1 first and if no records query DB2

Since the data volume is more we are apprehensive about our choices. Appreciate if anyone has any other suggestions on how to approach this.

Answer 1

In my particular opinion, the best two choices are:

• always give the user the data newer than a relative date (e.g. the last three months of data).
• always give the user the last n data (e.g. the newest 250 rows).

Give all data will be inefficient when you have a big dataset.

But if you want to strengthen the autonomy and protect the user's work (two important design principles in user interfaces) then you must let the user configure the relative time or the number of data items desired. Or you can also let the user explore all/older data in particular situation (e.g. using a special window, a pagination system, a particular interface or a completly new use case).

Let's see two examples. (I assume that user is querying the server with newest data and OLD is the name of the dblink you use to reference the server with the data older than two weeks. I also assume that the target table is named DATATABLE and the column with the date is called DATADATE).

To retrieve the last three months (first choice):

SELECT * FROM DATATABLE
UNION ALL
SELECT * FROM DATATABLE@OLD WHERE MONTHS_BETWEEN(SYSDATE, DATADATE) >= 3;

And, to retrieve the last 250 rows (second choice):

SELECT * 
  FROM (SELECT * FROM DATATABLE ORDER BY DATADATE DESC) 
  WHERE ROWNUM <= 250;
UNION ALL
SELECT *
  FROM (SELECT * FROM DATATABLE@OLD ORDER BY DATADATE DESC)
  WHERE ROWNUM <= (250 - (SELECT COUNT(*) FROM DATATABLE));