I was looking for something in the New Features Manual and I had a total WTF moment when I saw this stuff.
If you look at the final section of the article, you can see in some cases these just get transformed to regular joins and outer joins, but there is certainly something else under the hood, as shown by the pipelined table function example.
I think it’s going to take me a long time before I think of using these in my regular SQL…
Cheers
Tim…
Update: The optimizer has used LATERAL
inline views during some query transformations for some time, but they were not documented and therefore not supported for us to use directly until now. Thanks to Dominic Brooks and Sayan Malakshinov for the clarification.
LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in 12c : https://t.co/JSR7wDjUcz
LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in 12c @oraclebase http://t.co/6ItOoK1ILE #OrclDBA #odtug
RT @oraclebase: LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in 12c : https://t.co/JSR7wDjUcz
You’ve been able to see the same LATERAL internal translation in 10053 trace files for ANSI outer joins for some time.
http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/
https://jonathanlewis.wordpress.com/2011/01/31/ansi-outer/
https://jonathanlewis.wordpress.com/2012/07/16/ansi-outer-2/
RT @oraclebase: LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in 12c : https://t.co/JSR7wDjUcz
RT @oraclebase: LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in 12c : https://t.co/JSR7wDjUcz
Dom: Thanks for the links. I can’t remember seeing them. Useful!
Cheers
Tim…
LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in 12c http://t.co/3IvAsZtTN5 #oracle
LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in 12c | The ORACLE-BASE Blog http://t.co/a4O24J166g