I'm currently working on a large Database System. There are some rather
large transaction tables, in particular general ledger and journal tables
that take on millions of records per month. We are running into space
problems and major performance problems with queries,
inserts/updates/deletes. A lot of time has been spent on finding suitable
indexes for the tables and examining explain plans with only marginal
success.
I've been asked to look into database partitioning, a concept I'm not
familiar with. I'm not sure whether it is a logical or physical partitioning
of the database. I have no idea how to set it up. The only thing I know is
that we want to partition our transaction data by fiscal_year and
accounting_period in our large general ledger tables.
If anyone can shed some light or point me in the right direction (url, book,
whatever) I'd be most grateful. I've done a google search but can only find
theoretical treatments on the subjects. I'm looking for something more
practical.
In addition to the above there's a requirement for fitting this into an
archival strategy for this system - the basic idea is to peel off the older
data at regular intervals and assign the recovered partitions to new
transaction data.
Thanks,
Tim
