Solved

indexes

Posted on 2011-03-15
5
388 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
  • 3
  • 2
5 Comments
 
LVL 73

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 73

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 73

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now