2011 年，一位 ITPUB 的网友请求优化如下 SQL，该 SQL 执行不出结果。

    1.   explain plan for   select ((v.yvalue * 300) / (u.xvalue * 50)), u.xtime  
    2.     from (select x.index_value xvalue, substr(x.update_time, 1, 14) xtime  
    3.            from tb_indexs x  
    4.            where x.id in (select  min(a.id)  
    5.                             from tb_indexs a  
    6.                            where a.code = 'HSI'  
    7.                              and a.update_time > 20110701000000  
    8.                              and a.update_time < 20110722000000  
    9.                            group by a.update_time)) u,  
    10.          (select  y.index_value yvalue, substr(y.update_time, 1, 14) ytime  
    11.             from tb_indexs y  
    12.            where y.id in (select  min(b.id)  
    13.                             from tb_indexs b  
    14.                            where b.code = '000300'  
    15.                              and b.update_time > 20110701000000  
    16.                              and b.update_time < 20110722000000  
    17.                            group by b.update_time)) v  
    18.    where u.xtime = v.ytime
    19.    order by u.xtime;
    1. Explained.
    1. select * from table(dbms_xplan.display);
    1. PLAN_TABLE_OUTPUT  
    2. -------------------------------------------------------------------------------------
    4. Plan hash value: 573554298  
    6. -------------------------------------------------------------------------------------
    7. | Id | Operation                         | Name            | Rows | Bytes |Cost(%CPU)|
    8. -------------------------------------------------------------------------------------
    9. |  0 | SELECT STATEMENT                  |                 |    1 |    54 |   13  (8)|
    10. |  1 |  SORT ORDER BY                    |                 |    1 |    54 |   13  (8)|
    11. |  2 |   NESTED LOOPS                    |                 |    1 |    54 |   12  (0)|
    12. |  3 |    MERGE JOIN CARTESIAN           |                 |    1 |    33 |   10  (0)|
    13. |  4 |     NESTED LOOPS                  |                 |    1 |    27 |    6  (0)|
    14. |  5 |      VIEW                         | VW_NSO_2        |    1 |     6 |    4  (0)|
    15. |  6 |       HASH GROUP BY               |                 |    1 |    41 |    4  (0)|
    16. |  7 |        TABLE ACCESS BY INDEX ROWID| TB_INDEXS       |    1 |    41 |    4  (0)|
    17. |* 8 |         INDEX RANGE SCAN          | IDX_UPDATE_TIME |    1 |       |    3  (0)|
    18. |  9 |      TABLE ACCESS BY INDEX ROWID  | TB_INDEXS       |    1 |    21 |    2  (0)|
    19. |*10 |       INDEX UNIQUE SCAN           | PK_INDEXS       |    1 |       |    1  (0)|
    20. | 11 |     BUFFER SORT                   |                 |    1 |     6 |    8  (0)|
    21. | 12 |      VIEW                         | VW_NSO_1        |    1 |     6 |    4  (0)|
    22. | 13 |       HASH GROUP BY               |                 |    1 |    41 |    4  (0)|
    23. | 14 |        TABLE ACCESS BY INDEX ROWID| TB_INDEXS       |    1 |    41 |    4  (0)|
    24. |*15 |         INDEX RANGE SCAN          | IDX_UPDATE_TIME |    1 |       |    3  (0)|
    25. |*16 |    TABLE ACCESS BY INDEX ROWID    | TB_INDEXS       |    1 |    21 |    2  (0)|
    26. |*17 |     INDEX UNIQUE SCAN             | PK_INDEXS       |    1 |       |    1  (0)|
    27. -------------------------------------------------------------------------------------
    29. Predicate Information (identified by operation id):
    30. ---------------------------------------------------
    32.    8 - access("A"."UPDATE_TIME">20110701000000 AND "A"."CODE"='HSI' AND
    33.               "A"."UPDATE_TIME"<20110722000000)
    34.        filter("A"."CODE"='HSI')
    35.   10 - access("X"."ID"="$nso_col_1")
    36.   15 - access("B"."UPDATE_TIME">20110701000000 AND "B"."CODE"='000300' AND
    37.               "B"."UPDATE_TIME"<20110722000000)
    38.        filter("B"."CODE"='000300')
    39.   16 - filter(SUBSTR(TO_CHAR("X"."UPDATE_TIME"),1,14)=SUBSTR(TO_CHAR("Y"."UPDATE_TIME"),1,14)
    40.               )
    41.   17 - access("Y"."ID"="$nso_col_1")
    43. 38 rows selected.

    大家请仔细观察 SQL 语句，该 SQL 访问的都是同一个表 TB_INDEXS，表在 SQL 语句中被访问了 4 次，我们可以对 SQL 进行等价改写，让 SQL 只访问一次，从而就达到了优化目的。

    但是，网友希望在不改写 SQL 的前提下优化该 SQL 语句，因此只能从执行计划入手优化 SQL。执行计划中，Id=3 是笛卡儿积，这就是为什么该 SQL 执行不出结果。为什么会产生笛卡儿积呢？因为执行计划中所有的步骤 Rows 都估算返回为 1 行数据，所以优化器选择了笛卡儿积连接（在 5.4 节中我们讲过，离笛卡儿积关键字最近的「表」被错误地估算为 1 行的时候，优化器很容易选择走笛卡儿积连接）。

    执行计划的入口是 Id=8，也就是 SQL 语句中的 in 子查询，优化器评估 Id=8 返回 1 行数据，但是实际上 Id=8 要返回 2 万行数据。笔者曾经尝试对表 TB_INDEXS 重新收集统计信息，但是收集完统计信息之后，优化器还是评估 Id=8 返回 1 行数据。

    为什么优化器会评估 Id=8 返回 1 行数据呢？这是因为字段 UPDATE_TIME 被设计为了 NUMBER 类型，而实际上 UPDATE_TIME 应该是 DATE 类型，同时 where 条件中还有一个选择性较低的过滤条件，优化器估算返回的行数等于表的总行数与 UPDATE_TIME 的选择性、CODE 的选择性的乘积。UPDATE_TIME 因为字段类型设计错误，本来应该估算返回 21 天的数据，但是因为 UPDATE_TIME 设计为了 NUMBER 类型，导致优化器在估算返回行数的时候不是利用 DATE 类型估算返回行数，而是利用 NUMBER 类型估算返回行数。大家请注意观察 UPDATE_TIME 的过滤条件，将年月日存储为 NUMBER 类型是一个天文数字，然后 where 条件只是取出一个天文数字中极小一部分数据，因此估算返回的行数始终会被估算为 1 行，

    因为执行计划入口的 Rows 估算错误，所以后面的执行计划不用看，全是错误的。因为 UPDATE_TIME 已经被设计为 NUMBER 类型了，想要通过修改 UPDATE_TIME 为 DATE 类型来纠正优化器估算返回的 Rows 是不可行的，因为需要申请停机时间。

    怎么才可以让优化器知道真实 Rows 呢？我们可以使用 HINT：CARDINALITY。

    /*+ cardinality（a 10000）*/表示指定 a 表有 1 万行数据。

    /*+ cardinality（@a 10000）*/表示指定 query block a 有 1 万行数据。

    添加完 HINT 后的执行计划如下。

    1.  set autot trace  
    2.  select /*+ cardinality(@a 20000) cardinality(@b 20000) */((v.yvalue * 300)/(u.xvalue * 50)), u.xtime  
    3.    from (select x.index_value xvalue, substr(x.update_time, 1, 14) xtime  
    4.             from tb_indexs x  
    5.            where x.id in (select /*+ QB_NAME(a) */ min(a.id)  
    6.                             from tb_indexs a  
    7.                            where a.code = 'HSI'  
    8.                              and a.update_time > 20110701000000  
    9.                              and a.update_time < 20110722000000  
    10.                           group by a.update_time)) u,  
    11.          (select  y.index_value yvalue, substr(y.update_time, 1, 14) ytime  
    12.             from tb_indexs y  
    13.           where y.id in (select /*+ QB_NAME(b) */ min(b.id)  
    14.                            from tb_indexs b  
    15.                            where b.code = '000300'  
    16.                              and b.update_time > 20110701000000  
    17.                              and b.update_time < 20110722000000  
    18.                            group by b.update_time)) v  
    19.    where u.xtime = v.ytime
    20.    order by u.xtime;
    1. 3032 rows selected.
    1. Elapsed: 00:00:15.07
    3. Execution Plan
    4. ----------------------------------------------------------  
    5. Plan hash value: 2679503093
    7. -------------------------------------------------------------------------------------
    8. | Id | Operation                         | Name            | Rows| Bytes |Cost(%CPU)|
    9. -------------------------------------------------------------------------------------
    10. |  0 | SELECT STATEMENT                  |                 |  935| 50490 | 1393  (7)|
    11. |  1 |  SORT ORDER BY                    |                 |  935| 50490 | 1393  (7)|
    12. |* 2 |   HASH JOIN                       |                 |  935| 50490 | 1392  (7)|
    13. |  3 |    VIEW                           | VW_NSO_1        |20000|   117K|    4  (0)|
    14. |  4 |     HASH GROUP BY                 |                 |20000|   800K|    4  (0)|
    15. |  5 |      TABLE ACCESS BY INDEX ROWID  | TB_INDEXS       |    1|    41 |    4  (0)|
    16. |* 6 |       INDEX RANGE SCAN            | IDX_UPDATE_TIME |    1|       |    3  (0)|
    17. |* 7 |    HASH JOIN                      |                 |31729|  1487K| 1386  (7)|
    18. |* 8 |     HASH JOIN                     |                 |20000|   527K|  695  (7)|
    19. |  9 |      VIEW                         | VW_NSO_2        |20000|   117K|    4  (0)|
    20. | 10 |       HASH GROUP BY               |                 |20000|   800K|    4  (0)|
    21. | 11 |        TABLE ACCESS BY INDEX ROWID| TB_INDEXS       |    1|    41 |    4  (0)|
    22. |*12 |         INDEX RANGE SCAN          | IDX_UPDATE_TIME |    1|       |    3  (0)|
    23. | 13 |      TABLE ACCESS FULL            | TB_INDEXS       | 678K|    13M|  678  (5)|
    24. | 14 |     TABLE ACCESS FULL             | TB_INDEXS       | 678K|    13M|  678  (5)|
    25. -------------------------------------------------------------------------------------
    27. Predicate Information (identified by operation id):
    28. ---------------------------------------------------
    30.    2 - access("Y"."ID"="$nso_col_1")  
    31.    6 - access("B"."UPDATE_TIME">20110701000000 AND "B"."CODE"='000300' AND  
    32.               "B"."UPDATE_TIME"<20110722000000)  
    33.        filter("B"."CODE"='000300')  
    34.    7 - access(SUBSTR(TO_CHAR("X"."UPDATE_TIME"),1,14)=SUBSTR(TO_CHAR("Y"."UPDATE_TIME"),1,14)  
    35.               )
    36.    8 - access("X"."ID"="$nso_col_1")  
    37.   12 - access("A"."UPDATE_TIME">20110701000000 AND "A"."CODE"='HSI' AND  
    38.               "A"."UPDATE_TIME"<20110722000000)  
    39.        filter("A"."CODE"='HSI')  
    41. Statistics  
    42. ----------------------------------------------------------
    43.          29  recursive calls
    44.           0  db block gets
    45.        8351  consistent gets
    46.        4977  physical reads
    47.          72  redo size
    48.      141975  bytes sent via SQL*Net to client
    49.        2622  bytes received via SQL*Net from client
    50.         204  SQL*Net roundtrips to/from client
    51.           1  sorts (memory)  
    52.           0  sorts (disk)  
    53.        3032  rows processed

    通过指定执行计划入口（子查询）返回 2 万行数据，纠正了之前错误的执行计划，SQL 最终执行了 15 秒就返回了所有的结果。

    如果不知道有 CARDINALITY 这个 HINT，怎么优化 SQL 呢？我们可以启用动态采样 Level 4 及以上（最好别超过 6），让优化器能较为准确地评估出子查询返回的 Rows，这样也能达到优化目的。如果不知道动态采样怎么优化 SQL 呢？我们可以直接使用 HINT，比如 USE_HASH 等，让 SQL 走我们认为正确的执行计划也能达到优化目的。当然了，最佳的优化方法应该是直接从业务上入手，从表设计上入手，从 SQL 写法上入手，而不是退而求其次从执行计划入手，但是很多时候我们往往只能从执行计划上入手优化 SQL，这或许是绝大多数 DBA 的无奈。