Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    23

    Unanswered: Views Versus Temporary Tables

    Hi

    I'm creating stored procedures in Oracle and would like to ensure I apply "best practice". When I developed in Sybase I used temporary tables quite a lot.

    Now I wonder in Oracle if I should use views or temporary tables when I need to create small data sets and work with them. The procedures are complicated so there is no way to avoid using either of these.

    Which is better a view or a temporary table? Is there certain instances when I should choose 1 over the other?

    In addition I have read that indexes can be created on Global Temporary Tables but I have not been able to get any of the recommended syntax to work

    many thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In Oracle temporary tables should not often be required. Use views or "inline views" to construct your queries.

    If you do need a temporary table, then Global Temporary Tables (GTTs) are the right thing to use, and unlike in some other DBMSs these are permanent tables - i.e. they get created once and used many times, not created and dropped at run time.

    What is your problem with creating indexes on GTTs? The syntax is no different than on any other table:
    Code:
    SQL> create global temporary table gtt2 ( id integer primary key, name varchar2(10) );
    
    Table created.
    
    SQL> create index gtt2_idx on gtt2(name);
    
    Index created.
    But "best practice" would be not to use GTTs as an every day solution to all problems: only use them when you really havea need to temporarily store some data, not as a way of "factoring" your queries.

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    You can also use PL/SQL tables. These use the servers memory, and are only avialable to the program during execution, and obviously, the data is session specific (like GTTs).

    I usually use PL/SQL tables for smaller sets of data or for use with Bulk Collects, and GTTs for larger sets of data.

    Both can work in most cases. Just gotta choose the best option for the requirements.

    HIH

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >When I developed in Sybase I used temporary tables quite a lot.
    Exactly why were temporary tables used alot?
    What problem is/was being solved by them in Sybase?
    Depending upon why/how they were being used in Sybase,
    they may be completely unnecessary in Oracle.
    With Oracle readers NEVER block writers & writers NEVER block readers.
    Oracle guarentees readers a consistent view of the database which existed
    at the start of their query; regardless of subsequent changes to data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2004
    Posts
    23
    many thanks for the replies - the temp tables were required in sybase because the data is manipulated in different ways before being presented to the user - they are not straightforward queries.

    carloa - you suggest I use pl/sql tables, are these different from the ordinary oracle tables? If yes could you provide syntax for the creation statements

    kind regards

  6. #6
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by kerrie
    carloa - you suggest I use pl/sql tables, are these different from the ordinary oracle tables? If yes could you provide syntax for the creation statements

    kind regards
    kerrie, actually PL/SQL tables (now called Associative arrays) are what they are called now "Arrays". They are not tables at all.
    I have an aversion to temporary tables. I use these array thingy's and inline views for everything. I'd be hard pressed to have to use a temporary table for anything.
    Another thing you can do to perform complex manipulations on data is create stored functions and use them in SQL.

    Here's something on arrays:
    http://download-west.oracle.com/docs...olls.htm#35359
    My way or the highway. Yeah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •