On 21 Aug 2006, vibroverb_at_hotmail.com wrote:
>
> Frank van Bortel wrote:
>> Deltones schreef:
>> > I posted a question last week where people where some of the guys
>> > who replied recommanded that I use "create global temporary table"
>> > instead of "create table". I tried it with the script I've been
>> > asked to modify that contained the latter construction.
>> >
>> > I get my results every time with the "create table" version, but if
>> > I'm lucky, I get results maybe 50% of the time with the "create
>> > global temporary table". Take note that it's the same queries that
>> > are run in both cases and that I also tried the "on commit delete
>> > rows", "on commit preserve rows" clause, and that the tables are
>> > dropped at the end. Why does it do this?
>> >
>> > Are tables created with "global temporary" different that regular
>> > tables?
>> >
>> > Thanks
>> >
>> > Denis
>> >
>> Yes - they exist for the duration of the session, if not shorter.
>> And you do not "use the create global temporary table everytime",
>> you just use it once.
>>
>> Your style reminds me of dBaseIII and Foxbase I used to program.
>>
>> And if you get the wrong results, you might run into a bug,
>> in which case versions do matter enormously!
>
> It's the style of the script I have to modify, not mine. The main
> query to create the first temp table is an 800 lines abomination :)
But you have already modified the script to add "global". That means
you have access to and the capability of modifying this abomination.
So, therefore, you can just remove the entire table creation from the
abomination script. Create the GTT by executing that part before you
start using the script. Create it, and then run your script time and
time again.
A GTT is a table to be created once, just like the rest of the DDL in
Oracle.
--
Galen Boyer
Received on Mon Aug 21 2006 - 19:04:03 CDT
