We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment.
Create tenant specific aggregate tables, and query from those.
Horizontally partition the data based on tenant.
The first option means that for every new tenant that comes onboard, we'll need to create a new set of tables. This isn't that difficult, as a new tenant signing up comes with a few weeks notice, and a lack of reporting will be revealed early on if we forget.
Partitioning the data to me sounds like a better approach, since we're not duplicating the data. We don't have to rely on a process to transfer new data to the aggregate tables.
I'm wondering which of these options would be better, if anyone has had similar experiences before. Does partitioning the data actually help? Or would it be not so much different from leaving all the data in one 'space'?
And, with Oracle 10g, how does one horizontally partition data? If I had the following table:
TABLE Transaction(id, tenant_id, a, b, c, d)
We'll be migrating to Oracle 11g soon, so any differences in partitioning across versions would be appreciated.
(Note: I tried to use a partitioning tag, but not enough rep, if someone else could add a tag that'd be cool)