Home » RDBMS Server » Performance Tuning » Exchange Partition (Oracle 10g UNIX)
Exchange Partition [message #383389] Wed, 28 January 2009 11:29 Go to next message
dr46014
Messages: 48
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
dr46014
Messages: 48
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Range Hash Partitioning vs. range partition plus B Tree index (merged)
Next Topic: query tuning
Goto Forum:
  


Current Time: Fri Dec 09 17:32:55 CST 2016

Total time taken to generate the page: 0.17814 seconds