RE: Fun with DB2/400 Optimizer...well, not quite.

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"?