Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Difference between alias and view defined select * from

    Hi,

    Definition of table:
    create table t1 (c1 integer, c2 integer);

    What is the difference between
    create view v1 as select * from t1;
    and
    create alias a1 for t1;

    Thanks,
    Grofaty

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    Grofaty,

    at the first moment, you don't have any difference between a1 and v1. Every select you make for t1, a1 and v1 should have the same access path. You will have a difference if you change t1 (ALTER TABLE T1 ADD C3 INTEGER). After this change, you can only "see" C3 through t1 and a1. For v1 this new column doesn't exist (just the table columns that existed when you created your view with SELECT *)!

    HTH,
    Rodney
    Rodney Krick

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks, RKrick.

    What about performances. Could you please rank answers a, b or c accourding to execution time?
    a) select * from t1;
    b) select * from v1;
    c) select * from a1;

    Thanks,
    Grofaty

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    None, as far as I understand

    Cheers

    Sathyaram

    Originally posted by grofaty
    Hi,

    Thanks, RKrick.

    What about performances. Could you please rank answers a, b or c accourding to execution time?
    a) select * from t1;
    b) select * from v1;
    c) select * from a1;

    Thanks,
    Grofaty
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    How does DB2 works to get the data from view?

    I think that database reads the view definition and then merge select statement with view definition and then executes the merged select statement. Isn't this slower then just executing the select statement directly to the table, where no merge is needed?

    Thanks,
    Grofaty

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Well, a sort of ...

    The delay/overhead will be in the Prepare Phase of your statement and not in the execution phase ...

    Once the view definition is in the catalog cache, then there is not going to be any read on the system table , ie, for subsequent access to the view, you read the definition from memory (assuming your catalog cache is tuned) ...

    For static SQL, you will see the overhead only in the BIND stage ..

    For dynamic SQL with parameter markers, you bind only once and so little overhead ...

    For dynamic SQL without parameter markers, you will be preparing once for every execution ... If the object definitions are in the cache, as far as I understand, almost every nanosecond of the prepare time is used to generate the access plan and so your view definition is no overhead ..


    Considering these, a select * view, alias or table should all be equally good ..

    HTH

    Sathyaram


    Originally posted by grofaty
    Hi,

    How does DB2 works to get the data from view?

    I think that database reads the view definition and then merge select statement with view definition and then executes the merged select statement. Isn't this slower then just executing the select statement directly to the table, where no merge is needed?

    Thanks,
    Grofaty
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks, sathyaram_s!

    Grofaty

Posting Permissions

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