RE: Partitioned Indexes question
- From: "Tanel Poder" <tanel.poder.003@xxxxxxx>
- To: "" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Mar 2004 23:46:12 +0200
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.
Other related posts:
