I have two huge tables on separate databases. One of them has the information of all the SMS that passed through the company's servers while the other one has the information of the actual billing of those SMS.

My job is to compare samples of both of these tables (for example, the records between 1 and 2 pm) to see if there are any differences: SMS that were sent but not charged to the user for whatever reason that may be happening.

The columns I will be using to compare are the remitent's phone number and the exact date the SMS was sent. An issue here is that dates usually are the same on both sides, but in many cases differ by 1 or 2 seconds.

I have, so far, two alternatives to do this:

  1. (PL/SQL) Create two tables where i'm going to temporarily store all the records of that 1hour sample. One for each of the main tables. Then, for each distinct phone number, select the time of every SMS sent from that phone from both my temporary tables and start comparing one by one using cursors. In this case, the procedure would be ran on the server where one of the sources is so the contents of the other one would be looked up using a dblink.

  2. (sqlplus + c++) Instead of storing the 1hour samples in new tables, output the query to a text file. I will have two text files, one for each source. Then, open the first file and load all of it's content on a hash_map (key-value) using c++, where the key will be the phone number and the value a list of times of SMS sent from that phone. Finally, open the second file, grab each line (in this format: numberX timeX), look for numberX's entry on the hash_map (wich will be a list of times) and then check if timeX is on that list. If it isn't, save it somewhere to finally store it on a "uncharged" table (this would also be the final step on case 1)

My main concern is efficiency. These samples have about 2 million records on each source, so just grabbing one record on one side and looking it up on the other would not be possible. That's the reason I wanted to use hash_maps

Which do you think is a better option?

  • do you think you could add a few sample rows from the two sources, enough so that a proposed solution for the sample would solve your problem? Jun 1, 2012 at 21:38

5 Answers 5


If the databases are on the same server, you can simply run a query against both databases.

If not, you could use Oracle Database Gateways (if you have it) to connect to both, and similarly do the comparison.

  • And what about the options I suggested? which you think would perform better?
    – Alex
    Jun 1, 2012 at 20:52
  • Probably option 2. You don't necessarily need c++ to do it, either. Just SQL to two files and then a diff utility. Jun 1, 2012 at 22:28

At first blush, your second option is probably going to be superior.
Here's my line of thinking and assumptions.

0) I'm assuming you don't have a massive DB system sitting idly by waiting for a query like this to justify its purpose.

1) You're likely going to have run this multiple times. If nothing else, once discrepancies in the bill processing have been resolved, this audit process will be needed to validate the fixes.

2) Parallel operations will be key in getting reasonable run times. The second option you suggest will allow you to break things out into chunks of individual "sent" listings and "billed" mappings. Those chunks can be farmed out to distributed clients for faster processing time.

3) C++ code will give you more options in handling the time discrepancies and subsequent (re-)processing of those entries.


Here's a very efficient way to compare two queries (generalized solution for the problem of comparing two tables efficiently):

  SELECT foo1 col1, foo2 col2 FROM ...
  SELECT bar1 col1, bar2 col2 FROM ...
) t
GROUP BY col1, col2

This query takes two sub-queries that are expected to be identical, and the output is the rows that are only in one of the queries. In other words, if the two sub-queries return identical rows, the output should be empty.

I needed to compare two large data sets in the past, where performance was an important concern, and this was by far the fastest method I found.

You mentioned the timestamps may have a few seconds difference. To hide that noise, I recommend to use some kind of date rounding, but I don't know the syntax for that.

  • And which of the alternatives I suggested you think is a better choice?
    – Alex
    Jun 1, 2012 at 20:43
  • You should take a moment and consider the query I proposed, it should be more efficient than the two solutions you offered. But the first method you propose is really not good at all, because processing records one by one is inefficient, and would impact your live databases. Your second solution will be faster, and it won't impact much your live databases.
    – janos
    Jun 2, 2012 at 6:04

Two million records shouldn't be that big a deal, especially if phone number is indexed. So your time is probably more important than the query time. Heck, I would probably create a table and import all the records from one database to the other if I couldn't otherwise reference both in one query.

So, I'll assume you can reference both tables in one query.

The logic for matching within the 2 second window is somewhat tricky. For example what if there was an SMS at 12:00:01 and 12:00:05 and a bill for 12:00:03? Really, one of the two hasn't been billed.

So what you really want as a first step is to match SMS message count and messages billed count for a phone number. Later you can figure out which one wasn't billed (or possibly not have enough information to do so precisely, as in my example).

We can do that:

SELECT A.PhoneNumber,SentCount,BilledCount 
     (SELECT SentMessages.PhoneNumber, Count(SentMessages.SentDate) as SentCount
        FROM SentMessage
    GROUP BY SentMessages.PhoneNumber) as A JOIN
      (SELECT BilledMessages.PhoneNumber, Count(BilledMessages.BilledDate) as BilledCount
        FROM BilledMessages
    GROUP BY BilledMessages.PhoneNumber) AS B
          ON A.PhoneNumber=B.PhoneNumber
       WHERE SentCount>BilledCount

And we might as well run this on the entire table as a sample - except that maybe some messages aren't actually supposed to have been billed yet, so we don't want those. That's outside the scope of the question anyway.


I suggest creating a database link between the two databases, it lets you access a remote table as if it where in the same database.

Once you have the database link, code the solution in a PL/SQL store procedure.

I suggest PL/SQL Developer as the IDE for writing store procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.