Solved

indexes

Posted on 2011-03-15
5
387 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Processing of multiple cursor 6 35
Queries 15 34
How to free up undo space? 3 29
Oracle - SQL Parse String 5 20
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

867 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

21 Experts available now in Live!

Get 1:1 Help Now