10.4 Performance and the MODEL Clause 313
Chapter 10
10.4.2 Understanding MODEL Clause Query Plans
An Oracle SQL MODEL clause query is accessed as ORDERED,
CYCLIC, or ACYCLIC. Any of the three aforementioned options can be
accessed as FAST. The different access methods are used by the optimizer in
generating a query plan, depending on the structure of the MODEL clause
RULES clause.
Figure 10.32 shows an example of an ORDERED FAST MODEL
clause query where expressions on both sides of the rules are all simple cell
expressions.
Figure 10.33 shows an example of an ORDERED MODEL clause
query where expressions in the rules have multiple cell references and not
single cell references, as in Figure 10.32.
Figure 10.34 shows an example of an ACYCLIC FAST MODEL clause
query where the RULES clause is changed to AUTOMATIC ORDER, as
opposed to the default SEQUENTIAL ORDER.
Figure 10.35 shows an example of an ACYCLIC MODEL clause query
without the FAST setting because of the presence of a complex analytical
function.
Figure 10.32
ORDERED FAST
MODEL clause
access.
314 10.4 Performance and the MODEL Clause
Figure 10.36 shows an example of a CYCLIC MODEL clause query,
because calculations with the rules depend on each other: x depends on y,
and y depends on x.
Figure 10.33
ORDERED
MODEL clause
access.
Figure 10.34
ACYCLIC FAST
MODEL clause
access.
10.4 Performance and the MODEL Clause 315
Chapter 10
Figure 10.35
ACYCLIC
MODEL clause
access.
Figure 10.36
CYCLIC MODEL
clause access.
316 10.4 Performance and the MODEL Clause
This chapter has covered many details of the Oracle SQL MODEL
clause for creation of spreadsheet-like multiple-dimensional array struc-
tured reports.
The next chapter will begin the third part of this book, examining
advanced and obscure topics in general—including advanced query rewrite,
advanced parallel processing for data warehouses, loading data into a data
warehouse, and, finally, general data warehouse architecture.

Get Oracle Data Warehouse Tuning for 10g now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.