Home » SQL & PL/SQL » SQL & PL/SQL » Creating Hash Partition (oracle 10g)
Creating Hash Partition [message #407204] Tue, 09 June 2009 01:39 Go to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
hi,

Our fact tables has million of records and i have to create partition based on dynamic surrogate key(PK). So i created hash partition on different tablespaces. Below is the hash partition which i have created.Pls suggest if any improvement required.

STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "SATA"
  PARTITION BY HASH ("SKEY")
 (PARTITION "PAR_18"
   TABLESPACE "SATA10",
 PARTITION "PAR_19"
   TABLESPACE "SATA1",
 PARTITION "PAR_20"
   TABLESPACE "SATA3",
 PARTITION "PAR_21"
   TABLESPACE "SATA4",
 PARTITION "PAR_22"
   TABLESPACE "SATA5",
 PARTITION "PAR_23"
   TABLESPACE "SATA6",
 PARTITION "PAR_24"
   TABLESPACE "SATA7",
 PARTITION "PAR_25"
   TABLESPACE "SATA8",
 PARTITION "PAR_26"
   TABLESPACE "SATA9",
 PARTITION "PAR_1"
   TABLESPACE "SATA1",
 PARTITION "PAR_28"
   TABLESPACE "SATA1",
 PARTITION "PAR_29"
   TABLESPACE "SATA2",
 PARTITION "PAR_30"
   TABLESPACE "SATA4",
 PARTITION "PAR_31"
   TABLESPACE "SATA5",
 PARTITION "PAR_27"
   TABLESPACE "SATA10",
 PARTITION "PAR_2"
   TABLESPACE "SATA2",
 PARTITION "PAR_32"
   TABLESPACE "SATA6",
 PARTITION "PAR_3"
   TABLESPACE "SATA3",
 PARTITION "PAR_4"
   TABLESPACE "SATA7",
 PARTITION "PAR_5"
   TABLESPACE "SATA8",
 PARTITION "PAR_6"
   TABLESPACE "SATA9",
 PARTITION "PAR_7"
   TABLESPACE "SATA10",
 PARTITION "PAR_8"
   TABLESPACE "SATA1",
 PARTITION "PAR_9"
   TABLESPACE "SATA2",
 PARTITION "PAR_10"
   TABLESPACE "SATA2",
 PARTITION "PAR_11"
   TABLESPACE "SATA3",
 PARTITION "PAR_12"
   TABLESPACE "SATA4",
 PARTITION "PAR_13"
   TABLESPACE "SATA5",
 PARTITION "PAR_14"
   TABLESPACE "SATA6",
 PARTITION "PAR_15"
   TABLESPACE "SATA7",
 PARTITION "PAR_16"
   TABLESPACE "SATA8",
 PARTITION "PAR_17"
   TABLESPACE "SATA9")
  PARALLEL 8 ;
Re: Creating Hash Partition [message #407205 is a reply to message #407204] Tue, 09 June 2009 01:41 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Is it better?
Please quantify.

[Updated on: Tue, 09 June 2009 01:43]

Report message to a moderator

Re: Creating Hash Partition [message #407207 is a reply to message #407204] Tue, 09 June 2009 01:58 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
BlackSwan
Thanks for replying.

That is what i want to hear from you experienced guys.Is it worth or not.
Re: Creating Hash Partition [message #407211 is a reply to message #407204] Tue, 09 June 2009 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why this number of partitions?
Why parallel 8?

Regards
Michel
Re: Creating Hash Partition [message #407217 is a reply to message #407204] Tue, 09 June 2009 02:31 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Hash partitions= 2 power n

so 32 partitions is created.

parallel:- Parallel processing is splitted to 8.
Re: Creating Hash Partition [message #407222 is a reply to message #407217] Tue, 09 June 2009 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why 32? Why 8?

Regards
Michel
Re: Creating Hash Partition [message #407250 is a reply to message #407204] Tue, 09 June 2009 05:05 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
i had tried for
8
16
32 -- little better performance

parallel
4
8 -- better performance
Re: Creating Hash Partition [message #407255 is a reply to message #407250] Tue, 09 June 2009 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why do you ask and what are you asking exactly?

Regards
Michel
Re: Creating Hash Partition [message #407274 is a reply to message #407204] Tue, 09 June 2009 07:08 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
The performace is not so good .So i just want to know any changes will make it good.
Re: Creating Hash Partition [message #407276 is a reply to message #407274] Tue, 09 June 2009 07:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Partitioning an object can improve performance in two ways:
- Partition pruning (eg. WHERE part_col = :y)
- Partition-wise join (eg. join equi-partitioned tables on the partition-key.

If your SQLs don't use either of these, don't expect any performance improvement.

Ross Leishman
Re: Creating Hash Partition [message #407280 is a reply to message #407274] Tue, 09 June 2009 07:20 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
"Performance is not so good"

That's not a very helpful statement.
Performance tuning is a tricky process.

What problem are you trying to fix? - and the answer to that question isn't 'The performance of my system' - tell us what the observed performance problem is.

Can you tell us how you came to the conclusion that the partition structure of this table is thing that needs to be fixed to cure that performance problem?

Have you (for example) tuned all of the SQL that accesses this table, (or indeed tuned all of the other poorly performing SQL?)
Previous Topic: possible to partition exchange two tables
Next Topic: Select for update where current of problem
Goto Forum:
  


Current Time: Fri Dec 02 16:41:36 CST 2016

Total time taken to generate the page: 0.19730 seconds