DBSPACETEMP question

Hi, I'm sure I've seen this mentioned before but I can't find the thread now :o( I set up DBSPACETEMP in my onconfig to be a list of five dbspaces on five different disks. All of the dbspaces are marked as temporary. Reading through my online administration course notes I find a supplement which states that temp. tables created explicitly using SELECT .... FROM .... INTO TEMP a_temp_table, are only created in the temp dbspaces if either the database is not logged or if the WITH NO LOG option is used. O.K. this I understand. My question is does the same apply for implied temp. tables used for ORDER BY clauses etc? If so given that all of my databases are logged, what is the best strategy for managing the temp db spaces? Should I remove the temp. flag from some of my them but leave them in the DBSPACETEMP list? If this list has a mixture of temporary and normal dbspaces will it still function correctly? Online 7.24.UC5 HP-UX 10.20 -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf .

tony, > I'm sure I've seen this mentioned before but I can't find the thread now yeah, it has been discussed before, but so has most of what comes up on the list, new people come, people who have been around run into things they have forgotten, etc.. it all works out in the end. > My question is does the same apply for implied temp. tables used for ORDER > BY clauses etc? If so given that all of my databases are logged, what is > the best strategy for managing the temp db spaces? Should I remove the > temp. flag from some of my them but leave them in the DBSPACETEMP list? If > this list has a mixture of temporary and normal dbspaces will it still > function correctly? when the engine goes to create a temp table, it will look to see if there are any temp dbspaces to use. it will judiciously use them in round robin fashion in a number of ways. if it is justified, (i.e. if the table is large enough), it will fragment the table round robin through the available dbspaces. i believe there has to be 3 or more dbpsaces for this, but i'm not sure if that number is correct. so the engine knows how to make use of these. in a previous discussion, art was talking about how the temp dbspaces are used in the archive process for temp tables of pages that are changed during the archive of each dbspace. a table gets created in the temp dbspaces that are then written out to the archive and deleted. that behaviour has changed a bit bewteen 7.2X and 7.3, but that is the idea. so there are plenty of times that you want to have the temp dbspaces around. on a system i was working on last year, we needed 11gb of temp dbspace to build an index on a table with 157 dbspaces, and 1.78 billion rows. probably couldn't have done it without temp dbspaces. so no, don't remove the temp dbspace from them, and do leave them all in the list. the engine will use them properly for implicit tables, and you just have to set things so they are used the way you want them to be for explicit tables. big subject, but that should be the basics. hope that helps. mickm -- ----------------------------------------------------------------------- This is a signature file. This is only a signature file. Had this been an actual piece of useful information, you would have been instructed on what to do with it. -----------------------------------------------------------------------