Re: store data from intermedite SELECT

Vadim Grepan <_RMV_IT_kezal_at_mail.ru> wrote in message news:<bsgo88$ooh$1_at_n6.co.ru>...
> Hello All!
>
> What's the best way to store some data from intermediate select? For
> example, I'm doing large SELECT number #1 and then do some final
> selects:
> SELECT aa .. FROM SELECT #1 -- #2
> ...
> SELECT bb .. FROM SELECT #1 -- #3
> ...
>
> Result from select #2 and #3 will store in table.
>
> There is no need to store SELECT #1 in permanent table or use
> materialize views for that
>
> In other words I need some technics to store as much data in memory as
> possible, without usind hard disk storage. Is GLOBAL TEMPORARY TABLES
> good\effective\fast enough for that?

in a transactional system it is the only way to go if you really need to do that. Are you sure you cant turn your selects into the following:

select stuff
from (select stuff),
(select stuff2)
where stuff = stuff2

You eliminate an insert. This is the best way, if its possible.

Now, alot of people who have not actually tested this will disagree with my following statement... if it is a one time thing and/or if its part of a batch process that is not concerned with concurrency, the following is superior.

create table mytable nologging
as select stuff.

You cannot use that in a system that has significant concurrency. Its fine for a nightly batch process. Do this and dropping the table causes latch contention, but if there arent many other users contending for it and your not say(doing it 500 times in a loop) its not a problem.

Odds are you will see a post saying 'dont do this'. Odds are its by someone who never actually tested it in a batch process. Test it for yourself.

>
> Rgds, Vadim Grepan
Received on Fri Dec 26 2003 - 07:52:58 CST