  
|
Login/Register | Contact Us | 1+ 978-528-4660 |
Should partition key be included in secondary index ?
Tue, 02/07/2012 - 15:05
If a table is partitioned by column A and I wish to have an index on column B, should the secondary index by on (A,B) or just on B ?
Tue, 02/07/2012 - 16:22
#1
Hi,
You can put the index on
Hi,
You can put the index on just B. You do have to be aware of what you are doing because from a single partition procedure the index won't contain entries for rows in other partitions and will contain rows for each value of B that may be from values of column A that are different from the one used to route your stored procedure.
It might help to know more about what your are trying to index on and what query you want to use the index for.
-Ariel
Wed, 02/08/2012 - 09:46
#2
Re: Secondary Index
The query will always include both A and B in the where clause. So I think what you mentioned (index on just B) should suffice.
Is there a scenario in general, in which one would want to put index on both A and B ?
Thanks.
Wed, 02/08/2012 - 10:18
#3
Hi,
If you only make an index
Hi,
If you only make an index on B but query on A,B then the index scan will have to scan all rows that match B even if they have the wrong value of A. If A,B is a primary key you could declare that and that would generate an index. If B is unique for each A then you would save some space in the index by not including A.
An index scan can only use the index up until it is covering so A = ?, ( B >[=] ? | B <[=] ? | B = ?) could use the index, but if the predicate for A is not equality then the index can't be used.
-Ariel
Wed, 02/08/2012 - 10:27
#4
In your case, if column B is
In your case, if column B is non-unique and it has low cardinality, then having an index on both A and B would help.
In other cases, if an order by on both A and B is required, having a tree index on these two columns would save a sort because an index scan preserves the order.
