There’s a company I work with (read, “for”) that seems to be having a bit of trouble getting 4M rows into a table. Typically this isn’t an issue, but the nature of the data, suggests Oracle support, is causing the index to be “unbalanced.” There’s a more technical term for the lack of balancing, and though I was scolded for using the term lopsided (Thanks, Tom), I still imagine the b-tree visually being lopsided. In any case, all of the signs point to the primary key as the issue. I’m dealing with a two column table, both of type “NUMBER” and both are used for the Primary Key. There’s about 500,000,000 rows in the table and I’m trying to add 4,000,000 rows. When diving in to super secret Oracle internals, I’ve found that almost all of the waits are of type “db file scattered read” and when run with the “e1″ parameter, the data file it’s after only houses one part of this table: the index which is the primary key. Interesting to say the least.

At this point, Oracle isn’t much help and things are getting worse, so I’ve decided if index maintenance is the issue, there’s gotta be a better way to store the index information.  Maybe partitioning the table with a hash partition and locally partitioned indexes… then the answer comes to me in the form of Oracle’s own documentation.

Globally partitioned indexes. Just like partitioned tables, a database can have partitioned indexes, local OR global.  Locally partitioned indexes require a partitioned table and, from what I can tell, the same partitioning parameters, whereas globally partitioned indexes can be on anything, even when on a partitioned table…. sounds messy.

Next up… Oracle Partitioned Indexes - Part 2 (choosing the partition type)