Re: Partitioning in 8i

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Mon, 20 Nov 2000 10:28:50 -0800 (PST)
Message-Id: <10686.122523@fatcity.com>

--0-966244235-974744930=:88045
Content-Type: text/plain; charset=us-ascii

The problem with this is that the rules for a locally partitioned index state that the partition key must be part of the index key. My PK is a sequential number called order_nbr. That's dictated by the business rules and can't be easily changed. My partition key is a date called date_key. If I add date_key (no matter how I do it) to the PK, I introduce the possibility of duplicate order_nbr's, so long as they're in different dates. That violates my business rules that say an order_nbr can only be used once. The end result appears to be that I can't partition my table on dates. Only on order_nbr ranges. That'll at least give me the ability to rebuild local index partitions without taking down the entire table. But it won't facilitate a fast date-based purge or archive. Which would also be of great benefit. danut.bancea_at_ifl.bmo.ca wrote:

Hi,

You can try this but I'm not sure that is working:

create a index which will contain the primary key column, (you can add other column also), and after that you can creat the PK . In this case the PK will use current index.

Normally if you create the index local, when you will drop the partition will drop just the index which corespond to that partition.

But again, I didn't tested, I'am just thinking that is possible to work.

Danut,

Chuck Hamilton <chuck_hamilton_at_yahoo.com> Sent by: root_at_fatcity.com

11/10/00 11:36 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 
        cc:         
        Subject:        Partitioning in 8i

Can someone help with this problem. I have an order table with > 100 million orders. I want to partition it based on the month the order was taken and every month add a new partition and drop the one that's 25 months old. When I do this however it invalidates the PK index which is simply the order's number (a sequential number). I can't locally partition the PK index because it doesn't contain the partition key column (order date). I can't change the PK because that would allow duplicate order numbers if created on different days. What can I do to quickly drop the 25th month without invalidating the PK index which effectively disables the entire table?

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. All with one Wallet.

Do You Yahoo!?
Yahoo! Calendar - Get organized for the holidays! --0-966244235-974744930=:88045
Content-Type: text/html; charset=us-ascii

The problem with this is that the rules for a locally partitioned index state that the partition key must be part of the index key. My PK is a sequential number called order_nbr. That's dictated by the business rules and can't be easily changed. My partition key is a date called date_key. If I add date_key (no matter how I do it) to the PK, I introduce the possibility of duplicate order_nbr's, so long as they're in different dates. That violates my business rules that say an order_nbr can only be used once. The end result appears to be that I can't partition my table on dates. Only on order_nbr ranges. That'll at least give me the ability to rebuild local index partitions without taking down the entire table. But it won't facilitate a fast date-based purge or archive. Which would also be of great benefit.
  danut.bancea_at_ifl.bmo.ca wrote: 
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px"> Hi, You can try this but I'm not sure that is working: create a index which will contain the primary key column, (you can add other column also), and after that you can creat the PK . In this case the PK will use current index. Normally if you create the index local, when you will drop the partition will drop just the index which corespond to that partition. But again, I didn't tested, I'am just thinking that is possible to work. Danut, 
<TABLE width="100%"> 
<TBODY> 
<TR vAlign=top> 
<TD> 
<TD>Chuck Hamilton <chuck_hamilton_at_yahoo.com> Sent by: root_at_fatcity.com 
11/10/00 11:36 AM Please respond to ORACLE-L 
<TD>                To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>         cc:                 Subject:        Partitioning in 8i</TD></TR></TBODY></TABLE> Can someone help with this problem. I have an order table with > 100 million orders. I want to partition it based on the month the order was taken and every month add a new partition and drop the one that's 25 months old. When I do this however it invalidates the PK index which is simply the order's number (a sequential number). I can't locally partition the PK index because it doesn't contain the partition key column (order date). I can't change the PK because th!
at would allow duplicate order numbers if created on different days. What can I do to quickly drop the 25th month without invalidating the PK index which effectively disables the entire table? 
 
<HR>
 Do You Yahoo!? Yahoo! Shopping - Thousands of Stores. Millions of Products. All with one Wallet. 
 
</BLOCKQUOTE> <hr size=1>Do You Yahoo!? 
<a href="http://calendar.yahoo.com/">Yahoo! Calendar</a> -
Received on Mon Nov 20 2000 - 12:28:50 CST