RE: Local Prefix vs non-prefix indexes

From: Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca>
Date: Wed, 29 Nov 2000 11:10:53 -0400
Message-Id: <10695.123198@fatcity.com>

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

Here is my take on this:

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

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

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

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

Bizarre.

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

Regards,
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: Cale, Rick T (Richard) [SMTP:RICHARD.T.CALE_at_saic.com]
> Sent: Wednesday, November 29, 2000 10:05 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Local Prefix vs non-prefix indexes

>=20

> I was reading about partitioning and it states the following:
>=20
> "Local nonprefixed indexes provide more availability than prefixed =
indexes
> when partition
> maintenance operations are carried out."

>=20

> Can someone explain WHY?

>=20

> Thanks
> Rick Cale, Science Applications International Corp.
> Phone:865-481-2198, fax:865-481-8555
> e-mail: caler_at_saic.com

>=20
>=20

> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --=20
> Author: Cale, Rick T (Richard)
> INET: RICHARD.T.CALE_at_saic.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 Wed Nov 29 2000 - 09:10:53 CST