Avoid cartesian join with "INNER JOIN TABLE(user_defined_array)"?

990 views Asked by At

I know how to avoid cartesian joins with an inner join in general cases.

Is there a way to avoid a cartesian join, in Oracle, when joining to a table created from a user-defined array, like so?

select col1, col2, ...
from mytable mt
inner join TABLE(user_defined_number_array(1,2,3,4)) t ON t.column_value = mytable.col1
... --inner join other tables
...
where mt.col1 IN (
  SELECT column_value FROM TABLE(user_defined_number_array(1,2,3,4))
);

(In this case, user_defined_number_array is a table of number(18), and col1 is of the same type.)

Is the only way around this to define a table type with a keyed value? (Note that, for the purposes of this, I expect the arrays to be quite small - probably never more than ten rows.)

Thank you.

EDIT: Explain plan is below, and I've updated the query to better reflect what I'm doing. I didn't obscure the explain plan, because I figure it probably isn't worth it.

Plan hash value: 3336195920

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                     |    18 |  1224 |   183K  (1)| 00:36:40 |
|   1 |  NESTED LOOPS OUTER                          |                     |    18 |  1224 |   183K  (1)| 00:36:40 |
|   2 |   NESTED LOOPS                               |                     |    18 |  1026 |   183K  (1)| 00:36:40 |
|   3 |    NESTED LOOPS                              |                     |    18 |   792 |   183K  (1)| 00:36:40 |
|   4 |     NESTED LOOPS                             |                     |    18 |   684 |   183K  (1)| 00:36:40 |
|   5 |      MERGE JOIN CARTESIAN                    |                     |  8168 | 32672 |   179K  (1)| 00:36:00 |
|   6 |       COLLECTION ITERATOR CONSTRUCTOR FETCH  |                     |       |       |            |          |
|   7 |       BUFFER SORT                            |                     |  8168 | 16336 |            |          |
|   8 |        SORT UNIQUE                           |                     |       |       |            |          |
|   9 |         COLLECTION ITERATOR CONSTRUCTOR FETCH|                     |       |       |            |          |
|  10 |      TABLE ACCESS BY INDEX ROWID             | LOG_ACTIVITY        |     1 |    34 |     1   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN                       | LOG_ACTIVITYC3      |     1 |       |     1   (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID              | RESPONSE_CODE       |     1 |     6 |     1   (0)| 00:00:01 |
|* 13 |      INDEX UNIQUE SCAN                       | RESPONSE_CODEP1     |     1 |       |     1   (0)| 00:00:01 |
|  14 |    TABLE ACCESS BY INDEX ROWID               | LOG_ACTIVITY_TYPE   |     1 |    13 |     1   (0)| 00:00:01 |
|* 15 |     INDEX UNIQUE SCAN                        | LOG_ACTIVITY_TYPEP1 |     1 |       |     1   (0)| 00:00:01 |
|  16 |   TABLE ACCESS BY INDEX ROWID                | EMPLOYEE            |     1 |    11 |     1   (0)| 00:00:01 |
|* 17 |    INDEX UNIQUE SCAN                         | EMPLOYEEP1          |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
0

There are 0 answers