Professional Web Applications Themes

No predicate push-down into View - why? - IBM DB2

Hi, Following up on this previous post I have a concrete example of where the lack of a predicate push-down is significant as it fails to resrict a cartesian join. I'll try to give an excerpt first in case this yields something obvious end-user Query = "select * from VIEW.MY_TASKS where user_oid = 1" where MY_TASKS View is structured like WITH cartesian_join(user_oid,........... as (SELECT a.OID,........ FROM table1 a, table2 b) SELECT a.user_oid,......... FROM cartesian_join a left outer join table2 ON ......... If I hard code a predicate of user_oid = 1 into the cartesian_join CTE it optimises as I would ...

  1. #1

    Default Re: No predicate push-down into View - why?

    Hi,

    Following up on this previous post I have a concrete example of
    where the lack of a predicate push-down is significant as it fails to resrict
    a cartesian join.

    I'll try to give an excerpt first in case this yields something
    obvious

    end-user Query = "select * from VIEW.MY_TASKS where user_oid = 1"

    where MY_TASKS View is structured like

    WITH
    cartesian_join(user_oid,...........
    as (SELECT a.OID,........
    FROM table1 a, table2 b)
    SELECT
    a.user_oid,.........
    FROM
    cartesian_join a left outer join table2
    ON .........

    If I hard code a predicate of user_oid = 1 into the cartesian_join CTE
    it optimises as I would expect, and runs in significantly less time
    consistent with the cardinality of the number of user_oids in table1.

    However, the end-user query predicate is not pushed into the view ?

    Any ideas, is this clear what I am getting at here ?

    Any help appreciated, as it is fundamental to our use of views that
    such predicates are pushed_down.

    Thanks.

    Paul.
    Paul Reddin Guest

  2. #2

    Default Re: No predicate push-down into View - why?

    [email]paulabacus.co.uk[/email] (Paul Reddin) wrote in message news:<1fd2a603.0307040747.3a8e2d45posting.google. com>...
    > Any help appreciated, as it is fundamental to our use of views [common table
    > expressions] that such [end user] predicates are pushed_down.
    Paul,

    According to Don Chamberlin's Complete Guide to DB2 UDB, one of the
    properties of common table expressions is that they remove the
    possibility of potential inconsistencies that might arise if you
    instead used identical table expressions at multiple points in a query
    (without repeatable read). This is a useful property, although I
    can't find reference to it in the manuals incidentally.

    From this I have always understood that commmon table expressions are
    evaluated once, which would tend to mitigate against pushing down
    predicates into them - the optimizer could only push down a predicate
    if that push-down was clearly appropriate in all cases where the CTE
    was referenced.

    In your case, does the CTE get referenced more than once in the query?
    If so, I think that's all the answer you need. If not, then clearly
    the optimizer could treat the CTE like a regular table expression, but
    maybe the IBM bods have missed that potential tweak - after all, under
    such cirstances you yourself could just rewrite the CTE to a
    regular table expression.

    Hope this helps.


    Jeremy Rickard
    Jeremy Rickard Guest

  3. #3

    Default Re: No predicate push-down into View - why?

    In DB2 can you use a parameter in a view? Would you be able to make a view
    like...


    "Paul Reddin" <paulabacus.co.uk> wrote in message
    news:1fd2a603.0307040747.3a8e2d45posting.google.c om...
    > Hi,
    >
    > Following up on this previous post I have a concrete example of
    > where the lack of a predicate push-down is significant as it fails to
    resrict
    > a cartesian join.
    >
    > I'll try to give an excerpt first in case this yields something
    > obvious
    >
    > end-user Query = "select * from VIEW.MY_TASKS where user_oid = 1"
    >
    > where MY_TASKS View is structured like
    >
    > WITH
    > cartesian_join(user_oid,...........
    > as (SELECT a.OID,........
    > FROM table1 a, table2 b)
    > SELECT
    > a.user_oid,.........
    > FROM
    > cartesian_join a left outer join table2
    > ON .........
    >
    > If I hard code a predicate of user_oid = 1 into the cartesian_join CTE
    > it optimises as I would expect, and runs in significantly less time
    > consistent with the cardinality of the number of user_oids in table1.
    >
    > However, the end-user query predicate is not pushed into the view ?
    >
    > Any ideas, is this clear what I am getting at here ?
    >
    > Any help appreciated, as it is fundamental to our use of views that
    > such predicates are pushed_down.
    >
    > Thanks.
    >
    > Paul.

    Brien Schultz Guest

  4. #4

    Default Re: No predicate push-down into View - why?

    In DB2 can you use a parameter in a view? Would you be able to make a view
    like...

    Create view X as Select * from VIEW.MY_TASKS where user_oid = ?

    Then select from it like...

    Select * From X Where ? = 1231

    I mention this because it may help the database understand what you're
    trying to do, allowing it to optimize your command. ...then again, I really
    don't understand your question, or DB2 for that matter, so maybe this has
    nothing to do with what you're asking. I just know that when I write my GUI
    code using parameters really speeds up my performance.

    "Paul Reddin" <paulabacus.co.uk> wrote in message
    news:1fd2a603.0307040747.3a8e2d45posting.google.c om...
    > Hi,
    >
    > Following up on this previous post I have a concrete example of
    > where the lack of a predicate push-down is significant as it fails to
    resrict
    > a cartesian join.
    >
    > I'll try to give an excerpt first in case this yields something
    > obvious
    >
    > end-user Query = "select * from VIEW.MY_TASKS where user_oid = 1"
    >
    > where MY_TASKS View is structured like
    >
    > WITH
    > cartesian_join(user_oid,...........
    > as (SELECT a.OID,........
    > FROM table1 a, table2 b)
    > SELECT
    > a.user_oid,.........
    > FROM
    > cartesian_join a left outer join table2
    > ON .........
    >
    > If I hard code a predicate of user_oid = 1 into the cartesian_join CTE
    > it optimises as I would expect, and runs in significantly less time
    > consistent with the cardinality of the number of user_oids in table1.
    >
    > However, the end-user query predicate is not pushed into the view ?
    >
    > Any ideas, is this clear what I am getting at here ?
    >
    > Any help appreciated, as it is fundamental to our use of views that
    > such predicates are pushed_down.
    >
    > Thanks.
    >
    > Paul.


    Brien Schultz Guest

  5. #5

    Default Re: No predicate push-down into View - why?

    Parameters in views are not supported in DB2.

    Brien Schultz wrote:
    > In DB2 can you use a parameter in a view? Would you be able to make a view
    > like...
    >
    > Create view X as Select * from VIEW.MY_TASKS where user_oid = ?
    >
    > Then select from it like...
    >
    > Select * From X Where ? = 1231
    >
    > I mention this because it may help the database understand what you're
    > trying to do, allowing it to optimize your command. ...then again, I really
    > don't understand your question, or DB2 for that matter, so maybe this has
    > nothing to do with what you're asking. I just know that when I write my GUI
    > code using parameters really speeds up my performance.
    >
    > "Paul Reddin" <paulabacus.co.uk> wrote in message
    > news:1fd2a603.0307040747.3a8e2d45posting.google.c om...
    >
    >>Hi,
    >>
    >>Following up on this previous post I have a concrete example of
    >>where the lack of a predicate push-down is significant as it fails to
    >
    > resrict
    >
    >>a cartesian join.
    >>
    >>I'll try to give an excerpt first in case this yields something
    >>obvious
    >>
    >>end-user Query = "select * from VIEW.MY_TASKS where user_oid = 1"
    >>
    >>where MY_TASKS View is structured like
    >>
    >>WITH
    >> cartesian_join(user_oid,...........
    >> as (SELECT a.OID,........
    >> FROM table1 a, table2 b)
    >>SELECT
    >> a.user_oid,.........
    >>FROM
    >> cartesian_join a left outer join table2
    >> ON .........
    >>
    >>If I hard code a predicate of user_oid = 1 into the cartesian_join CTE
    >>it optimises as I would expect, and runs in significantly less time
    >>consistent with the cardinality of the number of user_oids in table1.
    >>
    >>However, the end-user query predicate is not pushed into the view ?
    >>
    >>Any ideas, is this clear what I am getting at here ?
    >>
    >>Any help appreciated, as it is fundamental to our use of views that
    >>such predicates are pushed_down.
    >>
    >>Thanks.
    >>
    >>Paul.
    >
    >
    >
    >
    Blair Adamache Guest

  6. #6

    Default Re: No predicate push-down into View - why?

    Now now. This isn't FAKING. This is just a different word for the same
    thing:
    CREATE FUNCTION v(a INT) RETURNS TABLE (c1 INT):
    RETURN SELECT c1 FROM T WHERE pk = a;

    This IS a federated view. It has the same internal workings (expanded
    into the query) as a view.

    Cheers
    Serge

    --
    Serge Rielau
    DB2 UDB SQL Compiler Development
    IBM Software Lab, Toronto

    Visit DB2 Developer Domain at
    [url]http://www7b.software.ibm.com/dmdd/[/url]


    Serge Rielau Guest

Similar Threads

  1. URL Push
    By rwarend in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: November 16th, 01:20 AM
  2. Using variable in SQL IN predicate
    By robnollie in forum Coldfusion Database Access
    Replies: 3
    Last Post: February 16th, 06:01 PM
  3. push
    By Sh4n3 webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: January 13th, 07:17 AM
  4. Wierd error when going to Design View from HTML view
    By VB Programmer in forum ASP.NET General
    Replies: 1
    Last Post: July 10th, 03:20 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139