RE: Local Prefix vs non-prefix indexes

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Wed, 29 Nov 2000 11:04:20 -0500
Message-Id: <10695.123206@fatcity.com>

My take on that statement is they really meant:
> "Local nonprefixed indexes provide more availability than prefixed =
indexes
> when partition maintenance operations are carried out."

"Local indexes provide more availability than global prefixed indexes = when
partition maintenance operations are carried out"

Partition Maintenance Operations are adding, merging, splitting, = dropping of
individual partitions.

If you use a global prefixed index, then when you perform a partition mainenance operation, the while index must be rebuilt to drop the = dropped
index blocks or update the "updated" rowids (in the case of a partition merge or split)

> -----Original Message-----
> From: Boivin, Patrice J [mailto:BoivinP_at_mar.dfo-mpo.gc.ca]
> Sent: Wednesday, November 29, 2000 10:31 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Local Prefix vs non-prefix indexes

>=20
>=20

> I sat through the Oracle8i New Features for Administrators=20
> course, but I
> don't know much.

>=20

> Here is my take on this:

>=20

> Prefixed indexes start with the partition key.
> I don't know what Oracle means by "maintenance", but if it=20
> relies on the
> prefixed index to locate data blocks, that would tie up the=20
> index during
> maintenance.

>=20

> Non-prefixed indexes start with columns other than the=20
> partition key column,
> therefore Oracle wouldn't use those during maintenance on partitions.

>=20

> I am not sure that the other indexes pointing to that=20
> partition would work
> at all, however, it seems to me if Oracle shuffles data=20
> blocks around in the
> partition, then the indexes pointing to those blocks would be=20
> invalidated.
> Hence the rebuild index online feature... to speed up the=20
> rebuilding of
> indexes after work on partitions has completed.

>=20

> Now that I think about it, if it relies on the partition key=20
> index to work
> on that partition, the index would be invalidated as well,=20
> wouldn't it?

>=20

> Bizarre.

>=20

> Where did you find that statement in the Oracle=20
> documentation? Now I am
> curious to know what they meant by "maintenance".

>=20

> Regards,
> Patrice Boivin
> Systems Analyst (Oracle Certified DBA)

>=20


> Systems Admin & Operations | Admin. et Exploit. des syst=E8mes

> Technology Services        | Services technologiques

> Informatics Branch         | Direction de l'informatique=20

> Maritimes Region, DFO      | R=E9gion des Maritimes, MPO

Received on Wed Nov 29 2000 - 10:04:20 CST