Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2015
    Posts
    3

    Unanswered: Host variable and literal comparison

    According to Bonnie Baker, a host variable and literal comparison is a stage 2 predicate. Does anyone know if this is still the case in the latest versions? The comparison in question is something of the form

    WHERE :host-var is null or ColA = :host-var

    Seeing as there are no column references on the left, it seems a little strange that DB2 might try and evaluate this at stage 2 for every row. If I analyze the same in Postgres, it does what it calls a One-Time Access of the left side, and then either table scans if it evaluates to true or index scans ColA if it is false. I appreciate that the host-var can always be checked in application code, but if it were to be ran in SQL could DB2 use the ColA index? The documentation suggests that COALESCE is a stage 1 predicate so would COALESCE(:host-var, ColA) = ColA be a better approach or does the column need to be the thing being coalesced

    Thanks

  2. #2
    Join Date
    Apr 2015
    Posts
    3

    Host variable and literal comparison

    Please delete this reply
    Last edited by xx396; 04-02-15 at 07:39. Reason: duplicate original post

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    it is a stage 2 predicate. If its just one or two, sometimes its best to have two versions of the query and to perform that check prior to executing the correct query. If as in most cases, this is one of those user search sessions where they could enter one or many options on a screen for filtering the data they want to see, then you have a few options. You can write the query with every possible AND/OR combination and live with it. You can have the process build the query dynamically based on the user's inputs, I would look at option 1 there and bind with REOPT always.
    Dave

  4. #4
    Join Date
    Apr 2015
    Posts
    3
    Quote Originally Posted by dav1mo View Post
    it is a stage 2 predicate. If its just one or two, sometimes its best to have two versions of the query and to perform that check prior to executing the correct query. If as in most cases, this is one of those user search sessions where they could enter one or many options on a screen for filtering the data they want to see, then you have a few options. You can write the query with every possible AND/OR combination and live with it. You can have the process build the query dynamically based on the user's inputs, I would look at option 1 there and bind with REOPT always.
    Thanks for that. Yes it is the typical user search session that you describe.

    Doesn't option 1 mean that an index can never be used though as each filter snippet will be marked as stage 2? I'm more leaning towards option 2 myself if the optimizer isn't able to sort it out in the way that Postgres appears to be doing

Posting Permissions

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