7

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) Plain Table Query Plan Bitmap Index: Bitmap Index query plan Partitions: Partition query plan

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?

1
  • I've updated my answer to respond to your update. Sep 21, 2012 at 5:45

3 Answers 3

5

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

3

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

6
  • Thanks, I've done a little test here with 1M rows: 500348 with TENANTID=2, 499652 with TENANTID=1 randomly distributed and, according to the QUERY PLAN, partitioned table has smaller cost (1112 vs 1521) compared to bitmap indexed one. How would you explain it? Sep 11, 2012 at 15:40
  • Can you post your query here. Sep 12, 2012 at 6:12
  • see the edit please Sep 12, 2012 at 8:14
  • 1
    A bitmap index might not be a good choice if the table is being updated frequently (and concurrently)
    – user1822
    Sep 12, 2012 at 9:33
  • @Tsar, I see that you have concluded that the Cost column that you had been looking at turned out to be unreliable, so I guess I don't have to explain anything to you anymore about rowids. Sep 12, 2012 at 16:22
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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