RE: Partitioned Indexes question

From: Lim, Binley <Binley.Lim_at_NBNZ.CO.NZ>
Date: Tue, 9 Mar 2004 13:11:56 +1300
Message-ID: <5FA85DB5FB07D7118CF50002A5754C0902E2CA1A@nbnzhexch2.nbnz.co.nz>

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.

Local indexes follow the partitioning scheme of the tables(!), but you can only do range-partitioning on global indexes. These are in the docs, but probably not worded, or located where you were expecting to find them. In general, the partitioning error-messages are also quite helpful:

	global partition by hash(col_a)
                                      *
	ERROR at line 2:
	ORA-14005: missing RANGE keyword

You can only do "range" at the partitioning, and not sub-partitioning level:

	subpartition by range(col_b)
	*
	ERROR at line 8:
	ORA-00922: missing or invalid option





> -----Original Message-----

> From:	Koivu, Lisa [SMTP:Lisa.Koivu_at_Cendant-TRG.com]

> Sent:	Tuesday, March 09, 2004 5:56 AM

> To:	oracle-l_at_freelists.org

> Subject:	Partitioned Indexes question

>
> Hello all,
>
> I seem to remember that an index does not need to be partitioned on the
> same key as a table. Does anyone know, does this refer to hash
> partitioned indexes? I have read through the docs and it doesn't say
> so. For example, I'd like to create a hash-partitioned index on a range
> partitioned table.
>
> Also, can a composite-indexed table be hash at the partition level, and
> range at the subpartition level? Again, I haven't read that this is
> possible, but that doesn't mean it isn't.
>
> I just wanted to be sure I didn't miss something.
> thanks
>
> Lisa Koivu
> Monkey Mama
> Orlando, FL, USA
>
>
>
> "The sender believes that this E-Mail and any attachments were free of any
> virus, worm, Trojan horse, and/or malicious code when sent. This message
> and its attachments could have been infected during transmission. By
> reading the message and opening any attachments, the recipient accepts
> full responsibility for taking proactive and remedial action about viruses
> and other defects. The sender's business entity is not liable for any loss
> or damage arising in any way from this message or its attachments."
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

This communication is confidential and may contain privileged material. If you are not the intended recipient you must not use, disclose, copy or retain it. If you have received it in error please immediately notify me by return email and delete the emails.
Thank you.

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Mar 08 2004 - 18:09:40 CST