Solved

indexes

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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
This video shows how to recover a database from a user managed backup

757 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

18 Experts available now in Live!

Get 1:1 Help Now