January 14, 2016 at 6:38 am
OK...so I get that in Oracle you have Global Temp Tables whose structure is permanent, but whose data is temporary, so you can get something similar to what you have with SQL Server, except that you don't create the TT inside your SP instead you create it 'as part of the schema.'
Yet...somehow I don't like this....when I browse for my tables, I'm going to see hundreds of these temp tables cluttering up the list.
I can't help wondering if I'm missing the point.
Does the typical PL/SQL developer have a different approach to work tables? Tables which hold an intermediary result set which is required later in the script. I know I can use e.g. CTEs, but there scope is limited to a single statement, and while I do like CTEs in general, on occasions I've ripped them out and replaced them with temp tables for performance reasons.
So what does the Oracle dev do in these situations? The only other thing that springs to mind for persisting a recordset is a ..... cursor! I know that this isn't a dirty word in Oracle like it is in SQL - but I still don't understand fully why not.
I'm finding the learning curve from SQL to Oracle steeper than I imagined....and that bit of the curve where I currently find myself really sucks.
Thanks for any input!
David McKinney.
January 14, 2016 at 7:01 am
You have many options when you need to use Temp object in SQL Server:
•Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or the programmable object ends.
•Local temp tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed. Temp tables created in a stored procedure (SP) can be referenced by queries in the SP, sub SPs, triggers fired by the affected tables of the SP.
•Global temp tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
•TempDB permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the sql server is restarted.
And the CTE is a kind of a Temp object.
Igor Micev,My blog: www.igormicev.com
January 14, 2016 at 7:12 am
...and I thought I was missing the point! 😉
My question relates to ORACLE!
Thanks anyway.
January 14, 2016 at 7:18 am
Igor Micev (1/14/2016)
You have many options when you need to use Temp object in SQL Server:•Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or the programmable object ends.
•Local temp tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed. Temp tables created in a stored procedure (SP) can be referenced by queries in the SP, sub SPs, triggers fired by the affected tables of the SP.
•Global temp tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
•TempDB permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the sql server is restarted.
And the CTE is a kind of a Temp object.
There are some caveats that you have to keep in mind...
For table variables, understand that they will not and cannot be made to use statistics and, unless you force a recompile, will be evaluated as never containing more than a single row, which could seriously affect performance.
Local temp tables don't just close when the connection that created it closes. Temp Tables drop when the object (usually a stored procedure) that created them ends. If you have a stored procedure that calls a stored procedure that creates a temp table, the calling procedure cannot see the temp table.
If you do use Global temp tables, be very aware that they can cause serious concurrency issues because of usage persistence.
Be aware that putting real tables in Temp can be a bit of a problem. I'd leave Temp DB alone. If you want such permanent tables to be allow, create a separate "ScratchPad" database in the SIMPLE recovery mode and use that, instead. Of course, remember that's similar to using Global temp tables in that there may be concurrency issues not yet realized.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2016 at 7:31 am
Thanks Jeff...although.....
Hmmmm.....I must have been unclear in my question....but I'll say it again....I'm asking about Oracle (hence I posted in the Working with Oracle forum.)
.....I hate for your salient contributions to go to waste!
January 14, 2016 at 7:35 am
David McKinney (1/14/2016)
Thanks Jeff...although.....Hmmmm.....I must have been unclear in my question....but I'll say it again....I'm asking about Oracle (hence I posted in the Working with Oracle forum.)
.....I hate for your salient contributions to go to waste!
No problem. I was just responding to Igor's good post, which was based on SQL Server.
As for Oracle, I no longer have a clue on Temp Tables there. I only used them a couple of times and the last time I used Oracle was more than a decade ago, so I can't actually help there. Apologies for that but maybe this will act as a bump for your good post and someone who knows more about it will see it.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2016 at 7:51 am
Jeff Moden (1/14/2016)
Igor Micev (1/14/2016)
You have many options when you need to use Temp object in SQL Server:•Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or the programmable object ends.
•Local temp tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed. Temp tables created in a stored procedure (SP) can be referenced by queries in the SP, sub SPs, triggers fired by the affected tables of the SP.
•Global temp tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
•TempDB permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the sql server is restarted.
And the CTE is a kind of a Temp object.
There are some caveats that you have to keep in mind...
For table variables, understand that they will not and cannot be made to use statistics and, unless you force a recompile, will be evaluated as never containing more than a single row, which could seriously affect performance.
Local temp tables don't just close when the connection that created it closes. Temp Tables drop when the object (usually a stored procedure) that created them ends. If you have a stored procedure that calls a stored procedure that creates a temp table, the calling procedure cannot see the temp table.
If you do use Global temp tables, be very aware that they can cause serious concurrency issues because of usage persistence.
Be aware that putting real tables in Temp can be a bit of a problem. I'd leave Temp DB alone. If you want such permanent tables to be allow, create a separate "ScratchPad" database in the SIMPLE recovery mode and use that, instead. Of course, remember that's similar to using Global temp tables in that there may be concurrency issues not yet realized.
Thanks Jeff,
I'm aware of the points you mentioned. However my post was just a counting of the temp objects in use.
Igor Micev,My blog: www.igormicev.com
January 27, 2016 at 7:02 am
David, if you find a good solution to this question, please post it back here. I am also struggling with the lack of session-specific temp tables in Oracle to hold intermediate results.
Quick example: I had several different queries I wanted to run in one query window, and for each of them I needed to join the tables to a derived table: a small result set returned by some convoluted logic. In SQL Server, I'd dump the output of the convoluted logic into a temp table and then simply join each of the main queries to that temp table. I could then execute any of the main queries I liked, in any order, without needing to run all of them as I would if I'd used a CTE instead of a temp table.
Everything I've read says to stop thinking about using temp tables in Oracle, but I've not found a good solution for those situations that seem to be a challenge to code around.
Good luck, and hope to hear from you!
Rich
January 27, 2016 at 8:24 am
No great revelations I'm afraid. I can just give you what I think is the Oracle approach.
If I understand you correctly, you're in a query window, rather than a module / package / stored proc.
If the CTE approach doesn't work for you (e.g. if you want to use the same temp results in more than one way), then I guess you should create a global temporary table. Now this is session specific in the sense that the data in the table is specific to the session. Other sessions can see your table, but they can't see your data.
There are a lot of things that are just done differently in Oracle - and I'm thinking I just have to stop fighting this and just do what everyone else does. It's like moving to another country, where everything's familiar yet done differently to home. While I'll always be more 'at home' with SQL Server, I've got to assimilate when living in Oracleland!
It sounds like we're on similar paths....interested to hear of your progress.
I've just done the Oracle Fundamentals of SQL exam....not easy! Next up is PL/SQL...and I'm just really not sure if I even want to learn it.
January 27, 2016 at 8:45 am
Disclaimer first: I have never used Oracle. So take the below with a huge helping of salt, please.
However, I have heard many times that Oracle, unlike SQL Server, does not have a huge performance difference between set-based and row-based operations. In other words, using a cursor or other iterative approach in Oracle will not hurt you quite as bad as it does in SQL Server.
So for the problem as described by Rich, I am assuming that the typical Oracle way of handling it would be to create a cursor for the first convoluted-logic query, then step through the results and handle the rest of the logic for each row individually.
January 28, 2016 at 12:43 am
Hugo Kornelis (1/27/2016)
Disclaimer first: I have never used Oracle. So take the below with a huge helping of salt, please.However, I have heard many times that Oracle, unlike SQL Server, does not have a huge performance difference between set-based and row-based operations. In other words, using a cursor or other iterative approach in Oracle will not hurt you quite as bad as it does in SQL Server.
So for the problem as described by Rich, I am assuming that the typical Oracle way of handling it would be to create a cursor for the first convoluted-logic query, then step through the results and handle the rest of the logic for each row individually.
I worked with Oracle for only 3 years but, in that time, properly written set based code smokes cursors even in Oracle. The really bad part is (or at least was), a SELECT can't be used to return a result set directly to a GUI. Instead, it's usually better if you write a Global Reference Cursor and embed it in the same package as the procs. Because of the way those are built, they really good at speed. Cursors inside of stored procedures? Not so much. It is true that they don't hurt as bad as they do in SQL Server but they're still comparatively very slow compared to properly written set based code.
The problem is that you sometimes can't get away from cursors. Even triggers are row based. You write the code you want and the say "FOR EACH ROW". Again and thankfully, triggers are another place where cursors are too bad... especially since they're the only show in town there.
As a bit of a sidebar, I do wish that MS would build true "BEFORE" triggers like what they have in Oracle. Makes validations a whole lot easier and really helps avoid rollbacks due to illegal data trying to go into a table.
I'm still sold on SQL Server, though. You've just absolutely gotta love TempDB and temporary tables. I also love the fact that it allows me to bend the rules when I need to. Oracle is comparatively very rigid. And I absolutely hate the UPDATE with no FROM clause. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply