为何Oracle使用START WITH语句时会产生笛卡尔积连接？

问题原因 #

初始查询的写法逻辑有误：Oracle的START WITH和CONNECT BY是层次查询的控制子句，仅负责定义层次遍历的起点和遍历规则，不能用来作为表与表之间的连接条件。

你原来的写法里，FROM order_items, nc_objects so会默认生成两张表的笛卡尔积（交叉连接），之后START WITH so.object_id = order_items.object_id只是从这个笛卡尔积结果里筛选出符合条件的行作为层次查询的起点，并不是真正意义上的表关联。这就是执行计划里看不到两张表连接条件的原因——根本没有显式/隐式的JOIN逻辑，只是先做了全量交叉再过滤，效率极低。

正确写法 #

如果需要从order_items出发关联nc_objects的层次数据，且支持一个order_items对应多条so记录的场景，可以用以下两种方式：

方法1：JOIN结合层次查询 #

先通过JOIN建立表关联，再对关联结果做层次遍历：

SELECT UNIQUE so.object_id, oi.bpi_id
FROM order_items oi
JOIN nc_objects so ON so.object_id = oi.object_id
WHERE so.object_type_id = 9062352550013045460 /* Sales Order */
START WITH so.object_id = oi.object_id
CONNECT BY PRIOR so.parent_id = so.object_id

方法2：LATERAL JOIN（Oracle 12c+支持） #

如果需要对每个order_items行单独执行层次查询（支持一对多场景），可以用LATERAL关联子查询：

SELECT UNIQUE so.object_id, oi.bpi_id
FROM order_items oi
LATERAL (
    SELECT so.object_id
    FROM nc_objects so
    WHERE so.object_type_id = 9062352550013045460 /* Sales Order */
    START WITH so.object_id = oi.object_id
    CONNECT BY PRIOR so.parent_id = so.object_id
) so

这种写法和你原来的子查询写法类似，但LATERAL允许子查询返回多行结果，不会因为一个order_items对应多条so记录而报错。

补充说明 #

你之前的改写查询用了标量子查询，标量子查询要求必须返回且仅返回1行结果，所以只能处理单条so记录的场景。而LATERAL JOIN（或Oracle的OUTER APPLY/CROSS APPLY）支持子查询返回多行，适用性更广。

内容的提问来源于stack exchange，提问作者stevegrkek