Log table with millions of rows. Now, what?
I have a Log table with millions of rows. I’m thinking about separating the data into multiple tables (i.e. LoginHistory, ExceptionHistroy, PaymentProcessingHistory, etc.) What is the term used when taking a large table with many rows (not Columns) and creating multiple tables?
My current Log table schema resembles: LogID, LogMessage, LogReason, LoggedBy, LoggedOn, etc.
The problem is I’m putting too many things in one table, I think? Perhaps the table is too generic?
3 Solutions collect form web for “Log table with millions of rows. Now, what?”
It is called data partitioning.
Sharding is the term de jour. From the link:
Horizontal partitioning is a database design principle whereby rows of a database table are held separately, rather than splitting by columns (as for normalization). Each partition forms part of a shard, which may in turn be located on a separate database server or physical location.
I think you should be looking at horizontal partitioning. Horizontal partitioning is more or less a subset of sharding.
For more details on horizontal partitioning, here’s the wikipedia link:
You didn’t mention which DB technology you use, but here are some technical links that can get you going with partitioning your data:
IBM DB2 partitioning
SQL Server partitions
Sybase ASE 15.0 partitioning