Re: global index and local index

Not quite right. You can have partitioned global indexes. And an index which is unpartitioned is obviously global, though the standard appears to be to call that an "unpartitioned index" rather than a global one.

The key determinant is that in a local index, you can guarantee that the index entries from one index partition point to one and only one table partition. In a global index, the entries from a single index partition might point to one, two or more table partition rows.

With that in mind, your statement about what makes a local index (it's partitioned in the same way as the table) is exactly correct -though, of course, the index does not have to have the partitioning column as its leading column (hence we have local prefixed and local non-prefixed indexes).

The significance of the distinction between local and global is simply that various table partition DDL statements will invalidate the indexes built upon those partitions. If the index is local, then only that local partition will be invalidated (because only the one index partition contains entries pointing at the table partition affected by the DDL). But if the index is global, the entire index is invalidated by the same sort of commands (because many index partitions reference the one table partition).

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Uwe Schneider" <uwe_at_richard-schneider.de> wrote in message
news:3C2EF6C2.2B1CA835_at_richard-schneider.de...


> frank wrote:

> >

> > Hello,

> >

> > Does global index and local index concept used only in partition?

>

> I have heard about them only in conjunction with partitions.

>

> > Please give me an explanation on what's the difference of them.

>

> Local indexes are partitioned in the same manner as the table segment,

> i.e. the index data are in the same partition as the corresponding table

> data. Global index segments are unpartitioned.

>

> Uwe

>

> --

> Uwe Schneider       | Telefon +49 7244 / 609504

> Haydnstr. 1         | Mail    uwe_at_richard-schneider.de

> DE-76356 Weingarten | http://www.richard-schneider.de/uwe

> Linux - OS al dente!