SQL vs Oracle - temp tables

SQL vs Oracle

I ran into this a week ago and have not been completely happy with the Oracle solution. I am not saying that it doesn’t work, but it just seems like there is a better way. (oh look, there’s sql server doing it in a better way)

Here’s the skinny

Temporary tables.

I have been known to throw data sets around inside of a stored proc and SQL Server has this sweet little bit of trickery that lets me have a table variable. Oh yes friends, a variable that acts like a table. I can insert, update, delete and select data from it. It resides in memory and will usually never be written to disk.

Ok, quick history lesson for those of you not following. In SQL server, you can have temp tables and table variables. Temp tables are ALWAYS written to disk. Table variables are only written to disk if there is not enough memory to hold the data. So think about this, if I read from a few tables and put the data into a temp table and then read it back out, what am I doing? Read, write, then read. Your disks are just a spinnin! With a table variable (praise Bob!) it sits in memory, is only viable for the scope of the procedure and I am not spinning disks. Super groovy.

(just a personal opinion, but if you are returning so much data that your table variable needs to write to disk… maybe you should rethink what you are trying to do)

Back on topic.

So what do we have in Oracle land that can do the same thing you ask? Well in a word, nothing. The only alternative I can find is the Global Temporary Table. This is a physical table space created once and it sits there for you to use. Read from the disk, write back to the disk and read again. Ugh. It does have a nice feature though, it keeps session data separate. Session 1 can insert rows and retrieve them without the data being mangled and merged with data from sessions 2-900. Kind of nice, but it still writes to disk.

Coding procedures, especially ones where I have to nab a dataset and then join against it, is super easy when using table variables and the impact is pretty minimal. While global temp tables are not hard to use, they do have some tricksy properties.

If you are using a global temp table you need to be aware of the two different settings.

The basic syntax is:

CREATE GLOBAL TEMPORARY TABLE SOMETABLE ( SOMEID NUMBER, SOMEDATA VARCHAR2 (256) )

The next part if the syntax can be one of two settings

ON COMMIT DELETE ROW – This indicates the data should be deleted at the end of the transaction

Or

ON COMMIT PRESERVE ROWS – This indicates the data should be deleted at the end of the session

I personally use the PRESERVE since I use the temp table multiple times in a session / procedure and not just in one transaction.

In contrast, the table variable holds my data for the session.

I really do prefer the table variable to temp tables, writing data back to disk after I just got it out of there seems wrong to me.

Oh, here is the syntax for creating a table variable

Declare @SomeTable Table (SomeID INT, SomeData Varchar(256) )

--matt