TABLE / CAST / MULTISET vs in subquery in FROM clause
The following request doesn't work. It is expected to fail as it temp.col
refers to something that is not available in this context.
with temp as (
select 'A' col from dual
union all
select 'B' col from dual
)
select *
from temp,
(select level || temp.col from dual connect by level < 3);
Error message from Oracle: ORA-00904: "TEMP". "COL": Invalid ID
But why does the following query work? I see CAST / MULTISET as a way to go from SQL table to collection type and TABLE to go back to SQL table. Why do we use such a round trip? I think the query would work, but how?
with temp as (
select 'A' col from dual
union all
select 'B' col from dual
)
select *
from temp,
table(
cast(
multiset(
select level || temp.col from dual connect by level < 3
) as sys.odcivarchar2list
)
) t;
Result:
COL COLUMN_VALUE
--- ------------
A 1A
A 2A
B 1B
B 2B
See how the second column is called COLUMN_VALUE
. Looks like a generated name from one of the CAST / MULTISET or TABLE constructs.
EDIT
With the accepted answer below, I checked the documentation and found that the TABLE mechanism is an expression of a collection of tables. The expression between the parentheses is a collection expression. The documentation defines a mechanism called left - hand correlation :
Collection_expression can refer to columns of tables defined for it on the left in the FROM clause. This is called left-hand correlation. Left correlation can only happen in table_collection_expression. Other subqueries cannot reference columns defined outside the subquery.
So it looks like LATERAL in 12c.
source to share
Oracle allows side inline views to reference other tables within an inline view.
In older versions, this feature was mostly used for optimization as described on the Oracle Optimizer blog here . Explicit side joins were added in 12c. The first request requires a small change to work in 12c:
with temp as (
select 'A' col from dual
union all
select 'B' col from dual
)
select *
from temp,
lateral(select level || temp.col from dual connect by level < 3);
Apparently Oracle also silently uses side joins to fail the collection. There are a few cases where SQL uses a logical cross join, but the tables are clearly tightly coupled; such as XMLTable, JSON_table and queries like your second example. In these cases, it makes sense to run both tables together. I am assuming that it uses a lateral mechanism, although neither the execution plan nor the 10053 optimizer trace uses the word lateral. The documentation even has an example very similar to yours in Collection Unnesting: Examples . However, this "feature" is still not documented.
On the side of the note, in general, you should avoid SQL functions that increase context. Features like side joins, generic table expressions, and correlated subqueries can be helpful, but they can also make SQL queries difficult to understand. A normal inline view can be triggered and understood on its own and has a very simple interface - its projected columns. This simplicity makes it easy to assemble small components into a large operator.
I suggest you rewrite your query as shown below. Treat each built-in view like a function or procedure — give it good names and comments. This will help you later when you put them together into big, realistic statements.
select col, the_level||col
from
(
--Good comment 1.
select 'A' col from dual union all
select 'B' col from dual
) good_name_1
cross join
(
--Good comment 2.
select level the_level
from dual
connect by level < 3
) good_name_2
source to share