8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1 (12.1)


LATERAL eval said...

Hi Tim, according to my experiments, LATERAL always ended up with NESTED LOOPS. So IMO LATERAL ensures the inline view is evaluated later as a lookup table, i.e. never becomes the driving one. This way, it could allow for the inline view to reference columns from outside, because the outer part would be already evaluated. Therefore I don't see LATERAL just as alternative syntax. Regards and thanks

Tim... said...

Hi.

I explicitly say, "In the case of these simple examples, this is just a basic query transformation, allowing us to use an alternate syntax. Of course, that's not to say there isn't something more interesting under the hood for other cases."

Then show a more complicated example that shows lateral is not always transformed. :)

Cheers

Tim...

LATERAL transform said...

Tim, my post was not meant in the way I don't agree with you. Right the opposite, I agree that LATERAL causes some transformation to kick in and just proposed my way of understanding it, what the transformation actually could do. Hope this clears up my previous post. Actually I love your site, I always learn something new to try or at least to think of. Cheers, Daniel

Tim... said...

Hi.

Don't worry. I didn't take offence. Just pointing out I had made the point. :)

Thanks for the support. Happy to receive feedback. Hope I didn't scare you off. :)

Cheers

Tim...

Bry said...

Hi tim,
Very clearly written but would be even better if you added more commentary-insights, like - What do the traces tell us? Why do you use the Outer Apply join instead of an Outer join? (it appears the answer is, you want the join from the outer to the inner query).

thanks

Vijay Jagdale said...

Hi Tim: I don't see the point of OUTER APPLY. The stated example rewritten with LEFT JOIN will get the exact same results and is somewhat more readable:
SELECT department_name, employee_id, employee_name
FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id AND salary >= 3000
ORDER BY 1, 2, 3;

Tim... said...

Hi.

Did you read the query transformation section? I explain that for simple actions the query transformation results in a regular join conditions, but there are cases where this is not the case. It's all about where the predicates are placed to optimize the query. There are cases where normal joins are transformed to this syntax for improved performance.

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.