Re: Why Not Allow Temp Tables?

On Mon, 22 Sep 2003 22:52:35 GMT, "(Pete Cresswell)" <x_at_y.z> wrote:

>RE/
>>Are they making the distinction between dynamically-created temporary tables
>>(almost universally wrong) or global temporary tables created only once (not
>>often needed, but considerably less evil).
>
>The situation is that of creating reports. Bond trading application, many
>desks, many users at each desk, all at each other's throats, nobody wants to
>wait for the next guy - especially if they're on a competing desk.
>
>The first choice offered was temporary tables. Applealing because it puts each
>user in their own little world. If a hundred users try to run reports at the
>same time, performance will degrade - but everybody waits - nobody goes to the
>head of the line at the expense of others - everybody has the perception that
>their reports are being run *now*, albeit sometimes slowly.
>
>There's a second choice involving global tables, but part of the situation is
>that many people may choose to run reports that use the same suite of data at
>nearly the same times - yet each group of reports needs to be as "realtime" as
>possible. My current understanding of the global table option is that it will
>require locking out everybody except one user at a time - something about
>inserting into a temp table wiping pre-existing data - but I know nothing of
>P-SQL or Oracle.

 Sounds like you've not understood what global temporary tables are about? Their _structure_ is fixed, but the _data_ is visible only to the session that inserted it. Don't know where you heard about having to lock everyone out, but it's wrong. (Perhaps unless you're pooling all the reports through a single Oracle session, but then that's not going to allow more than one to run at a time anyway, so it can't be that). Multiple users can use a single global temporary table, and each has their own isolated set of data, invisible from each other.

 If they've decreed 'no dynamically created "temporary" tables using the plain "CREATE TABLE x" syntax, followed by a "DROP TABLE x" at some point' then they're correct. Adding and removing tables are schema changes, and you shouldn't be changing schema except in controlled circumstances (i.e. application upgrade).

 If you create normal tables on the fly:

• if reports die unexpectedly, what cleans up the table? With global temporary tables, Oracle cleans out the data at end of transaction or end of session (depending on the option chosen at create time).
• you have to grant 'create table' and 'drop table' to an application user; application users shouldn't have DDL access to the schema.
• you can't reuse any of the cached SQL, since all the object names are different, so hard parses will go up
• the app has to decide which tablespaces to use, etc.; that shouldn't be the job of the app, it's the DBA's job
• you get all the redo and undo overhead for the temporary data, which isn't needed if the data itself is only temporary; global temporary tables cut these both down
• no doubt many other points

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)