Lateral thinking about pushing predicates
May 30, 2024 Leave a comment
Recently I’ve found myself working on a set of data which requires a fair amount of crunching and “de-duplication” of data sent from the system of record (SOR) to identify what was the last event sent for a particular set of keys that meets certain unindexed filter criteria.
Which means I’m wanting to write code which drives from a set of data, and then joins to an inline aggregation view for each row from the driving rowsource.
The aggregation dataset is too large and open-ended to aggregate independently and hash join to the driving data set.
So by instinct I find myself wanting this to operate with a nested loop from the driving rowsource and for each row to push the join predicates into aggregation view.
However, I have found that specifying the correct push predicate hint syntax is a bit awkward and I try to be mindful of those who inevitably will come to the code at a later date to make a change.
There’s a good chance they won’t have heard of predicate pushing unfortunately and I strongly dislike leaving behind a legacy of heavily hinted code, although I make some exceptions.
Having sought out a high level 2nd opinion on a comment on Jonathan Lewis’s blog, I am now having a tendency to write such code using a LATERAL join, or at least experiment with it as an alternative approach.
You might have come across the lateral join not least from when it first started appearing in internal transformations for some ANSI SQL. But here is some documentation on this as a fully fledged SQL feature from 12c onwards:
- https://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1
- https://dfitzjarrell.wordpress.com/2022/05/11/getting-lateral/
Now I’m aware of course that there will be other optimizer transformations which can eliminate the lateral view, not least decorrelation:
But it should never be unexpected that any new feature might experience some degree of bugs and/or wrong result issues.
However I have been encouraged by the fact that in my circumstances, under my conditions, the results are good and the query is executing how I imagined it to with no further direction from hints, etc
With respect to the join conditions, you can specify the lateral view syntax in a couple of ways – either in the standard ON () join conditions, or the slightly strange scope of referencing the outer join conditions within the view itself, example here of the latter.
Rather than giving an emp-dept example of the lateral view which you can find elsewhere, not least in the links already provided and elsewhere, I’m going to provide a cutdown, sanitised snippet of the code I’m using in the real world although I’m sure this doesn’t really help with knowing when you might use this feature because I can’t give you all the background, data, objects from the real world either.
The snippet is from a INSERT SELECT. I’ve cut a whole bunch of information from the plan not least so you don’t see the horrible numbers that the optimizer throws up in terms of estimated rows and cost! (Which might/should be a warning sign that you wish to dig deeper into)
Really I want to just leave an idea of the scenario where I’ve been considering and experimenting with lateral joins.
with subq_active_positions as
(select /*+ */
rrs.f_id
, rrs.f_source
, rrs.reg
, max(rrs.c_id) keep (dense_rank first order by rrs.eventtime desc) c_id
, max(rrs.c_source) keep (dense_rank first order by rrs.eventtime desc) c_source
from regreport_event_state rrs
where 1 = 1
and reg = p_regulation
--
... some additional date filters
--
group by
rrs.f_id
, rrs.f_source
, rrs.reg
having ... some early filtering...
)
, subq_ap_with_eligible_events as
(select /*+ */ p.*, aud.*
from subq_active_positions p
inner join lateral
(select *
from
(select /*+ qb_name(sq1) */
max(rra.rowid) keep (dense_rank first order by rra.eventtime desc) rd
, max(rra.eventtype) keep (dense_rank first order by rra.eventtime desc) last_qualifying_event
, rra.f_id
, rra.f_source
, rra.c_id
, rra.c_source
, rra.reg
from regreport_event_report rra
where 1 = 1
--
and rra.f_id = p.f_id
and rra.f_source = p.f_source
and rra.c_id = p.c_id
and rra.c_source = p.c_source
and rra.reg = p.reg
--
group by
rra.f_id
, rra.f_source
, rra.c_id
, rra.c_source
, rra.reg
) x
where last_qualifying_event = 'Something_or_other') aud
on 1 = 1)
select *
from subq_ap_with_eligible_events;
===========================================================================================================
| Id | Operation | Name |
===========================================================================================================
| 0 | INSERT STATEMENT | |
| 1 | TEMP TABLE TRANSFORMATION | |
| 2 | PX COORDINATOR | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 |
| 4 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9E0321_A450058D |
| 5 | FILTER | |
| 6 | SORT GROUP BY | |
| 7 | PX RECEIVE | |
| 8 | PX SEND HASH | :TQ10000 |
| 9 | SORT GROUP BY | |
| 10 | PX BLOCK ITERATOR | |
| 11 | TABLE ACCESS STORAGE FULL | REGREPORT_EVENT_STATE |
| 12 | PX COORDINATOR | |
| 13 | PX SEND QC (RANDOM) | :TQ40001 |
| 14 | LOAD AS SELECT (HIGH WATER MARK BROKERED) | CANNED_REPORT_DATA |
| 15 | SORT AGGREGATE | |
| 16 | PARTITION HASH SINGLE | |
| 17 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | REGREPORT_EVENT_REPORT |
| 18 | INDEX RANGE SCAN | REGREPORT_EVENT_REPORT_PK |
| 19 | OPTIMIZER STATISTICS GATHERING | |
| 20 | BUFFER SORT | |
| 21 | PX RECEIVE | |
| 22 | PX SEND ROUND-ROBIN | :TQ40000 |
| 23 | SORT ORDER BY | |
| 24 | NESTED LOOPS | |
| 25 | VIEW | |
| 26 | NESTED LOOPS | |
| 27 | PX COORDINATOR | |
| 28 | PX SEND QC (RANDOM) | :TQ30000 |
| 29 | VIEW | |
| 30 | PX BLOCK ITERATOR | |
| 31 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9E0321_A450058D |
| 32 | PX COORDINATOR | |
| 33 | PX SEND QC (RANDOM) | :TQ20001 |
| 34 | VIEW | VW_LAT_75FFB6E0 |
| 35 | FILTER | |
| 36 | SORT GROUP BY | |
| 37 | PX RECEIVE | |
| 38 | PX SEND HASH | :TQ20000 |
| 39 | SORT GROUP BY | |
| 40 | FILTER | |
| 41 | PX PARTITION HASH ITERATOR | |
| 42 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | REGREPORT_EVENT_REPORT |
| 43 | INDEX RANGE SCAN | REGREPORT_EVENT_REPORT_PK |
| 44 | TABLE ACCESS BY USER ROWID | REGREPORT_EVENT_REPORT |
===========================================================================================================
Possibly this sounds a bit esoteric at beast and highly situation specific and yet without a useful standalone data setup to adequately explain the whys and wherefores. It is what it is.
The summary is if you want to join to an inline view and you think you want to push predicates, maybe do some testing around a lateral join to that inline view.
OraStory 
Recent Comments