temporary tables
Sam, December 23, 2012 - 11:12 am UTC
Tom:
Do you agree with the wiki article discussing the problems using temporary table basically that it has no statistics collection and the optimizer will take a while to query it.
http://www.orafaq.com/wiki/Temporary_table It seems they are saying "Avoid temporary tables if you can".
January 04, 2013 - 10:36 am UTC
I agree that you shouldn't use a temporary table if you can use an inline view or a with subquery.
that is - don't fall into a sqlserver programmer paradigm in Oracle - it is OK to join lots of tables in a single query - we prefer that.
If you do use a global temporary table and query it - you will need to address the statistics. There are a few approaches
a) use dynamic sampling, works well if the table is "about" the same size all of the time (is always sort of small - hundreds of rows, or is always medium - thousands of rows, or is always large).
b) use dbms_stats.gather/set statistics to put in place representative statistics and lock them. works well if the table is sometimes large/medium and sometimes small. You would use representative statistics for LARGE and lock them. The theory is that a plan for LARGE tables works well against small ones (typically true) - whereas a plan for SMALL tables performs really really bad against large ones
c) use the cardinality hint in the query for three sizes of tables - small, med and large. The developer *knows* how big the table is (they just loaded after all!). They could put in a hint that tells us "small, medium or large" - say 100 rows, 10,000 rows or 1,000,000 rows (just three values - NOT THE EXACT value - just something representative for the three sizes). That way - we'll have three queries and potentially three different plans in the shared pool.
And look to the future, a new release might change the way things work (check soon)
Temporary table
Rajeshwaran, December 23, 2012 - 11:15 pm UTC
temporary table basically that it has no statistics collection and the optimizer will take a while to query it
Here is what Tom suggests about Temporary table usage from Expert Oracle Database Architecture 2nd edition. <quote>
Temporary tables can be useful in an application where you need to temporarily store a set of rows to be
processed against other tables, for either a session or a transaction. They are not meant to be used as a
means to take a single larger query and break it up into smaller result sets that would be combined back
together (which seems to be the most popular use of temporary tables in other databases). In fact, you
will find in almost all cases that a single query broken up into smaller temporary table queries performs
more slowly in Oracle than the single query would have. I’ve seen this behavior time and time again,
when given the opportunity to rewrite the series of INSERTs into temporary tables as SELECTs in the form
of one large query, the resulting single query executes much faster than the original multi-step process
</quote>
So you don't need statistics on Temporary tables if they are used as an Interface.
Karthik Perumal, December 24, 2012 - 3:43 am UTC
Thanks a ton Tom! Would like an example from ur side though.
Getting away from Temp tables
Jordan, February 03, 2013 - 1:52 am UTC
"don't fall into a sqlserver programmer paradigm in Oracle"
I think you have a similar comment in nearly every other question regarding temporary tables on here. I have limited experience, but from what I have seen lots fo temp tables does seem to be status quo for SQLServer backed applications.
Is there something fundamentally different about SQLServer vs. Oracle that promotes the use of temp tables, or is it more a cultural thing?
February 04, 2013 - 9:58 am UTC
maybe it has changed over the years - but in sql server - if you had more than 3 or 4 tables in a join - really poor performance was the outcome (sybase was that way, microsoft bought the sybase code). so, to take it easier on sqlserver - programmers were just 'taught' that you broke big queries up into small bite sized pieces and glue them back together. This became ingrained and is still promoted (search for
break up large joins in sqlserver using temporary tables
on google for example...). so, even if it isn't true anymore - it has just become "something everyone 'knows'"
and they then try to apply it to ever other database out there. In Oracle - it in general would be a performance impediment - not improvement (meaning - yes - you might find a query here or there that is faster with a temporary table, but usually all you really wanted was a factored query (with'ed at the top of the query) not a global temporary table)
Yes, SQL Server has changed over the years.
Shannon Severance, February 04, 2013 - 6:03 pm UTC
maybe it has changed over the years - but in sql server - if you had more than 3 or 4 tables in a join - really poor performance was the outcomeI have used Microsoft SQL Server 6.5, 7.0, 2000 and 2005.
With 6.5 one still needed to split queries with too many joins would have terrible performance, often in the query optimization step.
Somewhere between 6.5 and 2005 SQL Server it became unnecessary to split many joins across multiple queries feeding temp tables, in most cases. I had thought the issue had been completely eliminated until I tried a particular query that just stunk. Splitting that
one resolved the performance problem with that
one particular query.
My memory is fuzzy, but I believe the biggest improvement with regards to this issue was the jump from 7.0 to 2000.
search for ...on google for example...). so, even if it isn't true anymore - it has just become "something everyone 'knows'"SQL Server is not unique because bad and/or stale information abounds on the internet. The first or second result of using
https://duckduckgo.com/?q=oracle+temporary+tables
is a website I will not name. The snippet shown in the search results reads, "Using temporary tables with Dictionary Views. The prudent use of temporary tables can dramatically improve Oracle SQL performance. To illustrate the concept..."
And yes, everyone knows that one needs to split a query with many joins. I currently work on a team that uses Oracle 10gR2 and 11gR2. From the "best practices" page of our internal wiki:
a. When solving a large problem, logically
and programically break it into pieces.
b. Use a series of CTAS to arrive at your
final result set.
c. Use a series of 2 or 3 table joins to
get to a result set instead of one
large 4 to 'N' table join to complete
it in one step.
For
Oracle! CTAS? Using temporary tables would be an improvement in terms of DB load. And I don't know, look at query plans before deciding that a query must be broken up, maybe? I have include the query plans both ways in comments, to preempt others from "improving" the performance by breaking it in to pieces.
February 06, 2013 - 7:53 am UTC
there is lots of bad advice everywhere... I agree. I usually only hear the advice to break large queries up for Oracle in shops where they were mostly sqlserver programmers becoming Oracle developers...
Code Maintenance Clarity is why we used Temporary Tables
Paul Kern, June 17, 2013 - 7:17 pm UTC
A single query can be so much more difficult to maintain over time, no different that for client side programming languages. We should be able to get good performance with temporary result tables that keep the code logical and easy to maintain.
Having programmed in DB2, then Sybase, then SQL Server, and now Oracle, my preference would be for Oracle to make the use of temporary tables as easy as SQL Server, with temporary table automatically granted life for just the session. Instead we now have queries with subselects that are all the more complex because they appear in a query with joins to a number of tables.
June 18, 2013 - 3:48 pm UTC
A single query can be so much more difficult to maintain over time
with "WITH" clauses and inline views - I disagree. (and they can and will when necessary create temporary tables for that query).
with
view1 as
(select..... ),
view2 as
(select .... ),
view3 as
(select ... )
SELECT ...
from view1, view2, view3;
as opposed to:
insert into #view1
select.....
insert into #view2
select.....
insert into #view1
select.....
select ....
from view1, view2, view3;
and you do have global temporary tables - but you will get better performance with a single query
"WITH" clauses vs. CREATE TEMP TABLE AS SELECT
Sanchita, January 23, 2014 - 1:40 pm UTC
Hi Tom,
We use a reporting application that will by default use Temp tables for storing intermediate query results. In a general scenario, the intermediate queries, can generate medium level of data (in thousands). The queries are of the form-
Create Temp table as Select..
In such case, what would be the difference in respect to how WITH or a single pass query would execute?
Performance with temporary tables
Sandeep Pharande, October 17, 2014 - 9:34 am UTC
Hi Tom,
We are facing one strange issue.
We have procedures for reporting purpose that use temporary tables. Every time we delete data from procedure at start of executing, populate new data into temporary tables and use ref cursors to return data.
Sometimes procedures takes very long time to executes (more than 30 min) even for small result, if we drop temporary table and recreate tables, procedures executes very fast but after few days again performance issue come.
What could be reason that after recreating temporary tables performance increases?
Performance v. "Footprint": using Global Temporary Tables
Loki Sorensen, April 28, 2015 - 11:27 pm UTC
This thread is all about performance. However, if I do not mind trading performance for a smaller "footprint" while working, am I justified in using temp tables in Oracle as the best practice?
Specifically, I just finished working on a reasonably complicated data extract - about a dozen joins, numerous join constraints, summary functions, etc. The goal was to have the least impact on other processes / users using the server at that particular time, i.e., to use the least "amount" of resources on the server at any given time while running the query or "chunk-queries."
From looking at the plans it appeared to me that the query structured as one set of joins would take up more resources (i.e., CPU, memory, I/O access) while running than the same query broken up into four pieces and the "put-together" "chunk-query" pieces. Although summing up the resources used by the five "chunk-queries" to produce the results clearly showed that as a whole they used more resources (120% + time to run each one), each "chunk-query" by itself took significantly (70%) less resources *while running* than the whole query as one multi-join query would have. Should I have instead looked at the WITH in-line views or other methods?