Re: [ZODB-Dev] RelStorage - zodbconvert script and the --clear flag
On Thu, Sep 1, 2011 at 12:45 AM, Shane Hathaway sh...@hathawaymix.org wrote: On 08/31/2011 05:11 PM, Darryl Dixon - Winterhouse Consulting wrote: Just had a quick query from my friendly local DBA; he wanted to know why --clear was using DELETE rather than TRUNCATE; his comments were along the lines of: * TRUNCATE creates no UNDO * TRUNCATE cleans out the indexes nicer Is there any real downside or gotcha I should be aware of to following this suggestion in this instance? To be clear: This is RelStorage 1.5.0 running against Oracle RAC 10g. I tried changing the line in question (relstorage/adapters/schema.py line 895) to TRUNCATE TABLE %s % table, and the following error is generated: ORA-02266: unique/primary keys in table referenced by enabled foreign keys So I guess without a bunch of work it will not be possible. Is clearing the database a common operation that should be tuned for performance? That part of the code is optimized for safety and readability, not performance. Replying late, but coming back to this case, I figured it is worth mentioning what I do to work around this (that deleting is slow even on a modest database): I drop the database and re-create. This is quick -- 20 seconds of manual work versus 20+ minutes of waiting for a delete to finish (PostgreSQL); I am pretty sure I will soon just wrap zodbconvert in a shell script (templated in my buildout) -- this avoids the whole truncate versus delete debate (and I can just keep my blob directory intact on the destination). Sean ___ For more information about ZODB, see http://zodb.org/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
[ZODB-Dev] RelStorage - zodbconvert script and the --clear flag
Hi Folks, Just had a quick query from my friendly local DBA; he wanted to know why --clear was using DELETE rather than TRUNCATE; his comments were along the lines of: * TRUNCATE creates no UNDO * TRUNCATE cleans out the indexes nicer Is there any real downside or gotcha I should be aware of to following this suggestion in this instance? regards, Darryl Dixon Winterhouse Consulting Ltd http://www.winterhouseconsulting.com ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage - zodbconvert script and the --clear flag
Just had a quick query from my friendly local DBA; he wanted to know why --clear was using DELETE rather than TRUNCATE; his comments were along the lines of: * TRUNCATE creates no UNDO * TRUNCATE cleans out the indexes nicer Is there any real downside or gotcha I should be aware of to following this suggestion in this instance? To be clear: This is RelStorage 1.5.0 running against Oracle RAC 10g. I tried changing the line in question (relstorage/adapters/schema.py line 895) to TRUNCATE TABLE %s % table, and the following error is generated: ORA-02266: unique/primary keys in table referenced by enabled foreign keys So I guess without a bunch of work it will not be possible. regards, Darryl Dixon Winterhouse Consulting Ltd http://www.winterhouseconsulting.com ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev