RE: Partitioned Indexes question
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Mar 2004 19:13:59 -0500
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@xxxxxxx]
Sent: Wednesday, March 17, 2004 4:46 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
