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

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!

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

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

As for the duration of the session, I get that, but here what I mean. I
run the script, I get my results. Then I run the same script, let's say
15 seconds later, I don't get anything.

But with the regular "create table" version, I get my results every
time. That's what confuses me.

Deltones 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
>
> As for the duration of the session, I get that, but here what I mean. I
> run the script, I get my results. Then I run the same script, let's say
> 15 seconds later, I don't get anything.
>
> But with the regular "create table" version, I get my results every
> time. That's what confuses me.

There are two different types of Global Temporary Tables. Go to
http://tahiti.oracle.com and read up on them and Morgan's Library
for demo code to understand how they work.

A GTT is a premanent object. You build it once and never again. It is
not part of the script.

This is Oracle not some other product. To be successful you need to read
and you need to do things Oracle's way.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 21 Aug 2006, vibroverb@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

Deltones schreef:
> 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
>
> As for the duration of the session, I get that, but here what I mean. I
> run the script, I get my results. Then I run the same script, let's say
> 15 seconds later, I don't get anything.
>
> But with the regular "create table" version, I get my results every
> time. That's what confuses me.
>
Are you using two sessions? Because session A has GTT "A" and session B
has GTT "B" - you cannot see GTT B from session A - session A will see
GTT A, not B.

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...