Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2013-02-01T09:28:09Z by ParulJ
ParulJ
ParulJ
5 Posts
ACCEPTED ANSWER

Pinned topic Generated columns in Table Joins : leading to inefficiency

‏2013-01-29T08:02:21Z |
I have two tables A & B.
create table A( acol varchar(10), acol_upper varchar(10) generated always as upper( acol ), acol1 double, acol2 datetime, acol3 varchar(10), ....... acol100 );
create table B( bcol varchar(10), bcol_upper varchar(10) generated always as upper( bcol ), bcol1 double, bcol2 datetime, bcol3 varchar(10),....... bcol100 );
create index mySchema.XAUP on mySchema.A( acol_upper ) allow reverse scans;
create index mySchema.XA on mySchema.A( acol3 ) allow reverse scans;
create index mySchema.XBUP on mySchema.B( bcol_upper ) allow reverse scans;
create index mySchema.XB on mySchema.B( bcol3 ) allow reverse scans;

Query 1 : select a.acol4,a.acol5.... a.acol100 from A
left outer join B
on A.acol = B.bcol
where A.acol3 in ( ... );

Query Plan 1 : Index Scan on table A ( XA ).
Comments : Expected behavior. No columns are being selected from Table B & we are doing a LOJ to TableB, therefore Table B is not in the plan.

Query 2 : select a.acol4,a.acol5.... a.acol100 from A
left outer join B
on Upper(A.acol) = Upper(B.bcol)
where A.acol3 in ( ... );

Query Plan 2 : Index Scan on table A ( XA ). Additional NL Join with Table B using index XBUP.
Comments : Unexpected behavior. Not sure why we have a NL Join with TableB using index XBUP even even though no columns have been selected from Table B.
PS : We are aware that an Upper( acol ) will redirect us to acol_upper.

Query 3 : select a.acol4,a.acol5.... a.acol100 from A
left outer join B
on A.acol_upper = B.bcol_upper
where A.acol3 in ( ... );

Query Plan 3 : Index Scan on table A ( XA ). No additional join with Table B.
Comments : Expected behavior. No columns are being selected from Table B & we are doing a LOJ to TableB, therefore Table B is not in the plan.

Question : Can someone explain query plan 2 ? Why do we have Table B in the picture when we introduce a join on upper( acol ) = upper( bcol ) ?
Updated on 2013-02-01T09:28:09Z at 2013-02-01T09:28:09Z by ParulJ
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: Generated columns in Table Joins : leading to inefficiency

    ‏2013-01-29T13:02:50Z  in response to ParulJ
    > Query 1 : select a.acol4,a.acol5.... a.acol100 from A
    > left outer join B
    > on A.acol = B.bcol
    > where A.acol3 in ( ... );

    > Query Plan 1 : Index Scan on table A ( XA ).
    > Comments : Expected behavior. No columns are being selected from Table B & we are doing a LOJ to TableB, therefore Table B is not in the plan.

    Even if you didn't select columns from Table B,
    result rows should be duplicated by the number of duplicated values of B.bcol.
    (Though it might not your desired result, general definition of join would guide to the result.)

    So, I guessed that B.bcol might be unique (by an unique constraint and/or by an unique index),
    because DB2 choosed access path without accessing Table B.

    If my guess was right,
    B.bcol_upper might have no unique attribute.
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: Generated columns in Table Joins : leading to inefficiency

      ‏2013-01-29T13:11:50Z  in response to SystemAdmin
      Sorry,
      I might do some mistakes.

      Query 3 joined on A.acol_upper = B.bcol_upper,
      and Query Plan 3 showed "No additional join with Table B".

      So, B.bcol_upper might be unique, too.
      Access plan of Query 2 might suggest insufficient opimizaion of DB2.
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: Generated columns in Table Joins : leading to inefficiency

    ‏2013-01-29T14:47:32Z  in response to ParulJ
    Parul,

    I am unsuccessful in an attempt to repro your problem.
    Here is what I get. As you can see table B is indeed not referenced.
    Perhaps if you could post you db2exfmt output.

    create table A( acol varchar(10), acol_upper varchar(10) generated always as (upper( acol )), acol1 double, acol2 date, acol3 varchar(10), acol100 varchar(10));
    create table B( bcol varchar(10), bcol_upper varchar(10) generated always as (upper( bcol )), bcol1 double, bcol2 date, bcol3 varchar(10), bcol100 varchar(10));
    create index XAUP on A( acol_upper ) allow reverse scans;
    create index XA on A( acol3 ) allow reverse scans;
    create index XBUP on B( bcol_upper ) allow reverse scans;
    create index XB on B( bcol3 ) allow reverse scans;

    explain plan for select a.acol100 from A
    left outer join B
    on Upper(A.acol) = Upper(B.bcol)
    where A.acol3 in ( 'hello', 'world');

    db2exfmt -d test -o plan.exfmt -1

    Rows
    RETURN
    ( 1)
    Cost
    I/O

    0
    HSJOIN<
    ( 2)
    0.0299059
    0
    /----------+-----------\
    0 0
    IXSCAN FETCH
    ( 3) ( 4)
    0.0147846 0.0151213
    0 0
    | /------+------\
    0 0 0
    INDEX: ADMINISTRATOR IXSCAN TABLE: ADMINISTRATOR
    XBUP ( 5) A
    Q5 0.0147846 Q3
    0
    |
    0
    INDEX: ADMINISTRATOR
    XA
    Q3
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: Generated columns in Table Joins : leading to inefficiency

      ‏2013-01-29T14:51:07Z  in response to SystemAdmin
      Trying to fix the formatting
       Rows
      RETURN
      ( 1)
      Cost
      I/O
      <table> <tr> </tr> </table>
      0
      HSJOIN<
      ( 2)
      0.0299059
      0
      /----------+-----------\
      0 0
      IXSCAN FETCH
      ( 3) ( 4)
      0.0147846 0.0151213
      0 0
      | /------+------\
      0 0 0
      INDEX: ADMINISTRATOR IXSCAN TABLE: ADMINISTRATOR
      XBUP ( 5) A
      Q5 0.0147846 Q3
      0
      |
      0
      INDEX: ADMINISTRATOR
      XA
      Q3
      • ParulJ
        ParulJ
        5 Posts
        ACCEPTED ANSWER

        Re: Generated columns in Table Joins : leading to inefficiency

        ‏2013-02-01T09:28:09Z  in response to SystemAdmin
        Serge, I am rechecking this. Will get back to you in couple days.