Oracle SQL 语句全表扫描优化：子查询索引失效分析

Oracle 中，存在如下 SQL 语句：

SELECT work_order
FROM g_sn_status
WHERE serial_number = (SELECT b.serial_number
                           FROM xx_packing_bind_inner a, 
                                xx_packing_bind_inner_detail b
                          WHERE     a.id = b.p_id
                                AND ROWNUM = 1
                                AND a.inner_number = '38905B9D29')

该语句中所有字段均已建立索引，然而仍然导致 g_sn_status 表进行全表扫描。

造成上述问题的原因在于子查询结果集没有利用索引。

在子查询中，serial_number 字段没有作为子查询的条件，而是作为结果集的一部分。子查询的条件则是通过连接操作 xx_packing_bind_inner 和 xx_packing_bind_inner_detail 表来实现。连接操作可能会导致索引失效。因此，执行子查询时，无法使用索引进行优化，需要对整个表进行全表扫描。

为了避免全表扫描，可以考虑以下优化方法：

将子查询结果作为主查询条件： 可以尝试将子查询的结果直接作为主查询的条件，避免使用连接操作。例如：

SELECT work_order
FROM g_sn_status
WHERE serial_number IN (SELECT b.serial_number
                           FROM xx_packing_bind_inner a, 
                                xx_packing_bind_inner_detail b
                          WHERE     a.id = b.p_id
                                AND ROWNUM = 1
                                AND a.inner_number = '38905B9D29')

使用联合查询： 可以考虑使用联合查询来优化查询语句。例如：

SELECT work_order
FROM g_sn_status
WHERE serial_number IN (SELECT b.serial_number
FROM xx_packing_bind_inner a 
JOIN xx_packing_bind_inner_detail b ON a.id = b.p_id
WHERE a.inner_number = '38905B9D29'
AND ROWNUM = 1)

优化子查询条件： 可以尝试优化子查询的条件，使其能够使用索引。例如，可以使用 EXISTS 操作符来判断是否存在符合条件的记录，或者使用 ROWNUM = 1 来获取第一条符合条件的记录，避免对整个表进行扫描。

通过以上方法，可以有效地避免子查询导致的全表扫描，提升查询性能。