Skip to Content
avatar image
Former Member

In a partitioned table, are indexes "local" by default?

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

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
    10|10000 characters needed characters exceeded