Is it worth partitioning a Table by single value in Oracle?

Question

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?

Answer 1

My opinion is that partitioning of that table in the manner presented may be useful.

Your queries will be two times faster (in the case when partitions are almost equal) if they make full scans.

In case your queryes have another tone of filters/conditions and use indexes, partitioning is not useful, because the level of an index is almost not affected by doubling the number of values.

UPDATE: For the test you did (SELECT * FROM table1 WHERE TENANTID=2) it is sure that partitioning is best. The bitmap solution need to scan the index, and after this, to scan all table's blocks containing rows with tenantid=2(it knows what are the rows). But partitioning will cause just scanning the table's partition with tenantid=2. They are phisically separated to tenantid=1.

So, two scans(index scan + table scan) vs one table scan(wich may be smaller).

Answer 2

When a column contains few distinct values, a.k.a. low cardinality, as in your case, you should create a Bitmap index on it. When you frequently query by that column, that is.

Here's more about Oracle Bitmap Indexes: http://www.dba-oracle.com/oracle_tips_bitmapped_indexes.htm

Answer 3

It can be very useful on a column with unevenly distributed data.

For example you have column status with values ACTIVE and HISTORY. The HISTORY partition might be much bigger but much less frequently queried than the AVTIVE partition.