× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



View materialization is when query optimizer has to 'materialize' rows for
the view (i.e. create an intermediate temporary result table) in order to
satisfy query request.  
View composition is when query optimizer takes your top select statement and
underlying view's select statement and merges them into a single statement
internally, not necessarily requiring intermediate temporary result tables
to be materialized (likely to perform better).

"WITH" syntax is known as Common Table Expressions (CTE).  CTEs perform well
on System i and sort of modularize complex SQL queries, so I like to use
them when I can.  I don't know if it'll lead to view composition or not,
that wasn't why I used it.  I was trying to use the CTE to give a hint to
query optimizer to perform the selection on the large table prior to
performing the join with the small table.  I don't know if query optimizer
would take my hint, but all information you can give to it is helpful.

Oh yeah, that reminds me, if you want to bias it toward using an index, add
an OPTIMIZE FOR 4665 ROWS clause, probably best inside the CTE.  This gives
another hint to the query optimizer, telling it that your query should be
highly selective, hence it should bias it toward using an index.

Elvis

-----Original Message-----
 Subject: Re: Fun with DB2/400 Optimizer...well, not quite.

Elvis, thanks for the links.  I will check them out.  I don't recognize this
differentiation between view composition and view materialization - can you
provide a quick explanation or some additional links.

Also, what is this "WITH..." syntax - does this lead to "composition"?




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.