Re: questions about very large table and partitioning

On Feb 19, 2008, at 7:46 AM, fdu(dot)xiaojf(at)gmail(dot)com 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(at)myemma(dot)com
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