6 Replies Latest reply on Jun 28, 2019 1:22 PM by study_anymore

    Evalute Order Of Model Clause.

    study_anymore

      I execute Below SQL.

      I think Val should be 101.

      But Val is 105.

      Why?

       

      select *

        from (select 1 as Ind,1 as Val from dual)

      model

      dimension by(Ind)

      measures(Val)

      rules iterate(5)(

      Val[any] = 100,

      Val[any] = Val[cv()]+1);

       

      Ind  Val

      ---  ---

        1  105

        • 1. Re: Evalute Order Of Model Clause.
          Stew Ashton

          I suspect that is a bug. Maybe Oracle thinks it can optimize the work by assigning the constant once. The following statement works as you would expect:

           

          select *
          from (select 0 as Ind,1 as Val from dual)
          model
            dimension by(Ind)
            measures(Val)
            rules iterate(5)(
            Val[any] = 100+iteration_number-iteration_number,
            Val[any] = Val[cv()]+1
          );
          
                 IND        VAL
          ---------- ----------
                   0        101
          

           

          Best regards,

          Stew

          • 2. Re: Evalute Order Of Model Clause.
            Solomon Yakobson

            Iterate 5 means rule section is applied 5 times. Now it all depends in what order rules are applied. Default is sequential. Rules are independent of each other so rule 1 is applied 5 times and then rule 2 is applied 5 times. If you make rule dependent then both rule 1 and rule 2 are applied and  only then we iterate:

             

            select *

              from (select 1 as Ind,1 as Val from dual)

            model

            dimension by(Ind)

            measures(Val)

            rules iterate(5)(

            Val[any] = Val[cv()] - Val[cv()] + 100,

            Val[any] = Val[cv()]+1)

            /

             

                   IND        VAL

            ---------- ----------

                     1        101

             

            SQL>

             

            SY.

            • 3. Re: Evalute Order Of Model Clause.
              Stew Ashton

              Solomon Yakobson wrote:

              ...

              Rules are independent of each other so rule 1 is applied 5 times and then rule 2 is applied 5 times.

              Where is that documented, and how do you explain the difference between OPs example and mine?

              Solomon Yakobson wrote:

              ...

              If you make rule dependent then both rule 1 and rule 2 are applied and only then we iterate:

              Are you still talking about sequential ordering, or are you saying that Oracle switches to automatic ordering? With automatic ordering, iteration is not an option.

               

              Here is another example:

               

              select *
                from (select 1 as Ind,1 as Val from dual)
              model
              dimension by(Ind)
              measures(Val)
              rules upsert all iterate(5)(
              Val[1+iteration_number-iteration_number] = 100,
              Val[1] = Val[cv()]+1);
              
                     IND        VAL
              ---------- ----------
                       1        101
              

               

              It seems to me that the rules are interdependent in every case, but in the OPs example the result is different.

              Regards,

              Stew

              • 4. Re: Evalute Order Of Model Clause.
                mathguy

                I don't think "default order is sequential" is correct for iterative models. The documentation says

                 

                ITERATE can be specified only for SEQUENTIAL ORDER models

                 

                "only" seems to rule out automatic order altogether, rather than just specify a default.

                 

                https://docs.oracle.com/cd/B19306_01/server.102/b14223/sqlmodel.htm#i1012496

                 

                 

                With that said, the documentation is not entirely clear on what "sequential order" means for an iterative model. Execute the first rule several times, then the second rule several times, then the third rule, etc.? Or execute all the rules once (IN SEQUENCE), then all the rules again (in sequence), etc.? Even though the documentation doesn't say one way or the other (unless I missed it), all the examples I have seen work according to the latter interpretation, not the former.

                 

                So, I agree with Stew - it's probably a bug.

                 

                Here is a brief example to show that even when there are inter-dependencies (where AUTOMATIC ORDER would lead to a different result), the iterative model uses SEQUENTIAL order:

                 

                select * from (select 1 as i from dual union all select 2 from dual)

                model

                  dimension by(i)

                  measures  (1 as x, 1 as y)

                  rules     iterate(1)(

                    x[any] = y[cv()-1],

                    y[any] = 3

                  )

                ;

                 

                         I          X          Y

                ---------- ---------- ----------

                         1                     3

                         2          1          3

                 

                Remove the ITERATE(1) directive and replace it with SEQUENTIAL ORDER, run the query and notice it produces the same result. Then run it again with AUTOMATIC ORDER and you will see the output is different.

                • 5. Re: Evalute Order Of Model Clause.
                  GregV

                  mathguy wrote:

                   

                   

                  With that said, the documentation is not entirely clear on what "sequential order" means for an iterative model. Execute the first rule several times, then the second rule several times, then the third rule, etc.? Or execute all the rules once (IN SEQUENCE), then all the rules again (in sequence), etc.? Even though the documentation doesn't say one way or the other (unless I missed it), all the examples I have seen work according to the latter interpretation, not the former.

                  My understanding is the same. Sequential order for an iterative model means the same as for a non-iterative model : "By default, rules are evaluated in the order they appear in the MODEL clause."

                   

                  https://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#DWHSG8779

                   

                  In the OP example it seems Oracle takes some liberty because of the ANY wildcard. If you replace the literal 100 with val[1], Oracle raises an ORA-32637 error:

                   

                  select *

                    from (select 1 as Ind,1 as Val from dual)

                  model

                  dimension by(Ind)

                  measures(Val)

                  rules iterate(5)(

                  Val[any] = val[1],

                  Val[any] = Val[cv()]+1)

                   

                  ORA-32637: Self cyclic rule in sequential order MODEL

                   

                  But that shouldn't be the case. val[1] is known and if the rules were evaluated in sequential order the values could be computed. If you replace ANY with 1, it works:

                   

                  select *

                    from (select 1 as Ind,1 as Val from dual)

                  model

                  dimension by(Ind)

                  measures(Val)

                  rules iterate(5)(

                  Val[1] = val[1],

                  Val[1] = Val[cv()]+1);

                   

                        IND        VAL

                  ---------- ----------

                          1          6

                  • 6. Re: Evalute Order Of Model Clause.
                    study_anymore

                    thanks for replys.

                     

                    I conclude this is a bug.

                     

                    Because document says below.

                    "ITERATE can be specified only for SEQUENTIAL ORDER models and such models are referred to as iterative models."