On Feb 19, 2008, at 7:46 AM, fdu.xiaojf@xxxxxxxxx wrote:
I have tried to do partition with inheritance and rules. First, I
created master table and many child table, and also the rules for
insert, delete and update. Then I do some select, insert, delete
and update operations on the master to test if it works. However,
the insert an delete work very well, but the update operation seems
never return. I tried several times, and could wait it to return
and killed the process.
I tried the commands manually, and it seemed very weird.
The delete command:
DELETE FROM master_table WHERE id='9999' AND data_type='aaa'
and select command with the same condition expression:
SELECT * FROM master_table WHERE id='9999' AND data_type='aaa'
both return without delay.
But the update command with the same condition expression:
UPDATE master_table set data_value='somevalue' WHERE id='9999' AND
data_type='aaa'
didn't return in 5 minutes.
Every table has index and constraint on column "id". I have already
set constraint_exclusion=true.
Why the update command runs so slow ?
You'll need to post some of your tables' schemas along with the
results of running your queries through EXPLAIN or EXPLAIN ANALYZE
(better) in order for anyone to answer that.
Erik Jones
DBA | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
