RE: Partitioned Indexes question
- From: "Koivu, Lisa" <Lisa.Koivu@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Mar 2004 09:08:47 -0500
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 previous releases of the Oracle Database, only the range-partitioned
global indexes were supported. Oracle 10g Database introduces the Hash
Partitioning method for global indexes.
************************************************************************
************************************************************************
*******
Figure 4.2 and 4.3 show a graphic view of partitioned and
non-partitioned global indexes. Note how the non-partitioned global
index is like a normal index.
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@xxxxxxxxxx]
Sent: Monday, March 08, 2004 7:12 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxxxx]
> Sent: Tuesday, March 09, 2004 5:56 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
