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 |
--------------------------------------------------------------------------------------------------------------------