Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

  1. Create tenant specific aggregate tables, and query from those.

  2. 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)

share|improve this question
add comment

1 Answer

up vote 7 down vote accepted

To answer your second question first: yes you should partition. Oracle's query optimizer has a feature called partition elimination, which will check the predicate for the partition and only execute the SQL on the appropriate partitions.

Partitioning also leaves all the data in one space. Conceptually, think of it as many tables of identical structure, with an implicit UNION ALL between them if you were to do a SELECT from the entire table. Except "under the hood" Oracle sorts the actual rows into the right "table" based on the criteria you specify. Any rows that come in that don't match any of the criteria, go into what's known as the "default" partition.

For what you want to do, a "range partition" might be a good approach (so you can add more tenants later), e.g.:

create table transaction (id, tenant_id, a, b, c, d)
partition by range(tenant_id)
partition p_tenant1 values less than (2) tablespace ts_tenant1
partition p_tenant2 values less than (3) tablespace ts_tenant2
partition p_tenant3 values less than (4) tablespace ts_tenant3
partition p_tenantd values less than (MAXVALUE) tablespace ts_default;

Then later

alter table transaction 
add partition p_tenant4 values less than (5) tablespace ts_tenant4;

This will create something that looks and behaves just like a normal table, but actually rows where tenant_id=1 will be in a partition in tablespace ts_tenant1, and queries will ignore all other partitions. Queries across the entire table can run in parallel on each partition. If tenant_id=4 in this scenario, the row will live in ts_default unless you add the new partition as shown, but the INSERT won't be rejected because there's no partition for it!

FWIW At my site we use partitioned tables in our 40Tb DW, you don't need to worry about this approach scaling or performing, if you choose your partitioning strategy well (e.g. you could partition on tenant_id then subpartition on month perhaps), create the right indexes, and so on.

share|improve this answer
Thank you, this information is really helpful. I'm wondering if there are any glaring 'gotchyas' with this approach? Do indexes still work the same way? Things like that. Alternatively, is there any good online reading material that you could point me to? I've done some searching, but does not look like there is a great amount of good writings on the subject. –  Josh Smeaton Mar 31 '11 at 9:41
The "partitioning concepts" section of the manual I linked to is a good place to start. Partitions also let you do other cool stuff, say you want to purge data older than a certain age and you have partitions by date, just drop the partition which is logically a TRUNCATE not a DELETE so it's nice and quick. There are two kinds of indexes you can use, that go across the entire table, or are per-partition, as ever the right indexes to use will depend on your access pattern. If you have queries that can execute within one partition, you would want partitioned indexes for example. –  Gaius Mar 31 '11 at 11:34
BTW partitioning is what MySQL types mean when they talk about "sharding" but they didn't invent it like they think, it's been around for decades (except done properly, not re-inventing the wheel to sort rows into partitions in your own code, maintaining x open DB connections etc). And since they don't have hash joins, they aren't missing anything by not being about to run queries across partitions. –  Gaius Mar 31 '11 at 11:55
The two biggest gotchas to partitioning are indeed 1) indexing: global indexes vs partition local indexes, see docs.oracle.com/cd/B28359_01/server.111/b32024/… and 2) licensing. It's an extra cost option; just because you have a license for Enterprise Edition doesn't mean you have a license for partitioning. –  Colin 't Hart Jul 26 '12 at 9:47
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.