| Partitioning [message #233549] |
Thu, 26 April 2007 09:51  |
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  |
rleishman
Messages: 3728
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 #234333 is a reply to message #234085] |
Mon, 30 April 2007 22:30 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)