Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Marcio.

Asked: August 02, 2004 - 8:37 am UTC

Last updated: August 08, 2022 - 4:47 pm UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Tom, sorry about the silly question, but have been trying to do something with SQL Model without any success. I cannot understand how it works, could you do some simple sample? -- may be already exists.

Thanks,

and Tom said...

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Upsert on multiple column dimensions

A reader, November 12, 2004 - 6:30 pm UTC

Hi Tom,

Could you explain the following:

I get two rows with this:

SELECT X1, X2, Y, Z FROM DUAL
MODEL
DIMENSION BY (1 X1, 1 X2)
MEASURES (1 Y, 1 Z)
RULES UPSERT
(
Y[2, 2] = Y[1, 1]
)

However only one row for this (I expected two):

SELECT X1, X2, Y, Z FROM DUAL
MODEL
DIMENSION BY (1 X1, 1 X2)
MEASURES (1 Y, 1 Z)
RULES UPSERT
(
Y[2, any] = Y[1, 1]
)


Thanks a lot.

Tom Kyte
November 13, 2004 - 9:52 am UTC

well, the first one said "for [x1=2,x2=2], y will be assigned the value that y[x1=1,x2=1] had"

the second says "for [x1=2, any value for x2], y will be ....."

but in the second case, there weren't any "any's" to be found -- none are made "up" for it (the only possible answers in my opinion would be either the empty set as it did OR an *infinite* set as there are an infinite set of X2 values possible -- just none actually present)

ORA-33262: Analytic workspace EXPRESS does not exist

gia, January 12, 2006 - 7:34 pm UTC

Hello Tom,

We have error ORA-33262 when using SQL_MODEL. Do we need to create AW EXPRESS?

Thanks

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jan 13 10:37:06 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> aw create awwork
SP2-0734: unknown command beginning "aw create ..." - rest of line ignored.
SQL> aw list
SP2-0042: unknown command "aw list" - rest of line ignored.
SQL>  SELECT  STATE, CCY, EXPECTED_DT, REPAY, INT_RATE,
  2    COF_RATE
  3   FROM LDV_CONTRACT_WGT_RATE
  4   MODEL RETURN UPDATED ROWS
  5   PARTITION BY (state,ccy)
  6   DIMENSION BY (expected_dt)
  7   MEASURES (repay,int_rate,cof_rate)
  8   RULES
  9        ( repay [to_date('31/12/9999','dd/mm/yyyy')] = SUM(repay) [CV(expected_dt)]
 10        )
 11    order by state,ccy,expected_dt
 12  /
 FROM LDV_CONTRACT_WGT_RATE
      *
ERROR at line 3:
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP
technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.

 

Tom Kyte
January 13, 2006 - 11:03 am UTC

I'd see the error message and do what it says :)

Script ran,

gia, February 14, 2006 - 1:48 am UTC

Hi Tom,

DBA ran script (he does NOT know aboput this script ??) to activate analytical option in 10g database. Problem resolved.

Thank you for your help.



Bryan, July 14, 2006 - 9:03 am UTC

Can you tell me what script your DBA ran to fix this issue( Create a workspace??)??..I am getting the same error??..

Model clause links

Soumadip, February 27, 2012 - 10:33 am UTC

Hi Tom,

None of the below links seems to be working now. Do you have the latest links?

Oracle Magazine Article by Jonathan Gennick:
https://asktom.oracle.com/Misc/oramag/on-uniqueness-space-and-numbers.html

Paper 40166 at:
http://www.oracle.com/openworld/archive/sf2003/index.html?solutions_bi.html

Oracle By Example (OBE):
http://otn.oracle.com/obe/obe10gdb/bidw/
Tom Kyte
February 28, 2012 - 7:17 am UTC

they only have online archives for five years of Oracle Magazine online - so the jan 2005 article isn't around anymore.


OBE:

http://apex.oracle.com/pls/apex/f?p=44785:29:0::NO:RP::



the 2003 paper doesn't seem to be around anymore either.

MODEL articles

Duke Ganote, February 28, 2012 - 8:18 am UTC

I like the tutorials by Rob van Wijk (here's part one):
http://rwijk.blogspot.com/2007/10/sql-model-clause-tutorial-part-one.html

and by Joe Fuda starting here:
http://www.sqlsnippets.com/en/topic-11663.html

and the MODEL appendix in Anthony Molinaro's "SQL Cookbook"
http://www.oreillynet.com/pub/a/network/2004/08/10/MODELclause.html

The 2003 Oracle White Paper on the MODEL clause is (currently) here:
http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

------------------------------------------------
I've been trying to collect the IMO "best" articles on MODEL, which I've got here:
http://it.toolbox.com/blogs/data-ruminations/sql-model-clause-references-and-recommendations-50461
which I plan on keeping updated.
Tom Kyte
February 28, 2012 - 9:53 am UTC

thanks! appreciate that.

2003 Oracle OpenWorld presentation?

Duke Ganote, March 26, 2012 - 9:30 am UTC

I think found the 2003 OpenWorld presentation on the MODEL clause by Andy Witkowski and some other guy.
download.oracle.com/owsf_2003/40166.ppt
Tom Kyte
March 26, 2012 - 11:21 am UTC

I remember that ;)

Model clause comparison with others

Asim, August 04, 2022 - 11:39 am UTC


I am trying to understand Model clause. I have following questions.

1.

Is it true that if you use MODEL clause you cannot use analytic functions in SELECT list?

2.

If the above is true, can we say that every query with analytical functions in SELECT list can be transformed into equivalent query with MODEL clause and without analytic functions in SELECT list?

3.

Also is the vice versa also true? ie every query with MODEL Clause can be transformed into equivalent query without MODEL clause and with analytical functions in SELECT list?

4.

Is the oracle's OLAP DML(or should we say MOLAP DML), is oracle's thing equivalent/compareable to the MDX (multidimentional expressions/queries) of MS SQL Server analysis services (OLAP/MOLAP).

5.

Is the MODEL clause, is Oracle's relational (ROLAP) thing equivalent/comparable to the MDX (multidimentional expressions/queries) of MS SQL Server analysis services (OLAP/MOLAP), that query MS SSAS's MOLAP cubes.?

6.

Is the MODEL clause, is Oracle's relational (ROLAP) thing equivalent/comparable to the oracle's OLAP DML that query oracle's OLAP/MOLAP cubes.

Thanks



Chris Saxon
August 04, 2022 - 1:52 pm UTC

1. Correct

If your query has a MODEL clause, then the query's SELECT and ORDER BY lists cannot contain aggregates or analytic functions

https://docs.oracle.com/en/database/oracle/oracle-database/21/dwhsg/sql-modeling-data-warehouses.html#GUID-AD6A9B81-492F-4724-9517-7758C4A98365

2. You can use analytic functions within MODEL itself, so a strict interpretation of your question would say this is true.

If you're asking if you can convert every analytic function to equivalent rules in MODEL, then you probably can; I'm not 100% certain.

You can always use MODEL in a subquery and an analytic on its results in an outer query.

3. No. The MODEL clause can generate new rows and change the values of the measures. Analytic functions can't do this.

4-6 - I don't know know about OLAP processing in either Oracle Database or SQL Server to answer this

Addendum to above

Asim, August 04, 2022 - 11:59 am UTC


7.
What is the order of processing of MODEL clause, I think, its like FROM JOIN, WHERE, GROUP BY, HAVING, WINDOW, MODEL, ORDER BY, FETCH? am i right? May be the WINDOW clause is not allowed with MODEL clause

8.
I see that MODEL clause can add rows to the query result set, it can also update the values of query result set. Can MODEL clause add columns to the query result set?




8.

Chris Saxon
August 04, 2022 - 1:55 pm UTC

7. You can't combine GROUP BY, HAVING or WINDOW with MODEL in the same subquery. Otherwise, this is correct.

8. Yes, you can define new columns in the DIMENSION/MEASURES clauses.

More

Asim, August 04, 2022 - 8:19 pm UTC


9.
I can see MODEL can reduce the no of rows of the result set ie RETURN UPDATED ROWS ONLY, can it also reduce the no of columns of the result set?

10.
What is the processing order of PIVOT clause

11.
If MODEL clause is used, can PIVOT clause be used in the same query(or to be precise in the same SELECT).

12.
Can I say that PIVOT and MODEL are the only clauses that can add columns to a query result set in addition to SELECT list.


Chris Saxon
August 05, 2022 - 12:56 pm UTC

9 MODEL returns the columns listed in the PARTITION, DIMENSION, and MEASURES clauses. If you don't list a table's columns in one of these then it's not in the results. So yes.

10 If you have questions about PIVOT, please submit a new question about this.

11 You can PIVOT before MODEL

12 No - MATCH_RECOGNIZE can define new columns

7

Asim, August 07, 2022 - 11:06 am UTC


7. But as we know PIVOT needs aggregate function and MODEL doesnt allow that. Then how is it possible.
Can you please gie an example of running query with PIVOT and MODEL togather in one SELECT (NOT in one query with outer and inner select).

Connor McDonald
August 08, 2022 - 1:20 am UTC

What is the issue with "NOT in one query with outer and inner select"

That's like saying "I want to count the records in a table but without using COUNT function".

If the facilities are available, then why would you not use them? That always befuddles me.

In fact, most often my first recommendation for developers building complicated SQL statements is to break it up using WITH because you *want* to have it compartmentalized to assist the next person coming along who will be maintaining it.

Sorry its 11 not 7

Asim, August 07, 2022 - 11:08 am UTC

Sorry it 11 not 7 in above post

Just to know

Asim, August 08, 2022 - 4:36 pm UTC

Because I just wanted make my concepts clear, and therefore wanted to know that PIVOT and MODEL can exist in the same SELECT or not.

Also I want to know if the following can exist in the same SELECT or not
a) MODEL and MATCH_RECOGNIZE
b) PIVOT and MATCH_RECOGNIZE

Chris Saxon
August 08, 2022 - 4:47 pm UTC

Rather than keep asking us what's possible - what have you tried?