Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in advance? Let's say you have a table AUDIT
with TenantId
column which could only containt values: 1, 2.
All SELECT
statements would have a WHERE
clause with a TenantId
parameter.
So, would it be beneficial to partition this table by TenantId? If so, would you also create an index on TenantId
column?
partition by list (TENANTID)
(
partition TENANT1 values (1),
partition TENANT2 values (2)
)
I have done a little experiment: inserted 1M records with TenantId randomly generated, thus creating:
- 499652 records with TenantId=1
- 500348 records with TenantId=2
Here are the query plans for statement:
SELECT * FROM table1 WHERE TENANTID=2
Plain Table (no indices, no partitions)
Bitmap Index:
Partitions:
Btw, in case where I have both, the index and partitions, query plan uses partition and not index, hence the plan looks exactly the same as the 2nd one shown above.
Clearly the partition wins, but does it? Apparently, the cost column in an execution plan is not a reliable way to judge the real costs of a SQL statements response time.
So, what is the best way then? How to choose one over another?