Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Partitioned Indexes question

RE: Partitioned Indexes question

From: Tanel Poder <>
Date: Wed, 17 Mar 2004 23:46:12 +0200
Message-Id: <>

On Tue, 2004-03-09 at 08:11, Lim, Binley wrote:
> If an index is partitioned on the same column(s)
> as the table, then the index is local. If you partition
> on different columns, then the index is global, and you
> have to specify the range in the index-create statement.

That's not true, at least in this wording. You can have a GLOBAL index partitioned exactly on same columns as the table, also equi-partitioned with the same value ranges as the table. If you state an index is GLOBAL, it will be GLOBAL, Oracle won't convert it to LOCAL by itself.

What makes an index GLOBAL or LOCAL then? It's the ability to address rows in multiple segments. A local index can have only 6-byte restricted rowids in it, thus making it unable to uniquely address rows in different tablespaces (since restricted rowid has tablespace relative file numbers in it, not completely unique across the whole database).

This is not a problem with non-partitioned tables, since a table's segment can belong to one and only one tablespace anyway, but with GLOBAL partitioned indexes on partitioned tables (where different segments may be in different tablespaces) an index stores 10-byte extended rowids, which means that referenced row's data_object_id is also stored in index block with regular rowid information.

Using data_object_id, it's possible to get the tablespace information out of data dictionary or dictionary cache and go get the row.

So a global index is an index which is able to reference rows in multiple segments, but requires more space, since additional bytes for getting the tablespace information are required for each index entry.


Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Wed Mar 17 2004 - 15:43:39 CST

Original text of this message