关于主表.列=子表.列这种写法隐患——子查询无法展开、返回多行报错

碰到一个SQL语句执行计划不合理,就详细分析了一下,结果发现自己对于子查询理解还存在一定的盲区,整理如下:

select wm_concat(r.recdefid)
  from t_test r
 where r.servnumber = (select t.servnumber
                         from t_test t
                        where t.oid = :1
                          and t.region = r.region)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

Plan hash value: 3667943477

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |       |       | 25480 (100)|          |       |       |
|   1 |  SORT AGGREGATE                      |                        |     1 |    37 |            |          |       |       |
|*  2 |   FILTER                             |                        |       |       |            |          |       |       |
|   3 |    PARTITION RANGE MULTI-COLUMN      |                        |    19 |   703 | 25270   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                 |    19 |   703 | 25270   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|*  5 |      INDEX SKIP SCAN                 | IDX_T_TEST_RECORGID    |    20 |       | 25255   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|   6 |    PARTITION RANGE ITERATOR          |                        |     1 |    24 |   209   (0)| 00:00:03 |   KEY |   KEY |
|   7 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                 |     1 |    24 |   209   (0)| 00:00:03 |   KEY |   KEY |
|*  8 |      INDEX RANGE SCAN                | PK_CS_REC_T_TEST       |     1 |       |   208   (0)| 00:00:03 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("R"."SERVNUMBER"=)
   5 - access("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
       filter("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
   8 - access("T"."OID"=TO_NUMBER(:1) AND "T"."REGION"=:B1)
       filter("T"."REGION"=:B1)

该SQL执行计划确实是有问题的,合理的执行计划是先对子查询的t_test t表经过oid的唯一索引,找到一条servernumber,然后将这条数据传递给t_test r表,然后对表t_test r走servernumber上的索引,但是优化器这里先选择了外部表t_test r为驱动表,走r.recdate > sysdate – 1 / 144字段的跳跃索引,然后去filter(类似于nested loops)子查询表t.oid = :1

那么优化器为什么在原SQL是=时候不选择用子查询结果集做驱动表了,具体原因是跟cost计算、还是子查询无法展开表连接有关?

排除cost成本问题:
这里加上hint leadind指定子查询的T@SEL$2表做驱动表,也就是t_test t表,cbo忽略掉了这个hint,还是走原来的执行计划,一般而言只要hint的执行计划是可行的并且不违背sql的返回结果,优化器就会走hint的执行计划,加上hint的SQL跟cost成本计算无关:

explain plan for
select /*+leading(T@SEL$2)*/wm_concat(r.recdefid)
  from t_test r
 where r.servnumber =(select t.servnumber
                         from t_test t
                        where t.oid = :1
                          and t.region = r.region)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

select * from table(dbms_xplan.display(null,null,'advanced'))

Plan hash value: 3667943477

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |     1 |    37 | 25480   (1)| 00:05:06 |       |       |
|   1 |  SORT AGGREGATE                      |                        |     1 |    37 |            |          |       |       |
|*  2 |   FILTER                             |                        |       |       |            |          |       |       |
|   3 |    PARTITION RANGE MULTI-COLUMN      |                        |    19 |   703 | 25270   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                 |    19 |   703 | 25270   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|*  5 |      INDEX SKIP SCAN                 | IDX_T_TEST_RECORGID    |    20 |       | 25255   (1)| 00:05:04 |KEY(MC)|KEY(MC)|
|   6 |    PARTITION RANGE ITERATOR          |                        |     1 |    24 |   209   (0)| 00:00:03 |   KEY |   KEY |
|   7 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                 |     1 |    24 |   209   (0)| 00:00:03 |   KEY |   KEY |
|*  8 |      INDEX RANGE SCAN                | PK_CS_REC_T_TEST       |     1 |       |   208   (0)| 00:00:03 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / R@SEL$1
   5 - SEL$1 / R@SEL$1
   6 - SEL$2
   7 - SEL$2 / T@SEL$2
   8 - SEL$2 / T@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("R"."SERVNUMBER"= (SELECT "T"."SERVNUMBER" FROM "TBCS"."T_TEST" "T" WHERE "T"."OID"=TO_NUMBER(:1) AND
              "T"."REGION"=:B1))
   5 - access("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
       filter("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
   8 - access("T"."OID"=TO_NUMBER(:1) AND "T"."REGION"=:B1)
       filter("T"."REGION"=:B1)

而如果用in的方式,优化器默认是选择t_test t做驱动表:

explain plan for
select wm_concat(r.recdefid)
  from t_test r
 where r.servnumber  in (select t.servnumber
                         from t_test t
                        where t.oid = :1
                          and t.region = r.region)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

Plan hash value: 1325607285

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |     1 |    61 |   360   (1)| 00:00:05 |       |       |
|   1 |  SORT AGGREGATE                        |                       |     1 |    61 |            |          |       |       |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID    | T_TEST                |     1 |    37 |   147   (0)| 00:00:02 |       |       |
|   3 |    NESTED LOOPS                        |                       |     8 |    61 |   360   (1)| 00:00:05 |       |       |
|   4 |     SORT UNIQUE                        |                       |     1 |    24 |   212   (0)| 00:00:03 |       |       |
|   5 |      PARTITION RANGE ALL               |                       |     1 |    24 |   212   (0)| 00:00:03 |     1 |    70 |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                |     1 |    24 |   212   (0)| 00:00:03 |     1 |    70 |
|*  7 |        INDEX RANGE SCAN                | PK_CS_REC_T_TEST      |     1 |       |   211   (0)| 00:00:03 |     1 |    70 |
|   8 |     PARTITION RANGE ITERATOR           |                       |     8 |       |   140   (0)| 00:00:02 |   KEY |   KEY |
|*  9 |      INDEX RANGE SCAN                  | IDX_T_TEST_SERVNUM    |     8 |       |   140   (0)| 00:00:02 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / R@SEL$1
   6 - SEL$5DA710D3 / T@SEL$2
   7 - SEL$5DA710D3 / T@SEL$2
   9 - SEL$5DA710D3 / R@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444 AND "T"."REGION"="R"."REGION")
   7 - access("T"."OID"=TO_NUMBER(:1))
   9 - access("R"."SERVNUMBER"="T"."SERVNUMBER")

in的书写方式oracle可以将其展开为表连接,从而优化器能够自行选择返回数据更少的t_test t表做驱动表。

很显然原SQL语句没办法将子查询展开表连接,这个我们在原SQL中强制添加hint unnest的方式,执行计划还是没有变化

select wm_concat(r.recdefid)
  from t_test r
 where r.servnumber =(select /*+unnest*/t.servnumber
                         from t_test t
                        where t.oid = :1
                          and t.region = r.region)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

也不是所有的=子查询就会导致无法展开表连接的,比如这里我们将原SQL的t.region = r.region去掉

explain plan for
select wm_concat(r.recdefid)
  from t_test r
 where r.servnumber =(select t.servnumber
                         from t_test t
                        where t.oid = :1)
   and r.recdate > sysdate - 1 / 144
 order by r.recdate asc

select * from table(dbms_xplan.display)

Plan hash value: 1804346429

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |     1 |    34 |   147   (0)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE                        |                       |     1 |    34 |            |          |       |       |
|   2 |   PARTITION RANGE MULTI-COLUMN         |                       |     1 |    34 |   147   (0)| 00:00:02 |KEY(MC)|KEY(MC)|
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID   | T_TEST                |     1 |    34 |   147   (0)| 00:00:02 |KEY(MC)|KEY(MC)|
|*  4 |     INDEX RANGE SCAN                   | IDX_T_TEST_SERVNUM    |     7 |       |   141   (0)| 00:00:02 |KEY(MC)|KEY(MC)|
|   5 |      PARTITION RANGE ALL               |                       |     1 |    21 |   212   (0)| 00:00:03 |     1 |    70 |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| T_TEST                |     1 |    21 |   212   (0)| 00:00:03 |     1 |    70 |
|*  7 |        INDEX RANGE SCAN                | PK_CS_REC_T_TEST      |     1 |       |   211   (0)| 00:00:03 |     1 |    70 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("R"."RECDATE">SYSDATE@!-.006944444444444444444444444444444444444444)
   4 - access("R"."SERVNUMBER"= (SELECT "T"."SERVNUMBER" FROM "TBCS"."T_TEST" "T" WHERE "T"."OID"=TO_NUMBER(:1)))
   7 - access("T"."OID"=TO_NUMBER(:1))

可见在这个SQL中造成执行计划错误是因为cbo没有办法将子查询展开为表连接,而造成没有办法展开为表连接的原因则是因为该sql书写方式是=的子查询书写方式,并且子查询中还嵌套了主查询的比较列,优化器对于子查询能够展开必须要和原SQL业务逻辑一致,这里优化器并不能智能的将该子查询展开。

关于子查询的写法:
尽量写成in、not in、exists和not exists方式,在oracle 11g(oracle 11g推出了null-aware anti join的算法,不过现网系统中对于该特性的隐含参数被置为了false)之前not in的方式如果比较列没有not null约束还是会导致子查询无法展开,写成=的方式不仅仅有隐患(如果子查询中还有主查询的列去做比较,优化器就没办法将其展开为表连接,这个在oracle 12c中都是如此,也没有办法展开为表连接),而且如果子查询返回多行数据,该SQL执行时候还会报错,如下:

SQL> select count(*) from t_test02 where object_id=100;

  COUNT(*)
----------
         3

SQL> select a.object_id from t_test01 a where a.data_object_id=(select b.data_object_id from t_test02 b where b.object_id=100);
select a.object_id from t_test01 a where a.data_object_id=(select b.data_object_id from t_test02 b where b.object_id=100)
                                                           *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

About xiaoyu

xiaoyu,享受数据库带给xiaoyu的乐趣! 13439818916@163.com 欢迎邮件联系讨论
This entry was posted in oracle, sql tuning and troubleshooting. Bookmark the permalink.