| Exchange Partition [message #383389] |
Wed, 28 January 2009 11:29  |
dr46014
Messages: 49
Registered: February 2007
|
Member |
|
|
Hi All
I am having a table which is having the below partition and index strategies.
Index : B Tree : Account_Nbr
Partition : Week_Code(200901,200902....200952 like this)
Sub Partition : Client_ID(List)
Now i have a huge volume of history data let's say 100 weeks data(1000 million records) in the table with different partitions on week code.
I am planning to load the table every week with a exchange partition method i.e creating a exchange table and do alter table exchange partition to load the table for new week code.
Now i have couple of questions :
What will be the partition and indexing strategy for exchange table.Will it be sub partitioned on Client_ID?
Is there any way i can improve the performance in this method?
What about index creation after the loading is over?Do i need to create index or it will be created automatically.
|
|
|
|
| Re: Exchange Partition [message #383432 is a reply to message #383389] |
Wed, 28 January 2009 20:31  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Your exchange table will be LIST partitioned on client_id - it will NOT be sub-partitioned.
If your index is defined as LOCAL, then you may build a similar locally partitioned index on the exchange table and it will be exchanged along with the table partition if you include the INCLUDING INDEXES option.
If the index is GLOBAL or GLOBALLY PARTITIONED, you would be best to pass the UPDATE GLOBAL INDEXES clause to the command IF YOU ARE JUST DOING ONE PARTITION. This is faster than rebuilding afterwards because it exploits the already sorted contents of the index that is retained.
However if you want to exchange many partitions, invalidate the global index and rebuild it later.
Ross Leishman
|
|
|
|
| Re: Exchange Partition [message #383684 is a reply to message #383389] |
Thu, 29 January 2009 13:11 |
dr46014
Messages: 49
Registered: February 2007
|
Member |
|
|
Thanks for your reply.
Let me ask one more question regarding the same exchange partition method of data load.
I am trying to load around 500 million record to a table every month.Now my table is partitioned on month_code and sub partitioned on client_id.Again there is a local index(B Tree) on account_nbr for each partition.Now the table has a column FLAG which has a value either Y or N.So to get the query result faster i m planning to have that flag as a BITMAP index in the table.
As we are planing partition exchange method of loading data
1.What will be the partition indexing strategy in the exchange table.
2.How BITMAP index will be rebuilt.
3.Every month there are different partitions.
If you can suggest some other alternatives that would be great.
|
|
|
|
| Re: Exchange Partition [message #383770 is a reply to message #383684] |
Fri, 30 January 2009 00:58 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It's quite simple, just make the exchange table look EXACTLY like a single partition of the base table.
If the base table is HASH sub-partitioned, then HASH partition the exchange table.
If the base table has a local index on Column X, create a local index on Column X of the exchange table.
If the base table has a foreign key constraint to table Y, place the same constraint on the exchange table.
There is a good section in the Oracle Data Warehousing Manual on PEL (Partition Exchange Loading). You would do well to read it.
Bitmap indexes on their own are generally useless; in most cases a table requires 2 or more bitmap indexed columns before they pay off. See this link and read-up on Bitmap Indexes in the Oracle Performance Tuning Guide.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
