Little enhancements are always nice

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.

Advertisements

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