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

# Evalute Order Of Model Clause.

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.

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.

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.

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.

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.

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