SQL aggregation question

Question

I have three tables:

unmatched_purchases table:
unmatched_purchases_id --primary key
purchases_id --foreign key to events table
location_id --which store
purchase_date
item_id --item purchased

purchases table:
purchases_id --primary key
location_id --which store
customer_id

credit_card_transactions:
transaction_id --primary key
trans_timestamp --timestamp of when the transaction occurred
item_id --item purchased
customer_id
location_id

All three tables are very large. The purchases table has 590130404 records. (Yes, half a billion) Unmatched_purchases has 192827577 records. Credit_card_transactions has 79965740 records.

I need to find out how many purchases in the unmatched_purchases table match up with entries in the credit_card_transactions table. I need to do this for one location at a time (IE run the query for location_id = 123. Then run it for location_id = 456) "Match up" is defined as:

1) same customer_id
2) same item_id
3) the trans_timestamp is within a certain window of the purchase_date
  (EG if the purchase_date is Jan 3, 2005 
  and the trans_timestamp is 11:14PM Jan 2, 2005, that's close enough)

I need the following aggregated:

1) How many unmatched purchases are there for that location

2) How many of those unmatched purchases could have been matched with credit_card_transactions for a location.

So, what is a query (or queries) to get this information that won't take forever to run?

Note: all three tables are indexed on location_id

EDIT: as it turns out, the credit_card_purchases table has been partitioned based on location_id. So that will help speed this up for me. I'm asking our DBA if the others could be partitioned as well, but the decision is out of my hands.

CLARIFICATION: I only will need to run this on a few of our many locations, not all of them separately. I need to run it on 3 locations. We have 155 location_ids in our system, but some of them are not used in this part of our system.

Answer 1

try this (I have no idea how fast it will be - that depends on your indices)

  Select Count(*) TotalPurchases, 
     Sum(Case When c.transaction_id Is Not Null 
          Then 1 Else 0 End) MatchablePurchases
  From unmatched_purchases u
     Join purchases p 
        On p.purchases_id = u.unmatched_purchases_id
     Left Join credit_card_transactions c
        On customer_id = p.customer_id
           And item_id = u.item_id 
           And trans_timestamp - purchase_date < @DelayThreshold
  Where Location_id = @Location

Answer 2

At least, you'll need more indexes. I propose at least the folloging:

An index on unmatched_purchases.purchases_id, one on purchases.location_id and another index on credit_card_transactions.(location_id, customer_id, item_id, trans_timestamp).

Without those indexes, there is little hope IMO.

Answer 3

I suggest you to query ALL locations at once. It will cost you 3 full scans (each table once) + sorting. I bet this will be faster then querying locations one by one.

But if you want not to guess, you at least need to examine EXPLAIN PLAN and 10046 trace of your query...

Answer 4

The query ought to be straightforward, but the tricky part is to get it to perform. I'd question why you need to run it once for each location when it would probably be more eficient to run it for every location in a single query.

The join would be a big challenge, but the aggregation ought to be straightforward. I would guess that your best hope performance-wise for the join would be a hash join on the customer and item columns, with a subsequent filter operation on the date range. You might have to fiddle with putting the customer and item join in an inline view and then try to stop the date predicate from being pushed into the inline view.

The hash join would be much more efficient with tables that are being equi-joined both having the same hash partitioning key on all join columns, if that can be arranged.

Whether to use the location index or not ...

Whether the index is worth using or not depends on the clustering factor for the location index, which you can read from the user_indexes table. Can you post the clustering factor along with the number of blocks that the table contains? That will give a measure of the way that values for each location are distributed throughout the table. You could also extract the execution plan for a query such as:

select some_other_column
from   my_table
where  location_id in (value 1, value 2, value 3)

... and see if oracle thinks the index is useful.