This discussion is archived
12 Replies Latest reply: Mar 31, 2013 5:50 PM by Justin Cave RSS

Temporary Tables

$phinx19 Newbie
Currently Being Moderated
Hi All,

What is the purpose of Oracle temporary tables? Is it just for sorting purpose or there is something more to it? Is sorting good when it happens in the temporary table?

Regards,
Sphinx
  • 1. Re: Temporary Tables
    sb92075 Guru
    Currently Being Moderated
    $phinx19 wrote:
    Hi All,

    What is the purpose of Oracle temporary tables? Is it just for sorting purpose or there is something more to it? Is sorting good when it happens in the temporary table?

    Regards,
    Sphinx
    post URL to Oracle documentation where "temporary tables" are discussed.
  • 2. Re: Temporary Tables
    $phinx19 Newbie
    Currently Being Moderated
    This is one of the link that I was able to find.

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm
  • 3. Re: Temporary Tables
    rp0428 Guru
    Currently Being Moderated
    >
    What is the purpose of Oracle temporary tables? Is it just for sorting purpose or there is something more to it? Is sorting good when it happens in the temporary table?
    . . .
    This is one of the link that I was able to find.
    >
    Well doesn't that link answer your question about the purpose?
    >
    Temporary tables are useful in applications where a result set is to be buffered (temporarily persisted), perhaps because it is constructed by running multiple DML operations
    >
    Nothing in that doc mentions sorting in relation to temporary tables. Temp tables and sorting have nothing to do with each other.

    You generally don't need temporary tables. Many developers converting from sql server to Oracle tend to use, and overuse, temp tables because they are used to needing them in sql server. That isn't true in Oracle.
  • 4. Re: Temporary Tables
    sybrand_b Guru
    Currently Being Moderated
    It is not for sorting purposes. For sorting purpose you have the pga and/or your temporary tablespace.
    That said, in a proper Oracle application one almost never need, unlike other databases like Sqlserver, temporary tables.


    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 5. Re: Temporary Tables
    $phinx19 Newbie
    Currently Being Moderated
    Hi All,

    I do not recall from where this has got into my head, I have always linked temporary tables with sorting.
    Thanks for correcting me.
  • 6. Re: Temporary Tables
    sb92075 Guru
    Currently Being Moderated
    $phinx19 wrote:
    Hi All,

    I do not recall from where this has got into my head, I have always linked temporary tables with sorting.
    Thanks for correcting me.
    when was the last time you sorted anything?
  • 7. Re: Temporary Tables
    Justin Cave Oracle ACE
    Currently Being Moderated
    Perhaps you were confusing temporary tables with the temporary tablespace?

    A temporary tablespace, among other things, is used for sorts that are too large for the session's PGA. A temporary tablespace is also (generally) used to store the data that is in a temporary table.

    Justin
  • 8. Re: Temporary Tables
    $phinx19 Newbie
    Currently Being Moderated
    Hi SB,

    As far as I can remember, I never sorted anything, I have just read it from somewhere and as Justin said perhaps I mixed temporary tablespace with temporary tables. :(

    My BAD!

    Regards,
    Sphinx.
  • 9. Re: Temporary Tables
    user639304 Explorer
    Currently Being Moderated
    Hi Justin,

    I thought that a temporary table is necessarily stored in a temporary tablespace. Could you please give a test case showing that it's possible for a temporary table to be stored outside a temporary tablespace?

    Thanks.
  • 10. Re: Temporary Tables
    rp0428 Guru
    Currently Being Moderated
    >
    I thought that a temporary table is necessarily stored in a temporary tablespace. Could you please give a test case showing that it's possible for a temporary table to be stored outside a temporary tablespace?
    >
    No - because as your first sentence says the data for a temp table IS stored in a temp tablespace. That is how Oracle can so easily get rid of the data when you are done with it (when you COMMIT, ROLLBACK or end the session).

    But the data does not have to be stored in YOUR temp tablespace. You can specify a different one.

    You posted a doc link. Right there on the first page is a link to the section 'Creating a Temporary Table'.

    And that section has an example.
    >
    By default, rows in a temporary table are stored in the default temporary tablespace of the user who creates it. However, you can assign a temporary table to another tablespace upon creation of the temporary table by using the TABLESPACE clause of CREATE GLOBAL TEMPORARY TABLE. You can use this feature to conserve space used by temporary tables. For example, if you need to perform many small temporary table operations and the default temporary tablespace is configured for sort operations and thus uses a large extent size, these small operations will consume lots of unnecessary disk space. In this case it is better to allocate a second temporary tablespace with a smaller extent size.

    The following two statements create a temporary tablespace with a 64 KB extent size, and then a new temporary table in that tablespace.

    CREATE TEMPORARY TABLESPACE tbs_t1
    TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON
    MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

    CREATE GLOBAL TEMPORARY TABLE admin_work_area
    (startdate DATE,
    enddate DATE,
    class CHAR(20))
    ON COMMIT DELETE ROWS
    TABLESPACE tbs_t1;
  • 11. Re: Temporary Tables
    user639304 Explorer
    Currently Being Moderated
    Hi rp0428,

    Thanks for your answer. But I asked the question because Justin wrote:

    >
    A temporary tablespace is also (generally) used to store the data that is in a temporary table.
    >
    In my opinion, the word "generally" he used implies that in some cases, a temporary table is not stored in a temporary tablespace. Or, probably, I haven't understood what he meant (and that was why I wanted him to share some light on this).
  • 12. Re: Temporary Tables
    Justin Cave Oracle ACE
    Currently Being Moderated
    I believe I made an editing mistake. I was originally going to say that the data in a temporary table is generally stored in the user's default temporary tablespace. I edited that to take out the unnecessary distinction between default and non-default temporary tablespaces and incorrectly left in the "generally" qualifier.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points