SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-19-2008, 08:23 PM
| ||||
| ||||
 FW: Whatcha' wanta have????? Be able to add the definition of a temp table to the system catalogs so that it's not necessary to define the table in the application. I would like it to be possible to set it 'not logged'. The table should automatically be created when it is referred too in a select, update or delete. This would be a great help for improving performance of off-the-shelf apps which create 'permanent' tables to hold temporary data for processing purposes. Mark sending to informix-list      |
|
04-19-2008, 08:23 PM
| |||
| |||
| Re: Whatcha' wanta have????? Mark Denham wrote: > Be able to add the definition of a temp table to the system catalogs > so that it's not necessary to define the table in the application. I > would like it to be possible to set it 'not logged'. The table should > automatically be created when it is referred too in a select, update > or delete. > > This would be a great help for improving performance of off-the-shelf > apps which create 'permanent' tables to hold temporary data for > processing purposes. This sounds kinda tempting to a lot of people, but it's not realistic. If it persists longer than a session, then it's a real table! What would distinguish a real temp table from these persistent temp tables? Only the "not logged" status? But if the data and table space is not going to be cleaned up at process termination, then it implies a kind of persistance that requires logging if the database is to recover properly. The magic of unlogged temp tables is that nothing in them is necessary to perform fast recovery or log rollforward, therefore if the session is happy not to be able to rollback it's data modifications of an unlogged temp table, you can save performance and logging activity in the engine. But this is not compatible with persistent tables. |
|
04-19-2008, 08:24 PM
| |||
| |||
| Re: Whatcha' wanta have????? Andrew Hamm wrote: > Mark Denham wrote: > >>Be able to add the definition of a temp table to the system catalogs >>so that it's not necessary to define the table in the application. I >>would like it to be possible to set it 'not logged'. The table should >>automatically be created when it is referred too in a select, update >>or delete. >> >>This would be a great help for improving performance of off-the-shelf >>apps which create 'permanent' tables to hold temporary data for >>processing purposes. > > > This sounds kinda tempting to a lot of people, but it's not realistic. If it > persists longer than a session, then it's a real table! No. The table is empty at the start of each session; its contents are local to each session. The great advantage is that you don't have to explicitly clean it out -- the contents are temporary, but not the definition. You also don't run into the issue of two independent parts of the same application both trying to use the same temporary table name with different structures (eg, create temp table t(...)). > What would > distinguish a real temp table from these persistent temp tables? Only the > "not logged" status? But if the data and table space is not going to be > cleaned up at process termination, then it implies a kind of persistance > that requires logging if the database is to recover properly. No; the key difference is that you can do: INSERT INTO temptable SELECT ... without having to create the table first. > The magic of unlogged temp tables is that nothing in them is necessary to > perform fast recovery or log rollforward, therefore if the session is happy > not to be able to rollback it's data modifications of an unlogged temp > table, you can save performance and logging activity in the engine. But this > is not compatible with persistent tables. There'd be nothing to deal with on recovery here, either - beyond normal temp table cleanup. The key point is that the definition is persistent, but the contents are per session -- I seem to remember there being a per-transaction option too... -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ |
|
04-19-2008, 08:24 PM
| ||||
| ||||
| Re: Whatcha' wanta have????? Jonathan Leffler wrote: > > The key point is that the definition is persistent, but the contents > are per session -- I seem to remember there being a per-transaction > option too... OK then - I can get down to that. Original poster has also explained that they are talking about schema only, and not data. I have sometimes heard people (both at work or on ng) asking "can I share my temp table with another process?" and it's a bit tricky to explain why not... What you described would also NOT offer that ability. Is there any room for a temp table that is owned by one process, is shared, but when the first process goes away the temp table is taken away with it? I think the people who ask for data sharing in temp tables are looking for a way to pass data back and forth between cooperating processes. Which almost sounds like a persistent, logged queue within the engine... |
Linear Mode
