Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: What is difference between a view and a temp table

    Hi there,

    What is the difference between a view and a temp table?

    If I just want to simplify my queries, I mean instead of making sub queries, I would like to create a view(or temp table) first, in terms of performance, is there any difference?

    Thanks,
    Jinse

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A view is not a real table. It is simply a "filter" on one or more real tables that is converted to an single SQL statement that accesses the real base table at execution time. This is different than a view in some other databases, which are actually materialized as a separate table like a DB2 MQT table.

    A temporary table is a real table that is created within an application program, and goes away when the applicaiton is finished. It is faster than creating a regular table and then dropping it within an application (becasue the temp table is not recorded in the DB2 catalog). Each instance of an application can have its own version of the temp table without having to come up with a unique name for the table. Only the application thread that creates the temporary table can access it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2006
    Posts
    33
    Quote Originally Posted by Marcus_A
    This is different than a view in some other databases, which are actually materialized as a separate table like a DB2 MQT table.
    I don't quite understand this sentence. Are you saying some db stores data in the view?


    Please allow me to summarize:
    1) View is just a definition, it is not storing any data. So every time it is used, it queries data from the real source table. If the data in source table changes, you will see the changes from the view as well. In terms of performance, it is not going to help, maybe worse? BTW, a view exists after the session.

    2) Temp table is really storing the data, so it faster the performance, but it only exist within the session. And if the data in source table changes, you won't automatically see the changes from the temp table unless you re-create it.

    Is it correct?

    Thanks,
    Jinse

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by jinsezh
    I don't quite understand this sentence. Are you saying some db stores data in the view?


    Please allow me to summarize:
    1) View is just a definition, it is not storing any data. So every time it is used, it queries data from the real source table. If the data in source table changes, you will see the changes from the view as well. In terms of performance, it is not going to help, maybe worse? BTW, a view exists after the session.

    2) Temp table is really storing the data, so it faster the performance, but it only exist within the session. And if the data in source table changes, you won't automatically see the changes from the temp table unless you re-create it.

    Is it correct?

    Thanks,
    Jinse
    1. You are mostly correct. There is no signifigant performance issue for views. Once created, a view is permanant until it is dropped or one of the reference objects is dropped. Please refer to the SQL Reference Vol 2, CREATE VIEW for examples.

    2. When a temp table is created (just like when a regular table that is created) it has no data in it. Please refer to the SQL Reference Vol 2, for DECLARE GLOBAL TEMPORARY TABLE for examples.

    Your may also be interested in Materialized Query Tables.

    A materialized query table is a table whose definition is based on the result of a query. As such, the materialized query table typically contains pre-computed results based on the data existing in the table or tables that its definition is based on. If the SQL compiler determines that a query will run more efficiently against a materialized query table than the base table or tables, the query executes against the materialized query table, and you obtain the result faster than you otherwise would.

    Please refer to the Administration Implementation Guide, Creating a materialized query table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2016
    Posts
    24
    Provided Answers: 1
    Table: Table is a preliminary storage for storing data and information in RDBMS. A table is a collection of related data entries and it consists of columns and rows.

    View: A view is a virtual table whose contents are defined by a query. Unless indexed, a view does not exist as a stored set of data values in a database.

    Advantages of view over table are

    > Ease of use: A view hides the complexity of the database tables from end users. Essentially we can think of views as a layer of abstraction on top of the database tables.
    > Space savings: Views takes very little space to store, since they do not store actual data.
    > Additional data security: Views can include only certain columns in the table so that only the non-sensitive columns are included and exposed to the end user. In addition, some databases allow views to have different security settings, thus hiding sensitive data from prying eyes.

    For more information, you can take the help of this link: http://sqltechtips.blogspot.com/2016/01/difference-between-tables-views.html

Posting Permissions

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