• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

indexes

I am partitioning a table that has indexes. Indexes for partitioned tables are either local or global. How do I determine if the index for my non-partitioned table is local or global?
0
msimons4
Asked:
msimons4
  • 3
  • 2
1 Solution
 
sdstuberCommented:
local/global doesn't apply to non-partitioned tables

If you want to pick one, then "global", but it doesn't really make sense to call it that
0
 
sdstuberCommented:
the reason local/global doesn't apply is

local means the index is partitioned into pieces that correspond to the table partitions
global means it applies to the whole table


since there can't be "local" in non-partitioned it must be global, but it's sort of redundant to say so
0
 
msimons4Author Commented:
Great. So if my non-partitioned table has an index on 6 columns, is  it better to use a global index on all 6 columns or a local on each column or do I not even have a choice. Am I able to have a global index on columns that are in different partitions?
0
 
sdstuberCommented:
the choice of builiding a concatenated index of multiple columns is completely independent of whether a table is partitioned or not or if the indexes will be global or local when there are partitions.

but again, since your table is not partitioned you don't have global or local indexes,  you just have "indexes".  If you feel you must give them a label then global but you should break yourself of that habit.

if your table has an index on 6 columns, "better" is determined entirely by how the table will be used

if you query the table by all 6 columns or by the leading columns of it then it's probably fine as is.

if you query your table by the trailing columns then you might want to consider reordering the index.

if you query sometimes by leading and sometimes by trailing then you might want 2 indexes (or more depending on the exact usage combinations)  but you don't want to create too many indexes as they will impede your dml

0
 
msimons4Author Commented:
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now