Re: Sybase vs Oracle: Temp Tables

Jerry Gitomer wrote in message <35D86C7A.5071_at_ictgroup.com>...
>Hi Barry,
>
> Maybe I have been brainwashed by Oracle, but I don't see the need for a
>temporary table. Other than not having to explicitly create and drop a
>table what does it buy me to have temporary tables?
>
>regards
>
>Jerry
>

Hi Jerry,

Certainly, anything you can do with temporary tables can be done with persistent tables. However, I can see several advantages to temporary tables (my info is based on Oracle Rdb and may not be applicable to Sybase).

1. If multiple individuals sharing the same schema (i.e., username) simultaneously execute code to create a temporary table, they will each get there own copy (even though it has the same name and seems to be in the same schema). This guarantees that they will not run into problems such as locking or looking at someone else's data.
2. If the session is aborted, the temporary table is automatically cleaned up.
3. Since the storage is not meant to be persistent, there is more chance for optimization.

Comments?

--
Barry L. Wallis, Senior Systems Engineer
Science Applications International Corporation
4161 Campus Point Court
San Diego, CA 92121

voice: (619) 646-9891, fax: (619) 646-9327
mailto: Barry.L.Wallis_at_cpmx.saic.com
                                                       



>Barry L. Wallis wrote:

>>

>> Fuzzy wrote in message <35d22eae.3183691_at_newshost.interact.net.au>...

>> >On 12 Aug 1998 11:37:03 -0500, arenson_at_swan.imgen.bcm.tmc.edu (Andrew

>> >D. Arenson) wrote:

>> >

>> >>

 [...]


>> >> In Sybase, I'm used to creating temporary tables on the fly

>> >>without having to run the 'create table' command, by using the

>> >>following syntax:

>> >>

>> >> select ...

>> >> into #TMP TABLE

>> >> from ...

>> >> where ...

>> >>

>> >>This doesn't work in Oracle (as far as I know). In fact, the

>> >>'SELECT ... INTO' syntax appears to be used for some sort of single

>> >>row loading of data into variables that I haven't fully explored yet.

>> >>

 [...]


>> >Oracle is the only major RDBMS that doesn't have this. (Sybase, MS SQL

>> >Server, Informix, DB2 all have it)

>> >

>>

>> The funny part is that although the Oracle RDBMS doesn't have this,

 Oracle


>> Rdb (Oracle's other relational database) does. Maybe someday they will

 take


>> the technology and migrate it into their core database.

>>

>>                                                        

>> Barry L. Wallis, Senior Systems Engineer

>> Science Applications International Corporation

>> 4161 Campus Point Court

>> San Diego, CA 92121

>>

>> voice: (619) 646-9891, fax: (619) 646-9327

>> mailto: Barry.L.Wallis_at_cpmx.saic.com

>>                                                        

>>

>>     ---------------------------------------------------------------

>>

>>                    Name: Barry L. Wallis.vcf

>>     Part 1.2       Type: unspecified type (application/octet-stream)

>>                Encoding: quoted-printable

>

>--

>Jerry Gitomer         ICT Group

>jgitomer_at_ictgroup.com Langhorne PA

>jgitomer_at_yahoo.com Opinions are mine not those of ICT Group


--