Why oracle constructs cartesian join with 'start with' statement

Question

I have query like this

select unique so.object_id, bpi_id
from order_items,
     nc_objects so
where so.object_type_id = 9062352550013045460 /* Sales Order */
start with so.object_id = order_items.object_id
connect by prior so.parent_id = so.object_id

Oracle version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

'explain plan' shows that there is no join conditions between tables so and order_items

explain plan of query

Can someone explain me why they are not connected by the start with statement?

I rewrited my initial query:

select unique (select so.object_id
               from nc_objects so
               where so.object_type_id = 9062352550013045460 /* Sales Order */
               start with so.object_id = order_items.object_id
               connect by prior so.parent_id = so.object_id) so_id,
              bpi_id
from order_items

It works, but only because of so have one row per order_items row.

Answer 1

If you do:

select so.object_id,
       bpi_id,
       SYS_CONNECT_BY_PATH(order_items.object_id||':'||so.object_id || ':' || bpi_id, ',') AS path
from   order_items,
       nc_objects so
where  so.object_type_id = 100/* Sales Order */
start with so.object_id = order_items.object_id
connect by prior so.parent_id = so.object_id

Then for the sample data:

CREATE TABLE order_items (object_id, bpi_id) AS
SELECT LEVEL, CHR(64+LEVEL) FROM DUAL CONNECT BY LEVEL <= 3;

CREATE TABLE nc_objects (object_id, object_type_id, parent_id) AS
SELECT LEVEL, 100, LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT LEVEL+10, 200, LEVEL + 10 - 1 FROM DUAL CONNECT BY LEVEL <= 3;

The output is:

OBJECT_ID	BPI_ID	PATH
1	A	,1:1:A
2	B	,2:2:B
1	A	,2:2:B,1:1:A
1	C	,2:2:B,3:1:C
1	B	,2:2:B,2:1:B
3	C	,3:3:C
2	A	,3:3:C,1:2:A
1	A	,3:3:C,1:2:A,1:1:A
1	C	,3:3:C,1:2:A,3:1:C
1	B	,3:3:C,1:2:A,2:1:B
2	C	,3:3:C,3:2:C
1	A	,3:3:C,3:2:C,1:1:A
1	C	,3:3:C,3:2:C,3:1:C
1	B	,3:3:C,3:2:C,2:1:B
2	B	,3:3:C,2:2:B
1	A	,3:3:C,2:2:B,1:1:A
1	C	,3:3:C,2:2:B,3:1:C
1	B	,3:3:C,2:2:B,2:1:B

You can see from the path that the so.object_id only equals the order_items.object_id for the START WITH row but for all the descendant rows every so.object_id is connected to every order_items.object_id with a CROSS JOIN.

If you want to rewrite it as a recursive query then:

WITH data (oi_id, so_id, bpi_id, parent_id, object_type_id) AS (
  SELECT oi.object_id,
         so.object_id,
         oi.bpi_id,
         so.parent_id,
         so.object_type_id
  FROM   order_items oi
         CROSS JOIN nc_objects so
),
hierarchy (so_id, bpi_id, parent_id, object_type_id, path) AS (
  SELECT so_id,
         bpi_id,
         parent_id,
         object_type_id,
         ','||oi_id||':'||so_id||':'||bpi_id
  FROM   data
  WHERE  so_id = oi_id
UNION ALL
  SELECT d.so_id,
         d.bpi_id,
         d.parent_id,
         d.object_type_id,
         h.path || ','||d.oi_id||':'||d.so_id||':'||d.bpi_id
  FROM   data d
         INNER JOIN hierarchy h
         ON (h.parent_id = d.so_id)
) SEARCH DEPTH FIRST BY so_id SET order_id
select so_id AS object_id,
       bpi_id,
       path
from   hierarchy
where  object_type_id = 100

Which outputs the same.

You can see that the join condition WHERE so_id = oi_id is only applied on the first join of the recursive query, the equivalent of the START WITH clause of the hierarchical query, and is not applied to any of the rows generated in the recursive part of the query, the equivalent of the CONNECT BY clause; therefore, it is not appropriate to join the two tables using the START BY clause as that only applies to the first row of the hierarchy and the rest are CROSS JOINed.

---

If you did want to JOIN at each level of the hierarchy then don't use a CROSS JOIN and use INNER JOIN instead:

select so.object_id,
       bpi_id,
       SYS_CONNECT_BY_PATH(order_items.object_id||':'||so.object_id || ':' || bpi_id, ',') AS path
from   order_items
       INNER JOIN nc_objects so
       ON so.object_id = order_items.object_id
where  so.object_type_id = 100 /* Sales Order */
connect by prior so.parent_id = so.object_id

Which outputs:

OBJECT_ID	BPI_ID	PATH
1	A	,1:1:A
2	B	,2:2:B
1	A	,2:2:B,1:1:A
3	C	,3:3:C
2	B	,3:3:C,2:2:B
1	A	,3:3:C,2:2:B,1:1:A

fiddle