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

Great.  I understand.  As an MS SQL Server DBA (well, primarily anway -
obviously I now work on the AS400 too), CTE's are something I am just now
encountering on SQL Server 2005.  I wish I had known they were on D2/400
before now!

Also, thanks for the info on materialization vs. composition.  Another thing
I haven't had to think much about in the MS world...

Ryan
"Elvis Budimlic"
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:001201c67842$42d2e550$6e01a8c0@xxxxxxxx
> 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"?
>
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>