Posted by Before LATERAL and CROSS APPLY were added (exposed to us) in 12c, a common technique to do correlated joins was using the TABLE/MULTISET technique.
For example, we might have had a couple of tables:
SQL> create table t as
2 select object_type, min(created) min_dte, max(created) max_dte
3 from dba_objects
4 where owner = 'SCOTT'
5 group by object_type;
Table created.
SQL> select * from t;
OBJECT_TYPE MIN_DTE MAX_DTE
------------------- --------- ---------
INDEX 09-OCT-13 09-OCT-13
TABLE 09-OCT-13 09-OCT-13
SQL>
SQL> create table t1 as
2 select object_type, object_name, object_id
3 from dba_objects
4 where owner = 'SCOTT';
Table created.
SQL> select * from t1;
OBJECT_TYPE OBJECT_NAME OBJECT_ID
------------------- ---------------------------------------- ----------
INDEX PK_DEPT 86892
TABLE DEPT 86891
TABLE EMP 86893
INDEX PK_EMP 86894
TABLE BONUS 86895
TABLE SALGRADE 86896
6 rows selected.
and then tried to join them in what seems to be a logical fashion, but got the following error
SQL> select *
2 from t,
3 ( select object_id
4 from t1
5 where object_type = t.object_type );
where object_type = t.object_type )
*
ERROR at line 5:
ORA-00904: "T"."OBJECT_TYPE": invalid identifier
Now this query could be trivially recast as a normal join, but there are examples where there is not the case, but we’re keeping this one as simple as possible.
We could workaround this by using TABLE and MULTICAST,
SQL> select *
2 from t,
3 table(cast(multiset(
4 select object_id
5 from t1
6 where object_type = t.object_type
7 ) as sys.odcinumberlist));
OBJECT_TYPE MIN_DTE MAX_DTE COLUMN_VALUE
------------------- --------- --------- ------------
INDEX 09-OCT-13 09-OCT-13 86892
TABLE 09-OCT-13 09-OCT-13 86891
TABLE 09-OCT-13 09-OCT-13 86893
INDEX 09-OCT-13 09-OCT-13 86894
TABLE 09-OCT-13 09-OCT-13 86895
TABLE 09-OCT-13 09-OCT-13 86896
6 rows selected.
which is a little “trick”, in effect being
- Take our child rows
- Collapse them into a single complex type (cast-multiset) to “avoid” this being seen as correlated join
- Use the TABLE operator to expand that type back out into rows.
But what if our query within the MULTISET is several “layers” deep. Then we might run into problems – I get this on 11g
SQL> select *
2 from t,
3 table(cast(multiset(
4 select object_id
5 from (
6 select distinct object_id
7 from t1
8 where object_type = t.object_type
9 )
10 ) as sys.odcinumberlist))
11 order by 1,4;
where object_type = t.object_type
*
ERROR at line 8:
ORA-00904: "T"."OBJECT_TYPE": invalid identifier
Because the reference on line 7 to “T.OBJECT_TYPE” is not a single layer from the originating table T, this query will not run in 11g.
But take a look what happens in 12c
SQL> select *
2 from t,
3 table(cast(multiset(
4 select object_id
5 from (
6 select distinct object_id
7 from t1
8 where object_type = t.object_type
9 )
10 ) as sys.odcinumberlist))
11 order by 1,4;
OBJECT_TYPE MIN_DTE MAX_DTE COLUMN_VALUE
----------------------- --------- --------- ------------
INDEX 20-NOV-15 16-DEC-15 92614
INDEX 20-NOV-15 16-DEC-15 92615
INDEX 20-NOV-15 16-DEC-15 99130
PROCEDURE 31-MAY-16 31-MAY-16 148999
TABLE 20-NOV-15 31-MAY-16 92607
TABLE 20-NOV-15 31-MAY-16 92608
TABLE 20-NOV-15 31-MAY-16 92609
TABLE 20-NOV-15 31-MAY-16 92610
TABLE 20-NOV-15 31-MAY-16 92616
TABLE 20-NOV-15 31-MAY-16 99128
TABLE 20-NOV-15 31-MAY-16 149002
TRIGGER 27-NOV-15 27-NOV-15 98228
TRIGGER 27-NOV-15 27-NOV-15 98229
TRIGGER 27-NOV-15 27-NOV-15 98230
14 rows selected.
No dramas at all. I always like these little touches that come along without too much fanfare, but make a developer’s life easier.
Learning is not a spectator sport 