advertisement

Listen Print Discuss

Using the New MODEL Clause in Oracle Database 10g
Pages: 1, 2, 3, 4

Investigating further on the potential benefits of using the MODEL clause, let's look at a snippet from a 10046 trace on the two examples above.

Pipelined table function



=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=57 oct=3 lid=57 tim=11151268307 
hv=4265205233 ad='183eee1c'
select * from table( get_emp_power_score() )
END OF STMT
PARSE #1:c=0,e=230,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=11151268295
BINDS #1:
EXEC #1:c=0,e=286,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=11151269167
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1111838976 p2=1 p3=0
=====================
PARSING IN CURSOR #2 len=78 dep=1 uid=57 oct=3 lid=57 tim=11151270047 
hv=3940482563 ad='1911392c'
SELECT EMP_SCORE_OBJ (EMPNO,SCORE,NULL) EMP_ROW FROM EMP_SCORE ORDER BY EMPNO 
END OF STMT
PARSE #2:c=0,e=160,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=11151270035
BINDS #2:
EXEC #2:c=0,e=261,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=11151271005
=====================
PARSING IN CURSOR #3 len=47 dep=2 uid=0 oct=3 lid=0 tim=11151271963 
hv=1023521005 ad='1a6876ec'
select metadata from kopm$  where name='DB_FDO'
END OF STMT
PARSE #3:c=0,e=191,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=11151271952
BINDS #3:
EXEC #3:c=0,e=199,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=11151272830
FETCH #3:c=0,e=69,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=11151273029
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=353 op='TABLE ACCESS BY INDEX ROWID KOPM$ 
(cr=2 pr=0 pw=0 time=75 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=354 op='INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 
pw=0 time=42 us)'
FETCH #2:c=0,e=3539,p=0,cr=9,cu=0,mis=0,r=28,dep=1,og=1,tim=11151274675
FETCH #1:c=0,e=5819,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=1,tim=11151275272
WAIT #1: nam='SQL*Net message from client' ela= 401 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=966,p=0,cr=0,cu=0,mis=0,r=13,dep=0,og=1,tim=11151277220
WAIT #1: nam='SQL*Net message from client' ela= 70159 p1=1111838976 p2=1 p3=0
=====================

MODEL clause


=====================
PARSING IN CURSOR #1 len=497 dep=0 uid=57 oct=3 lid=57 tim=61027987923 
hv=1265802836 ad='18e326a8'
select empno,
       s power_score,
       list
  from (
select score,
       empno,
       lag(score) over (partition by empno order by score) ls /* lag score */
  from emp_score
       )
 where ls is not null
 model
   dimension by (empno)
   measures (score s, ls, 0 tmp, cast(ls||','||score as varchar2(20)) list)
   rules iterate(3) (
      tmp[any] = s[cv()],
        s[any] = s[cv()] + ls[cv()],
       ls[any] = tmp[cv()],
     list[any] = list[cv()]||','||s[cv()]
   )
 order by 2 desc, 1
END OF STMT
PARSE #1:c=0,e=167,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=61027987912
BINDS #1:
EXEC #1:c=0,e=306,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=61027990149
WAIT #1: nam='SQL*Net message to client' ela= 8 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=2804,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=61027993234
WAIT #1: nam='SQL*Net message from client' ela= 407 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=251,p=0,cr=0,cu=0,mis=0,r=13,dep=0,og=1,tim=61027994500
WAIT #1: nam='SQL*Net message from client' ela= 123843 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=14 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=7 pr=0 pw=0 
time=2874 us)'
STAT #1 id=2 cnt=14 pid=1 pos=1 obj=0 op='SQL MODEL ORDERED (cr=7 pr=0 pw=0 
time=2760 us)'
STAT #1 id=3 cnt=14 pid=2 pos=1 obj=0 op='VIEW  (cr=7 pr=0 pw=0 time=572 us)'
STAT #1 id=4 cnt=28 pid=3 pos=1 obj=0 op='WINDOW SORT (cr=7 pr=0 pw=0 time=613 
us)'
STAT #1 id=5 cnt=28 pid=4 pos=1 obj=51474 op='TABLE ACCESS FULL EMP_SCORE (cr=7 
pr=0 pw=0 time=263 us)'
==========================================

Observe the extra work being done by the CBO to convert our PL/SQL into a valid table expression that can be used in SQL:


=====================
PARSING IN CURSOR #3 len=47 dep=2 uid=0 oct=3 lid=0 tim=11151271963 
hv=1023521005 ad='1a6876ec'
select metadata from kopm$  where name='DB_FDO'
END OF STMT
PARSE #3:c=0,e=191,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=11151271952
BINDS #3:
EXEC #3:c=0,e=199,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=11151272830
FETCH #3:c=0,e=69,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=11151273029
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=353 op='TABLE ACCESS BY INDEX ROWID KOPM$ 
(cr=2 pr=0 pw=0 time=75 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=354 op='INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 
pw=0 time=42 us)'
FETCH #2:c=0,e=3539,p=0,cr=9,cu=0,mis=0,r=28,dep=1,og=1,tim=11151274675
FETCH #1:c=0,e=5819,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=1,tim=11151275272
WAIT #1: nam='SQL*Net message from client' ela= 401 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=966,p=0,cr=0,cu=0,mis=0,r=13,dep=0,og=1,tim=11151277220
WAIT #1: nam='SQL*Net message from client' ela= 70159 p1=1111838976 p2=1 p3=0
=====================

kopm$ is the data structure being used to store and pipe our rows out. This is part of how the results of a PL/SQL function are transformed into a valid table expression. Although it may seem harmless, more work is involved when using object types and table functions in SQL, and this could come into play during peak load times or complex queries.

Conclusion

By using the MODEL clause, I was able to move the PL/SQL logic directly into SQL, thus avoiding the recursive calls and context switching that can result from calling PL/SQL in SQL. Ultimately this improves performance.

The MODEL clause is not a cure-all, but if you take the time to learn it and open yourself to new ideas, it can be a great new tool to have. In the right situation it could not only make the difference between poor and great performance, but also provide you an opportunity to do something exclusively in SQL that normally requires a procedural language.

To conclude, here are some final thoughts.

You'll love the MODEL clause because:

  • It's almost like a new language; you really begin to think about your data and result sets differently.
  • It gives you array access to your rows; procedural programmers may find the transition to set-based programming easier.
  • You'll be able to easily "make up" data and generate rows.
  • It brings recursive power to SQL.
  • The syntax quickly becomes very intuitive--PROLOG programmers will find this especially true.
  • It lets you perform complex inter-row calculations.
  • It combines procedural flexibility with set-based processing power.

You'll hate the MODEL clause because:

  • You can't pass a variable to ITERATE; it must be a constant.
  • You can expect some core dumps, especially when using for-loop iteration and reference models.
  • With the addition of analytic functions in 8.1.6, you may think you don't really need much else. (It's true, analytics are great--but there are still some things you can't do with analytics alone.)
  • Currently there is a 10,000-rule limit (though I've successfully reached 17,000).

You need to be aware of the following:

  • If your PGA is undersized, rules may be created as temporary lob segments.
  • You can't use correlated subqueries when doing for-loop iteration.
  • You'll read about reference models in the docs, which are very useful but are read-only.
  • You can't pass a bind variable to the ITERATE clause, but a simple workaround is to use the UNTIL clause: 
    rules iterate(10000) until (iteration_number >= :x )

    which lets you specify an exit condition using a bind variable or expression.

  • The MODEL clause may seem more like a niche addition to SQL rather than a long-awaited solution. Once this new feature has been accepted and used by a large number of developers, its usefulness will grow as developers will undoubtedly discover clever and unexpected uses for it. In other words, give it time, and it will grow on you.

Anthony Molinaro is a database developer at Wireless Generation.

Return to the O'Reilly Network