Re: [SQL] Calculation dependencies in views

Rick Delaney wrote:

> I have a table with, say, a dozen fields and I want to end up with a view with
> around 50 fields, calculated from the original dozen.
>
> So it would be something like this:
>
> CREATE VIEW final AS
>     SELECT
>         x, y, z,
>         (x + y)         as a,
>         (y + z)         as b,
>         (x + y) * z     as c,
>         (y + z) * x     as d,
>         (x + y) * z + x as e,
>         (y + z) * x + x as f
>    FROM my_table;
>
> except my expressions are longer and more complicated.  However, my expressions
> do have similar dependencies and redundancies.
    Be aware that 6.5.* might be unable to deal with the resulting size of    the rewrite rule. And I'm not sure that
I'llget TOAST ready for 7.0 to    handle it.
 

> My question is what is a good way of dealing with this?  I was going to do
> something like
>
> CREATE VIEW one AS
>     SELECT
>         id,
>         (x + y)         as a,
>         (y + z)         as b,
>    FROM my_table;
>
> CREATE VIEW two AS
>     SELECT
>         m.id,
>         o.a * m.z     as c,
>         o.b * m.x     as d,
>    FROM my_table m, one o
>    WHERE m.id = o.id;
>
> etc.  but I'll end up with a lot of levels and joins going this route which I
> expect will be pretty slow.
    In the above sample, it will end up in a join, because even after    rewriting there will be more than one used
rangetableentries in the    query. But if you're able to do the nesting in a manner like this:
 

         CREATE VIEW one AS           SELECT             x, y, z,             x + y AS sum_xy,             x + z AS
sum_xz,            y + z AS sum_yz           FROM my_table;
 
         CREATE VIEW final AS           SELECT             x, y, z,             sum_xy AS a,             sum_yz AS b,
         sum_xy * z AS c,             sum_yz * x AS d,             sum_xy * z + x AS e,             sum_yz * x + x AS f
         FROM one;
 
    and keep all views follow strictly one path
    final -> n-1 -> n-2 -> ... -> two -> one
    you'll end up the a construct that requires nested levels of rewriting.    But the resulting query will definitely
bea single table scan having    all your complicated expressions attached.
 
    BTW: if you nest many views, you'll probably get an ERROR about    "infinite recursion" from the rewriter. In that
case,you must raise the    value of the
 
    #define REWRITE_INVOKE_MAX 10
    at line 1466 in src/backend/rewrite/rewriteHander.c to some value fairly    above your nesting level.

> Can someone recommend anything to me?  Should I be approaching this from a
> completely different angle i.e other than views?
    Well, I'm a rewriting- and trigger-man. It really depends on the usual    behaviour of your data and application.
    Keep in mind, that the above view technique requires each value to be    computed for each single view attribute
foreach single row at each    SELECT. So if you usually SELECT relatively small amounts of your data,    or update big
amountsof it between the SELECT's, it wouldn't hurt.
 
    But if you're dealing with huge set's of row's at SELECT time and rarely    changes, you might be better off with
settingup a table having all the    fields in place, and let a trigger put all the calculated values in    place.
 


> I'm pretty new at this so any pointers will be appreciated.
    You're welcome.



Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #