Re: SQL CTE vs NTE (with LATERAL?) -- MIDRANGE-L

Some additional info...

I ran a pared down test of the CTE and NTE forms....as expected, both have
the same VE graph and the index over the detail is used.

I also tried adding LATERAL with no apparent change to the VE graph...

Before get to deep into building a full NTE version...I'd appreciate any
guidance on rather or not it will help. :)

Thanks!
Charles

On Tue, Apr 18, 2017 at 10:22 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

I'm working on creating a view to simplify access to some data for our BI
solution...

I've currently got it working with CTEs. Takes about a minute to
run...which isn't bad since the RPG report that provides the same data
takes 4 minutes.

But I think I can do better. A simplified example of the CTE form is like
so

with
hdr as (
select inv#, tran_date
from header
), dtl1 as (
select inv#, linetype
, sum(colA) as col1A_tot
, sum(colB) as col1B_tot
from detail1
group by inv#, linetype
), dtl2 as (
select inv#, linetype
, sum(colA) as col2A_tot
, sum(colB) as col2B_tot
from detail2
group by inv#, linetype
), sim_view as (
select inv#, tran_date, linetype
, col1A_tot, col2A_tot, col2B_tot
from hdr
left outer join dtl1
on hdr.inv# = dtl1.inv#
left outer join dtl2
on hdr.inv# = dtl2.inv#
)
select *
from sim_view
where tran_date between '2016-11-01' and '2016-11-30'

Note: the last CTE and the select are for testing purposes...as data will
most likely be pulled from the actual view a month at a time.

This example is very basic, the actual SQL has 11 CTEs (not counting
sim_view) and is 332 lines. :/

I've always preferred CTE to NTE as I think the CTE's are easier to
follow. Additionally, I've always felt there was little difference in
performance between CTE and NTE. As I understand it the optimizer uses the
same plan regardless. I suspect that while that may be true for relatively
simple queries, it may not be true for this one.

Looking at VE, I see that a full table scan is being done on detail1 and
detail2. I'm not accessing 30% or more of the records, so that's not the
reason for the table scan. I was hoping the optimizer would build a list
of inv# from header and use the indexes over inv# on detail1 and detail2;
basically pushing the WHERE down into the detail. I've seen the optimizer
do similar things with simpler queries.

I'm wondering if a NTE form would prove more optimize-able...allowing for
index usage on the various detail tables.

Also trying to understand if I need (or want) to use LATERAL on the NTE
form. I've been looking back at articles and archived posts, but I've not
been able to wrap my head around what benefits LATERAL provides.

I did run across a post about Oracle that mentions it will optimize a LEFT
OUTER JOIN of an NTE into a LATERAL; perhaps DB2 does the same and that
explains why I don't see any benefits.

I'd be nice if I could do this as a UTDF, being able to pass the dates in
as variables would make it easy to push down the filtering to the lower
levels. Unfortunately that's not an option.

Thoughts?

Charles