Home » SQL & PL/SQL » SQL & PL/SQL » Partition on table (Oracle10g)
Partition on table [message #326723] Thu, 12 June 2008 07:46 Go to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Hi All,

we want to do partition on fact tables. Earlier it was done on the basis of runid.Now we are introducing the concept of runskey in the table and it will be generated dynamically.
So run id is now not the part of primary key.
So can any one will give the idea how to do partition based on dynamially generated runskey.

thanks in advance

Re: Partition on table [message #326728 is a reply to message #326723] Thu, 12 June 2008 08:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Isn't the idea that YOU know how you want your table to be partitioned and YOU know how this runskey is generated?
Both are unknown to us, so how can we advise you here, unless you tell the answer to both questions. (which most probably will answer your question as well)
Re: Partition on table [message #326732 is a reply to message #326728] Thu, 12 June 2008 08:13 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
The runskey is autogenerated (like sequence number).
Re: Partition on table [message #326735 is a reply to message #326732] Thu, 12 June 2008 08:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
check this link

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#sthref2593

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#i1006194

Hope it helps

Regards

Raj
Re: Partition on table [message #326744 is a reply to message #326735] Thu, 12 June 2008 08:36 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Thanks Raj..
I will try for this hash partition.
Re: Partition on table [message #326747 is a reply to message #326744] Thu, 12 June 2008 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hashing partition is only useful if you have many independent disks to store each partition.
Otherwise it is useless.
You can't use it for partition pruning.
You can't use it for archiving.
You can't use it for independent back up.
You can't use it for transportation.
...

Regards
Michel
Re: Partition on table [message #327174 is a reply to message #326747] Sat, 14 June 2008 08:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why can't you use it for independent backup?

Say you have 7 hash partitions, and you back one up each day of the week. You get a corruption in partition 5, you restore it from last Thursday's backup and roll it forward.

I'm not a DBA and this stuff is all theory to me - I don't do it for my job. But what am I missing?

Ross Leishman

P.S. Hash partitioning probably is a stupid idea - I don't disagree with that part.
Re: Partition on table [message #327185 is a reply to message #327174] Sat, 14 June 2008 10:13 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rleishman wrote on Sat, 14 June 2008 15:34
Why can't you use it for independent backup?

You can, actually it is one of the main reasons to use hash partitioning. I have stupidingly written too fast my answer.

(And you can also benefit of partition pruning but only if you query against a list of values from the partition key.)

Regards
Michel

Previous Topic: Problem in creating view
Next Topic: Reading the current time zone
Goto Forum:
  


Current Time: Mon Dec 05 13:10:15 CST 2016

Total time taken to generate the page: 0.04698 seconds