Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Partitioned Indexes question

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 17 Mar 2004 19:13:59 -0500
Message-ID: <D91D9D5A73FC694BBC52F1EB26AD410F02780CB4@MSGBOSCLD2WIN.DMN1.FMR.COM>


The fact that the partitioned index is local is the reason to have restricted rowids while global partitioned indexes require extended rowids.

Also local indexes can be partitioned on columns that are not part of the indexed columns while global indexes are required to be partitioned on left prefix of the index column list.

Regards,

Waleed

-----Original Message-----
From: Tanel Poder [mailto:tanel.poder.003_at_mail.ee] Sent: Wednesday, March 17, 2004 4:46 PM
To: oracle-l_at_freelists.org
Subject: RE: Partitioned Indexes question

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.

Tanel.



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Mar 17 2004 - 18:12:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US