| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Partitioned Indexes question
Hi Binley,
I think you are right. Both scenarios are not possible in v9.
Thought you guys might get a kick out of the response I got from Support. He references everyone's favorite redneck.... wow, guess I got my money's worth out of this one.
Thanks everyone
08-MAR-04 22:17:12 GMT Indexes can not be hash partitioned before version 10g.
The following from Don Burleson:
Oracle10g Hash-Partitioned Global Indexes Index objects can be partitioned in the same way tables are. Indexes partitioned independently are called Global Indexes, and indexes that automatically link to a table's partitioning method are called Local Indexes.
In the range-partitioned global index, each index partition contains values defined by a partition boundary maxvalue. This ensures that all rows in the underlying table can be represented in the index.
Global prefixed indexes can be unique or non-unique. You cannot add a partition to a global index because the highest partition always has a boundary of maxvalue. If you wish to add a new highest partition, use the alter_index_split_partition statement.
In a hash-partitioned global index, each partition contains the values determined by Oracle's hash function, which is based on the partitioning key and the number of partitions. Global hash-partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index. Thus, hash-partitioned global indexes can improve the performance of indexes in which a small number of leaf blocks in the index have high contention in multi-user OLTP environments.
-----Original Message-----
From: Lim, Binley [mailto:Binley.Lim_at_NBNZ.CO.NZ]
Sent: Monday, March 08, 2004 7:12 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Partitioned Indexes question
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
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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- "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 -----------------------------------------------------------------Received on Tue Mar 09 2004 - 08:05:48 CST
 |
 |