Home » RDBMS Server » Performance Tuning » Partitioning
Partitioning [message #233549] Thu, 26 April 2007 09:51 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
I have a table which I made into a hash-partitioned. Before partitioning, i used to insert records in that table in just 5 minutes - around 25000 rows inserted. After partitioning, it became 20 mins to insert 25000 rows. My partition key is the Primary Key and I have created 9 indexes (btree) on each partition (6 of them).

I found that my query on this table improved after I partitioned. But insert is taking long time. Please help.

[Updated on: Thu, 26 April 2007 21:09]

Report message to a moderator

Re: Partitioning [message #234037 is a reply to message #233549] Sun, 29 April 2007 02:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmm. I haven't done any benchmarking on this sort of thing, but it doesn't completely surprise me.

Try some different exercises.
- Insert only rows with a single value of the hash-partition key so that they all go into the same partition. Does this performance compare with the non-partitioned version?
- Try INSERT INTO tab PARTITION (p) using the same data. Any better? Worse?

If it is caused by Oracle trying to flip between segments for each row, the above should demonstrate an improvement.

Also, are you sure it is the INSERT that is slower? If you are using INSERT INTO .. SELECT FROM then the SELECT might be running slower. You could check by loading the rows temporarily into another table.

Another worthwhile test is to check whether SQL*Loader is slower. Dump the data down to a file and load it.

Ross Leishman
Re: Partitioning [message #234085 is a reply to message #234037] Sun, 29 April 2007 14:34 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks, Ross! (I've been waiting for you Smile )

Quote:
Insert only rows with a single value of the hash-partition key so that they all go into the same partition.


The thing is that I used the primary key as the partition key so I won't be able to insert with a 'single value'. Is that fine? Is that a bad practice?? What's the best way to detremine the partition key for a hasp partition?
Re: Partitioning [message #234333 is a reply to message #234085] Mon, 30 April 2007 22:30 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I haven't tried it, but ORA_HASH may give you a hash key that correlates to your partitions.

eg.
CREATE TABLE one_part AS
SELECT *
FROM non_part_tab
WHERE ora_hash(pk, 6) = 1;

INSERT INTO part_tab
SELECT *
FROM one_part


If ORA_HASH() works the same way as the hash partitioning hash algorithm, then all of the rows in this statement will insert into the same partition.

I've been on holidays for the last week - that's why no earlier posts.

Ross Leishman
Previous Topic: Some basic tuning steps
Next Topic: Any Suggestion on this STATSPACK Report
Goto Forum:
  


Current Time: Tue Dec 06 12:27:39 CST 2016

Total time taken to generate the page: 0.13185 seconds