3

I've been working in Oracle World for 3 weeks after working in SQL Server Land for more than 4 years. Right now I'm finding Oracle's lack of local temp tables baffling.

In lieu of a data warehouse for reporting I've often been responsible for putting together reports from large amounts of normalized data. I quickly learned that cramming all of the logic into one gigantic query (i.e. one with many joins, sub-queries, correlated sub-queries, unions, etc.) was a recipe for terrible performance. Properly breaking the process into smaller steps and utilizing indexed temp tables (that you could create and alter on the fly within a procedure or an ad-hoc script) was often exponentially faster.

Enter Oracle... no local temp tables. I apparently can't even CREATE a global temporary table without being granted the permission to create permanent tables as well. I Googled "oracle temp table permission" and the first link returned is a forum question where the accepted answer starts with "As has been pointed out, it would be extremely unusual to want to have a user that could create global temporary tables but not permanent tables. I'm very hard-pressed to imagine a scenario where that would make sense." That's exactly what I could use in our prod environment. My SQL Server mind is blown.

I can almost accept having ONLY global temp tables to work with but is it really that unusual in Oracle to use them in this manner? What, if anything, can I do to implement some sort of similar step by step logic without using temp tables? How, within an ad-hoc script, can I save off and later reuse something similar to an indexed set of data? I'm obviously looking for something other than a subquery or a CTE.

I must be missing something...

anthony444
  • 41
  • 4
  • 1
    I like your approach to complex queries. As regards temporary tables. Maybe interesting? [How to create a temporary table in Oracle](http://stackoverflow.com/questions/2671518/how-to-create-a-temporary-table-in-oracle.). I suspect you can work in a similar manner as before? – Ryan Vincent Sep 08 '16 at 00:30
  • Materialized views, perhaps? https://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#DWHSG008 –  Sep 08 '16 at 02:11
  • If you look little bit ahead towards advanced oracle, you will find that you can play alot in memory. You can create records,objects to temporarily hold your data and intermediate query resultset. – XING Sep 08 '16 at 09:04
  • Thanks for the suggestions. I posed the question to the "Ask Tom" guys: https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9531849900346330282. I wasn't very encouraged by their feedback but the example I provided should clearly illustrate the kind of situation I'm talking about. In the end it doesn't sound like there's a viable alternative to GTTs and the elevated permissions required to "set them up". – anthony444 Sep 19 '16 at 15:00

1 Answers1

0

unfortunalty we don't have such a privilege.but as workaround you can revoke any quota on permanent tablespaces then the user can't create any permanent table.of course in 11g with deferred segment creation feature users can create table but they cann't insert any row. because temp tables uses of temporary tablespaces they won't have any problem. Mohsen

mohsen.b
  • 436
  • 2
  • 8