Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    5

    Unanswered: DB2 View Join Vs Table Join which is efficient

    Is View Join is more efficient than Table join. I have Views V1 and V2 and i have created Tables T1 and T2 with same columns in V1 and V2 resp.

    i have written a join query on T1 and T2 but they are taking more time than executing same join query on V1 adn V2.

    Please help me to expalin this scenario

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There should be no practical difference. In theory, there is very, very slight extra amount of time required to process a query against views as compared to a query written directly against the base tables, but it may not even be measureable.

    If you post the table DDL, including indexes, and the queries, then we can possibly provide an explanation.
    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
    Nov 2006
    Posts
    5
    View Join Query

    SELECT count(*) FROM SYSCAT.TABLES tab,SYSCAT.COLUMNS cols WHERE tab.TYPE in ('V','W') AND tab.TABNAME = cols.TABNAME AND tab.TABSCHEMA = cols.TABSCHEMA AND tab.TABSCHEMA IN ('TEST')

    Table Join Qury

    SELECT count(*) FROM T_DB2_TABLES tab,T_DB2_COLUMNS cols WHERE tab.TYPE in ('V','W') AND tab.TABNAME = cols.TABNAME AND tab.TABSCHEMA = cols.TABSCHEMA AND tab.TABSCHEMA IN ('TEST')

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by sunil_patil
    View Join Query

    SELECT count(*) FROM SYSCAT.TABLES tab,SYSCAT.COLUMNS cols WHERE tab.TYPE in ('V','W') AND tab.TABNAME = cols.TABNAME AND tab.TABSCHEMA = cols.TABSCHEMA AND tab.TABSCHEMA IN ('TEST')

    Table Join Qury

    SELECT count(*) FROM T_DB2_TABLES tab,T_DB2_COLUMNS cols WHERE tab.TYPE in ('V','W') AND tab.TABNAME = cols.TABNAME AND tab.TABSCHEMA = cols.TABSCHEMA AND tab.TABSCHEMA IN ('TEST')

    syscat.tables view is derived from sysibm.systables which is properly indexed.
    IBM00 +CREATOR+NAME
    IBM22 +INDEX_TBSPACE
    IBM23 +LONG_TBSPACE
    IBM78 +ROWTYPESCHEMA+ROWTYPENAME+CREATOR+NAME
    IBM21 +TBSPACE
    IBM137 +TID+FID

    are you sure the two tables that you have created T_DB2_TABLES ,T_DB2_COLUMNS are properly indexed ??

    --Rahul

Posting Permissions

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