I haven't had many opportunities for the past couple of months to write code, something I sorely miss. Since I changed jobs, I've been doing mostly analysis (aka learning all about the data). I think the last time I wrote any PL/SQL was March...wow.
One thing I have been tasked with doing is re-designing how we process payments. Many of the core tables have been bastardized in that entities are mixed up with attributes...or at least I think that's how you describe it. I'll show you an example and maybe someone can tell me what I am trying to explain.
Let's take a lookup table like payment types. You'd have Check, ACH and Credit Card. I'm sure there are others, I'm just trying to get the point across. Fairly simple right? Well, what has happened is that the business has expanded and in order to handle it, new payment types were created. Now we have Company 1 Check, Company 1 ACH and Company 1 Credit Card. Those lookup records have now been imbued with a different meaning. What's the term for that?
Oh, you were interested in the title? I'm getting there.
We store a payments in a "transaction" table with a few different lookup tables (like payment types) hanging off of it. We're not talking about Visa volume or anything, but a modest multiple of 10 thousand transactions a day. We hope to be getting a lot more soon. When considering the design of these types of tables, I have tended towards denormalization. You want to get in and out real quick without the need to lookup (I typically use codes for the lookup keys as opposed to the number, "ACH" or "CREDIT_CARD" for example) or write data to a bunch of different tables. One INSERT and done.
Hold on, I'm almost there.
As I was analyzing the data, I found that many of the columns I was grouping on or trying to filter on were not indexed. I suppose someone could make the case that leaving indexes off of this type of table would be good. My intuition is that they should be indexed, that the "overhead" of the index is nominal, but of course I have no proof one way or another.
I'd like to know what the DBAs out there think. A small aside, I consider myself a "dba" (note the lowercase) in that I have some crossover skills, but they tend to be from the application standpoint. "DBA" are people that keep the database running all the time. The ones that can perform a recovery (I can do backups, but I have yet to perform a recovery). The ones that love to dig into the internals of Oracle to see what's going on.
What do the DBAs think? Index a high volume table or leave them off? Either way, please explain yourself so I might learn something. ;)