Re: Is there any equivalent of Sql Server's temporary table in Oracle

"gamaz" <gamaz_at_eathlink.net> wrote in message news:aOLH9.5825$hM3.653883_at_newsread2.prod.itd.earthlink.net...
> Hi,
> In sql server, some of the stored procedures I handled using temporary
> tables. The idea was to make joins simplified specially when there are six
> to eight table joins involed in data extraction. Those can are broken up
in
> steps using temporary tables. I was wondering, what would be equivalent
> concept to handle joins of six to eight tables to extract data in Oracle.
I
> know it is possible do crude lengthy joins, but I am sure there would be
an
> elegant way to handle the same. Any insight/advise/link to knowledge site
is
> appreciated in advance.
> Regards
>
>

Depending on the complexity and on available resources, you might want to give a look at Oracle objects called snapshots (aka materialized views). Once you have created a snapshot, you can decide when and how frequently you want Oracle to refresh the data. In datawarehousing environments, where there can be queries taking ages to complete, you would tipically use a batch refresh mode or a scheduled task during off-peak hours. If queries do not involve zillions of records, you can force refreshes using certain built-in packaged procedures.
Of course you are expected to give at least a cursory read to Oracle manuals before tampering with these class of objects. :-)

Bye,
Flavio

PS: you can also create your home-brewn temporary tables management, Oracle does provide global temporary tables, where you can easily store everything you want, but I guess you don't want to reinvent the wheel, do you? Received on Thu Dec 05 2002 - 14:01:12 CST