It turns out that a lot of the headaches of the past few weeks at work are all due to an incredibly shitty primary key on a large warehouse table. Before I get into specifics there, I'd like to formally proclaim my allegiance to the use of surrogate keys and my hatred for compound keys. Many of the Oracle "gurus" (including Tom Kyte) agree as well. You can use the Google to find reasons to use them. I'll list smaller indexes on both the table in question AND tables with foreign keys as a big reason off the top.

Now, on this table at work, which I'll call FOO, there is currently a 10-field compound primary key. Yeah, that's right. I take some shame in having this in my database, although the design was inherited, and I'm just now feeling comfortable and confident enough to start suggesting radical changes to our developers. So this primary key begins with columns LOC, CUST_NO, YEAR, and MONTH. Then it includes 5 fields that are rarely, if ever queried (I turned on FGA on this table and verified that). Then the last field is the relatively new FOO_SEQ_NO, which is unique for a LOC, CUST_NO, YEAR and MONTH. Virtually every query against this table uses LOC, CUST_NO, YEAR and MONTH. FYI, this table is also range partitioned on YEAR and MONTH.

The problem is that after we load a new large batch of records from a previously unloaded market into the current partition, the optimizer no longer uses the big PK index, instead choosing to use a second index which doesn't even use CUST_NO. This causes a LOT of I/O and painfully slow query times, until we are able to gather_table_stats() on it, which can take up to 2 hours.

What became painfully obvious after I turned on fine-grained auditing, is that 95% of the queries only use LOC, CUST_NO, YEAR and MONTH, so the massively compound primary key index is almost 50% useless. On a development instance, I dropped the PK constraint and index and built a new non-unique index on just LOC, CUST_NO, YEAR and MONTH. I do plan to add a unique constraint on the former PK fields just to keep things sane in case some part of the application relied on that. The sample query ran fine before and after the dataload, using the new index right away without having to gather stats.

Now, the case of the FOO table was an EXTREME of an ugly compound key/index, and the problem wasn't so much the compoundedness of it as much as the index not being reflective of what user queries needed. But I still loathe compound keys.