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.

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?

share|improve this question
    
I've updated my answer to respond to your update. –  Florin Ghita Sep 21 '12 at 5:45
add comment

migrated from stackoverflow.com Sep 5 '12 at 15:06

This question came from our site for professional and enthusiast programmers.

2 Answers

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

share|improve this answer
    
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? –  Tsar Sep 11 '12 at 15:40
    
Can you post your query here. –  Henrique Ordine Sep 12 '12 at 6:12
    
see the edit please –  Tsar Sep 12 '12 at 8:14
    
A bitmap index might not be a good choice if the table is being updated frequently (and concurrently) –  a_horse_with_no_name Sep 12 '12 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. –  Henrique Ordine Sep 12 '12 at 16:22
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

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.