Using temp tables across platforms (especially Oracle)

Developers
1

A lot of our SQL statements currently rely on temp tables. In Postgres and SQL Server these have very nice properties: they’re only visible to the current user, are automatically dropped when the session ends, and can be created even when the user has no write access. In Oracle, temp tables are basically permanent tables, with the only difference that the data inside the table is only visible to the current user.

Our current solution is to

  • Always set the current schema to somewhere where the user has write access (the results schema they specified), and adding explicit schema references to all other tables (e.g. @cdm_schema.dbo.person).
  • Add truncate table and drop table statements for temp tables at the beginning and end of the SQL code.
  • Generate random temp table names so they cannot conflict with the temp tables of another user (haven’t done that yet)

Of course this sucks. Trying to read up on why Oracle is so bad with temp tables, it seems they’re advocating the use to CTEs so the optimizer gets a shot at being cleverer than the user. I will see if I can add CTEs to SqlTranslate (shouldn’t be a problem), but I have two reservations about them:

  1. I don’t share Oracle’s confidence in the optimizer
  2. Using CTEs makes code that much harder to debug

Any thoughts?

2

Martijn,

I agree with your instincts (i.e., not adding more work or trusting the optimizer). One thing I might suggest is for Oracle SQLTranslate, just abandon the use of temporary tables and instead create and delete real ones. I say that because you are basically doing the some thing any way, but they will not be lost if your session crashes (e.g., if you had successfully run 16 of 17 queries, hate to have to start over for that last one).

Jon

3

BTW, Patrick did note that it was better to use temporary tables for SQL Server performance, so sounds good. But for Oracle, since you don’t get the memory benefits, might as well get the safety benefits of real tables in case the session crashes. I just looked at my temp tables created last night for our prep work, and sure enough, all the temporary tables are still visible to me but they are totally empty. Luckily I had backed my first couple query results to real tables just in case.

4

I think a solution where MS SQL (main-code; input code) has temp tables and they get translated into Oracle Dialect somehow seamlessly is important.

For me, the biggest problem is authoring my code in MS SQL flavor without having CDM data on MS SQL server.

My code is authored in RedShift dialect (using as many constructs of MS SQL dialect as possible).

For this problem - it would be best to have a “coder friend” who would be willing to test a given code for me on MS SQL platform. :smile:

Or support more REDSHift-MS dialect also as input code dialect as well.

5

You’ll be happy to learn (hopefully) that a web interface that allows the testing of template sql across all supported database platforms is due for release in 2-3 weeks.

1 Like
6

If I am developing in R - is there a way via devtools to somehow install the “preview version” of SqlRender?

this one:
https://github.com/OHDSI/SqlRender/tree/unique_oracle_temp_tables

something like
install_github(user/project/???BRANCH???)

@schuemie