Unanswered: Not all the time Multipartition Tablespace better performance
I have a shell script where a lot of batch job processing involve, such load into a temporary table, comparison and update and so forth.
Once of the temporary table initially I created using single partition tablespace but due to timing I want to test to put this temp table into multipartition to gain performance. But, I can't imagine with multipartition table loading and reading... it cause 8 hours whereas I recreated the table into single partition tablespace, it only took only 30min to complete. Is this contention happen in multi-partition tablespace? What are the condition does not recommend to use multi-partition tablespace?
database partitioning ? table partitioning ?
multi-partition tablespace? you mean tablespace with multi containers ??
be VERY specific : platform - version all details....
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be
You could explain your problem more carefully...and also give more facts (versions,fixpacks, operating system, distribution keys etc)...
If the processing time varies between 30minutes (for a single partition tablespace) and 8 hours (for a multi-partition tablespace) then maybe you have either chosen poor distribution key(s) and/or failed to ensure co-location of the joins in the queries, and/or failed to index and runstats (with details) relevant columns for the queries...?
So: unless you understand how to correctly distribute your tables and also how to achieve co-located joins then you may fail to exploit multiple partition tablespaces properly. If you are able to study the access plans for the queries when using a multiple-partition-tablespace for the temporary table, then you can learn more...