OK if it's a permanent table - say for temp storage of
information before acting on it - make a permanent
table. 99% of the time it is going to be empty. When
you have it loaded - do the runstats - never when it's
empty.

Mark - Am I missing something?

To dynamically create tables when referred would be a
major pain in the arse. If you make the permanent you
get to decide where to put them (I know you can do
that with Temp Tables from within programs as well)
where if it is going to be done for you dynamically
that would have to be predetermined for all tables of
this type - yes?

Sorry - perhaps if you gave a complete example of how
you would utilize this 'feature' I would have a better
grasp.

Rob Vorbroker
--- Mark Denham <mkdenham@comcast.net> 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.
>
> Mark
>
> sending to informix-list


=====
Rob Vorbroker Phone: 513/336-8695
Vorbroker Consulting, Inc. Fax: 513/336-6812
www.vorbroker.com robv@vorbroker.com

__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
sending to informix-list


sending to informix-list
sending to informix-list

Rob Vorbroker wrote:

> OK if it's a permanent table - say for temp storage of
> information before acting on it - make a permanent
> table. 99% of the time it is going to be empty. When
> you have it loaded - do the runstats - never when it's
> empty.
>
> Mark - Am I missing something?


Yes. The contents should be per-session, but the table definition is
available to all sessions without explicitly creating it -- no need
for a SELECT ... INTO TEMP or CREATE TEMP TABLE in each session to
create the table.

Note that if you use a 'regular, permanent' table for temporary
contents, you also have to add something like SESSION_ID to each table
and include it as part of the PK, and every query has to qualify the
data with it, and that *is* a PITA.

> To dynamically create tables when referred would be a
> major pain in the arse.

No worse that CREATE TEMP TABLE or SELECT ... INTO TEMP.

> If you make the permanent you
> get to decide where to put them (I know you can do
> that with Temp Tables from within programs as well)
> where if it is going to be done for you dynamically
> that would have to be predetermined for all tables of
> this type - yes?
>
> Sorry - perhaps if you gave a complete example of how
> you would utilize this 'feature' I would have a better
> grasp.

CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE t
(
...as usual...
) [ ON COMMIT { PRESERVE | DELETE } ROWS ];

Then, in any other session (or, indeed, in this one), you can refer to
table t, which will be empty until data is added to it by the session,
and it will be emptied either each time a transaction commits or when
the session ends.

> Rob Vorbroker
> --- Mark Denham <mkdenham@comcast.net> 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.
>>
>>Mark
>>
>>sending to informix-list
>
>
>
> =====
> Rob Vorbroker Phone: 513/336-8695
> Vorbroker Consulting, Inc. Fax: 513/336-6812
> www.vorbroker.com robv@vorbroker.com
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Finance: Get your refund fast by filing online.
> http://taxes.yahoo.com/filing.html
> sending to informix-list
>
>
> sending to informix-list
> sending to informix-list


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/