1 post tagged “union query”

I was writing some SQL scripts during the week to migrate our production Oracle database schema so that it will function with some of the new web site functionality that we have been implementing.

Our site uses some complex button and function permissions that are all stored in a single table in our Oracle schema, accessed via an Oracle view.  We decided to perform some normalisation of these tables to accomodate some new functionality and I wanted to make sure that the "functional" content of this view was exactly same before and after the refactoring process.  I basically needed an easy method to compare the content of the view before and after my refactoring script had executed.  I didn't want to spend hours manually checking each function permission record.

My solution was to use a Union query.  Basically the idea is to drop the original contents of the view into a temporary table so that you have a snapshot of it's content before you perform any table and data modifications.  Once you have that stored away safe and sound, perform the modifications to your tables and data, and then run a union query between the modified view and the original data.  The key thing here is the record count.  Count the number of records in the snapshot table and note it down.  Then perform a union query between snapshot table and the refactored view.  If the content of the snapshot table is exactly the same as the view, then the record count of the union query should be exactly the same as the first record count on the snapshot table.  This will give you a very quick way of performing a high level comparison of the two tables.

In my case, the web application queries it's function permissions from an Oracle view.  We were modifying the underlying table structure and content, but the content of the view should remain untouched from the web app's perspective.  So these are the steps that I followed to guarentee that our table and data refactoring had not effected the content of the view.

These are the steps thay I followed:

1. Create a temporary table with the same columns as the view
2. Copy the data from the view into the temporary table - this basically is your benchmark
3. Execute the DDL and DML sql code to perform the table and data refactoring
4. Fix the view so that it now works with the refactored tables
5. Perform a record count of the temporary table
6. Perfom a union query between the temporary table and the refactored view, include a record count in the query
   
7. If the two record counts are the same then you know that your refactoring was successful

This works because a Union query will only include records in the result set that are unique.  If each record in the view is the same as a record in the temporary table, only one copy of that record will be returned by the union query.