Solved

indexes

Posted on 2011-03-15
5
392 Views
Last Modified: 2012-05-11
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
Comment
Question by:msimons4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35141124
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35141137
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
 

Author Comment

by:msimons4
ID: 35141201
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 35141270
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
 

Author Closing Comment

by:msimons4
ID: 35141326
Thanks
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question