If a nonclustered, nonunique index is added to a partition table, is it automatically made local? Meaning does it index each partition individually or the whole table?
I'm having problems examining the structure of partitioned tables to confirm which indexes are local. Even when I know an index has been explicitly defined as local, the output from <sp_help> is not much different from indexes that were not specified as local.
What is the recommended way to query a table's schema to confirm it is partitioned and has local indexes?
Thanks!
1 Answer
-
Oct 26, 2016 at 02:22 PM
What version of ASE are you running?
Do you have a license for semantic partitioning (or running the developer edition)?
Have you created a semantically partitioned table and index(es)?
------------
Default behavior for create index is to create a global index. For a local index you need to add the local index clause.
sp_help[index] should have a column named 'index_local' to the far right that shows the index as being global or local:
Object has the following indexes index_name index_keys index_description ... index_local ---------- ---------- ----------------- ... ------------ idx2 id nonclustered ... Local Index idx3 name nonclustered ... Global Index
sp_helpartition <tablename>,<indexname> should also show the partition type as a global or local index:
1> sp_helpartition t1,idx2 2> go name type partition_type partitions partition_keys ---- ----------- -------------- ---------- -------------- idx2 local index range 4 id
Add comment
Add comment