Re: Partitioning Question and FKs to Non-Partitiond Tables
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 25 Mar 2004 22:27:20 -0000
Notes in-line
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
----- Original Message -----
From: "david wendelken" <davewendelken@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 25, 2004 8:55 PM
Subject: Partitioning Question and FKs to Non-Partitiond Tables
:
: I've been working thru partitioning issues and have a question on a
specific topic.
:
: Guidance would be more than welcome!
:
: FACT) I have a natural way to partition many of the data tables in the
application. I have a partitioning data column in those tables. I'll call
it segment_value and it's part of the natural primary key.
: There's no other reasonable way to partition things that would also make
sense from a maintenance perspective.
:
: FACT) However, segment_value is not particularly unique, the other primary
key column in the partitioned tables is unique by itself. So, I've put
segment_value as the second column of the primary key, not the first column.
:
: BELIEF) I think that was a good thing to do. But I could be wrong.
May or may not be the case.
If the users frequently query using the unique bit,
but not using the segment value, then you do need
to have the unique bit at the front, so that the cost
of hitting every partition is reduced.
The benefit of having the segment value at the front
is that you may get some benefit from compressing
the indexes.
Then you have to consider the frequency and scale
of queries like:
segment = .. and uniquebit between
segment between .. and uniquebit between
segment between .. and uniquebit = ..
:
: FACT) Some reference tables should not be partitioned because, well, they
shouldn't be.
: They are used equally across all partitions.
:
: SAD FACT) I will need to load, flush, and re-load some partitions. The
exchange option looks promising for this.
:
: SAD FACT) I'm short-handed on my project and short on schedulable
down-time, too.
:
: BELIEF) Local indexes on partitioned tables make it easier to take care of
the above sad fact, with minimal impact on people making use of the other
partitions. Global indexes slow some maintenance activities down.
:
See my note www.dbazine.com/jlewis17.shtml
on the nastier side effects of using exchange
partition, though. But in general your belief
is sound.
: CONCLUSION) Use local indexes where possible.
:
: QUESTION)
: When I do an FK from a partitioned table to a non-partitioned table, it
won't have segment_value in the primary key. So, the FK index wouldn't
naturally have segment_value in it.
: That would seem to make it a global index.
:
A local index does not need to include the
partitioning key. It is local by virtue of the
fact that each partition of the table automatically
has a 'private' partition of the index. Every entry
in a specific index partition is guaranteed to point
to the same table partition.
If you have a query like:
foreign_key_value = 'xxx'
and segment_value = 'ttt'
Oracle knows that it can hit partition P88
of the foreign_key_value index because it
knows that segment_value 'ttt' belongs in
partition P88 of the table.
: Any way out of this, to end up with local indexes? I haven't (yet) tried
to see if I can get away with stuffing segment_value on the back of the same
index used by the foreign key and partition by that. Not a lot of hope,
but I might get lucky. Any comments before I spend the time to test out the
idea?
:
:
----------------------------------------------------------------
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:
