RE: Local Prefix vs non-prefix indexes

From: Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca>
Date: Thu, 30 Nov 2000 08:11:08 -0400
Message-Id: <10696.123309@fatcity.com>

Kevin,

This would make sense.

Global indexes are not associated with individual partitions, therefore = if
you work on one partition the whole index is invalidated (?). Better = to use
local indexes, that way queries pertaining to the other partitions = would not
be forced to do full table scans.

Thanks.

Patrice Boivin
Systems Analyst (Oracle Certified DBA)

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

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca

> -----Original Message-----
> From: Toepke, Kevin M [SMTP:ktoepke_at_cms.cendant.com]
> Sent: Wednesday, November 29, 2000 12:36 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Local Prefix vs non-prefix indexes

>=20

> 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."
>=20
> "Local indexes provide more availability than global prefixed indexes =
when
> partition maintenance operations are carried out"
>=20
> Partition Maintenance Operations are adding, merging, splitting, =
dropping
> of
> individual partitions.

>=20

> 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)

>=20
>=20


> > -----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
> >=20
> > E-Mail: boivinp_at_mar.dfo-mpo.gc.ca
> >=20
> <snip>
> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --=20
> Author: Toepke, Kevin M
> INET: ktoepke_at_cms.cendant.com

>=20


> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing =

Lists


> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Thu Nov 30 2000 - 06:11:08 CST