Lateral Views in Oracle 12c.

#TGIF.

In Oracle 12c, we got introduce to LATERAL Views officially(Before same was getting use internally as part of Query Transformation) to join Inline views, Pre 12c we were not able to achieve same of joining inline views.

With Lateral View hint, we can have correlated inline views in FROM Clause itself.
It allows to refer Inline View to help achieve join in FROM Clause and help to restrict same.

Lets start with examples, Suppose we require data like below.

COL1     COL2
------- ----------
1       1
2       1
2       2
3       1
3       2
3       3
4       1
4       2
4       3
4       4

For a value in COL1, we need to display number from 1 till col1 values in COL2.

below is the sample SQL to achieve similar results,

SELECT L.COL1, M.COL2
FROM
(SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL <= 4 ) L ,
(SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1) M

Without LATERAL View hint, it won’t allow joining within Inline Views.
It is bound to give below Exception.

ORA-00904: "L"."COL1": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 4 Column: 53

Lets try using LATERAL Hint.

SELECT L.COL1, M.COL2
FROM
(SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL <= 4 ) L ,
LATERAL (SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1) M

COL1 COL2
---------- ----------
1       1
2       1
2       2
3       1
3       2
3       3
4       1
4       2
4       3
4       4

Lets have a look at underlying execution plan,

-------------------------------------------------------------------
| Id | Operation |                         Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT                              | | 1 |
| 1 | NESTED LOOPS                                   | | 1 |
| 2 | VIEW                                          | | 1 |
|* 3 | CONNECT BY WITHOUT FILTERING | | |
| 4 | FAST DUAL                                       | | 1 |
| 5 | VIEW                                 | VW_LAT_A18161FF | 1 |
|* 6 | CONNECT BY WITHOUT FILTERING| | |
| 7 | FAST DUAL                                          | | 1 |
-------------------------------------------------------------------

Its work as nested loop or Filter clause in SQL Server, for each outer inner will get invoke\executed.

Had used Lateral Views to get all prime number till 100.
SQL for same,

SELECT 1 as prime_number FROM DUAL
UNION ALL
SELECT COL1
FROM
(SELECT COL1 ,
COUNT(
CASE
WHEN MOD(COL1 ,
CASE
WHEN COL2 = 1
OR COL2 = COL1
THEN NULL
ELSE COL2
END) = 0
THEN 1
ELSE NULL
END) CNT
FROM
(SELECT LEVEL + 1 COL1 FROM DUAL CONNECT BY LEVEL <= 99
) L ,
LATERAL
(SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1
)
GROUP BY COL1
ORDER BY 1
)
WHERE CNT = 0;

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle 12c New Features, Oracle Internal and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s