DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=fOkAw2jmEd32ay3tO1U8JT48AUHrLJrhwXcROvvhUE6WHLOv6INlmwF9dMO7FqhvMIuvFIDguf0XeQ4oQSBid4XVdoaROV6PdBEwzzjM/GcPJ5uyLkSe6lvzkRIVM8hFyJiabW0bOTeeUSwzfNi1qYECFJKVOteVZWLb30xHkjQ=
Message-ID: <6e49b6d00704040021o23d2018cjba621f34d70cd77c@mail.gmail.com>
Date: Wed, 4 Apr 2007 10:21:30 +0300
From: "Gints Plivna" <gints.plivna@gmail.com>
To: andert@gmail.com
Subject: Re: Global (and local) Temporary Tables & PL/SQL
Cc: "oracle-l@freelists.org" <oracle-l@freelists.org>
In-Reply-To: <6d45e210704031329o13210352j8a7e0e248352e70d@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Disposition: inline
References: <6d45e210704031329o13210352j8a7e0e248352e70d@mail.gmail.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: gints.plivna@gmail.com

> table, it won't compile as the table is created and populated in the
> same step and thus does not exist when trying to compile the
> procedure.

So from thuis text I understood that table is being created at
runtime. I can only add my voice to the Tim's mail that GTT are not
predicted to use in the same way as temporary tables are for example
used in MS SQL Server. I won't explain much here why I don't like
dynamic SQL - the usual performance and code maintainability problems
apply.

>
> The facts known at this time are:
>
> 1. This table may be somewhat large (several hundred thousand rows
> with several VARCHAR 255 fields) at first, but later will be much more
> reasonable.

We have succesfully used both "explicit" GTTs (created manually) and
"implicit" GTTs (created by with clause of Selects). The only problem
I've encountered is quite obvious - if we create GTT using with clause
for select and later it use in some nested loops operation in the
select body, then if the GTT is big full scanning of it many times
becomes quite a pain :) Of course full scan of GTT some fixed number
of times (not dependant from number of rows) is absolutely normal.

>
> 2. Many users will be using this table at the same time with differing
> data and processing other data based on what they do with the data in
> these temp tables.

So this is somewhat contradictionary that you wrote before. If you you
are creating this temp table on the fly then it is very doubtful that
many users can use it - it is hard to imagine a scenario when you on
the fly create this table and all users just like after command starts
to use it and when it is dropped all of them stops to use it.
Obviously these will be several tables and each user will have his own
table.
Coincidentally just about a week ago I reviewed a code done by a
developer (obviously with MS background), here is what I got:
1) he created the GTT called T2 but later in procedure used table
called T1. So a wonderful example how it is hard to debug dynamic sql
and how he obviously had created table T1 manually - and what is more
important he succesfully used it! The create and drop scenario was
absolutely redundant.
2) it was much harder for me to go through the code because it simply
was dynamic sql.
3) if you create them on the fly depending on your user management
(either Oracle or application server users) you have to think how to
differentiate the names of them so that they are unique for a user or
not. And going further what if the same user connects more than once
in the same time? You need to differentiate it based even on some
session info or using some unique identifier. I personally wouldn't
like to think about such problems knowing I could easily avoid them
just creating one table. Also if your session dies you have in next
procedure run to check whether the table already is or isn't.
4) ddl on the fly will commit your transaction. It might be relevant
or might not be relevant to your app, but one has to remember that.

>
> The questions are:
>
> 2. Is there a way to "force" PL/SQL to "trust me, the table will be
> there" to get the proc to compile.

Just precreating them if you haven't too much very important reasons
not to do that, for example each time the structure of it may be
different and is not known in advance.

>
> 3. What else should I be thinking about that I am not (since I just
> got blind-sided with this).
Generally in terms of redo inserts are very very cheap for GTTs,
updates are much cheaper than for permanent tables, but deletes are
almost in the same level.

Gints Plivna
http://www.gplivna.eu
--
http://www.freelists.org/webpage/oracle-l