Informix

  • 1.  VIEWs and WHERE statements

    Posted Thu September 23, 2021 09:51 AM
    Hi Group,

    how does Informix execute a WHERE statement in a query on a VIEW? Suppose we have this view (syntax might be not 100% accurate)

    CREATE VIEW myview AS
    SELECT st.blah AS st_blah,
      st.whatever AS st_whatever,
      st.anotherthing AS st_anotherthing,
      at.thingy AS at_thingy,
      at.growl AS at_growl
    FROM some_table st
    LEFT JOIN another_table at ON st.blah = at.thingy
    ORDER BY st.blah;​

    which is cheaper: add the WHERE in the VIEW, or in the query on the VIEW? So, which one is cheaper:

    CREATE VIEW myview AS
    SELECT st.blah AS st_blah,
      st.whatever AS st_whatever,
      st.anotherthing AS st_anotherthing,
      at.thingy AS at_thingy,
      at.growl AS at_growl
    FROM some_table st
    LEFT JOIN another_table at ON st.blah = at.thingy
    WHERE st.blah = 1
    ORDER BY st.blah;​

    or, a WHERE query on the first VIEW example:

    SELECT * FROM myview WHERE st_blah = 1;

    Or will the eventual query plan be the same and thus similar performance?

    Thanks in advance,
    Arjen.



    ------------------------------
    Arjen Van Drie
    ------------------------------


  • 2.  RE: VIEWs and WHERE statements

    Posted Thu September 23, 2021 10:03 AM
    Arjen:

    It's going to depend on the complexity of the VIEW in a general sense. The optimizer will try to modify the query from the VIEW to include your filters or additional joins when you query the view. Sometimes it can do that, in other situations it cannot. If it cannot modify the query (ie fold the VIEW's query into the outer query) then it will execute the VIEW into a temp table and join or filter the temp table to satisfy the outer query. Run your query under SET EXPLAIN to see what the optimizer decides to do.

    That said, if the ONLY filter value you will every apply to the st_blah view field is "st_blah = 1" then by all means include that in the VIEW itself! Just know that if do add the filter to the VIEW and you ever need to query where st_blah equals some other value than '1' you will need a new view or the raw underlying query to do so.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: VIEWs and WHERE statements

    Posted Thu September 23, 2021 10:50 AM
    Hello Art,

    thanks for your reply. The "st_blah = 1" was just an example: it will change and there will probably be more than just one condition in the WHERE statement.

    We will test and see what gives.

    Thanks again!
    Arjen.

    ------------------------------
    Arjen Van Drie
    ------------------------------