Search

Top 60 Oracle Blogs

Recent comments

LATERAL

Extending in-lists

A well known “limitation” of Oracle is that you can only have 1000 elements within an IN-LIST style expression in your SQL predicates. I use the quotes because I’ve always found that if you are heading into the territory where you need more than 1000 items in an IN-LIST, then it is often indicative of something else being wrong. For example, if the code is backing a user interface, then where in the design phase did someone not go “Whoa…we are going to have 1000 select-able elements on the screen?”

In any event, you can find many tricks and techniques out there on the intertubes about how workaround this issue, such as:

LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in 12c

love-sqlI 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…