为什么SQL 语句加上 WHERE ROWNUM < 10 速度非常慢

stziyan

Linux 版本CentOS release 5.5(Final)，oracle 10.2.0.1
以下sql不加 ROWNUM < 10 速度还可以，加上 ROWNUM < 10之后速度奇慢无比。执行计划如下，不知是什么原因。
附件是txt格式，方便查看执行计划。
SQL> set autotrace traceonly  
SQL>  SELECT   a.id,
  2                      a.title,
  3                      a.G_TIME,
  4                      NB.name
  5               FROM      PC_NDCINFO a
  6                      LEFT JOIN
  7                         PC_NBSCONF NB
  8                      ON NB.id = a.board_id
  9              WHERE   (a.G_TIME BETWEEN TO_DATE ('2012-04-17 11:26:11',
10                                                    'yyyy-MM-dd HH24:mi:ss')
11                                        AND  TO_DATE ('2012-04-18 11:26:11',
12                                                     'yyyy-MM-dd HH24:mi:ss'))
13                      AND (a.board_id IN
14                                (SELECT   DISTINCT a.board_id
15                                   FROM   PC_dmsion_site a
16                                  WHERE   a.dmsion_id IN
17                                               (SELECT   a.dimention_id
18                                                  FROM   PC_user_dmsion a
19                                                 WHERE   a.user_id = 5055
20                                                         AND a.channel_type =
21                                                               1)))
22                      AND a.repeat_flag = 0
23            ORDER BY  a.G_TIME DESC;

no rows selected

Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 2877262749

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                         | Rows  | Bytes | Cost (%CPU)|Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |  5978 |   665K| 26542   (1)|00:05:19 |
|   1 |  SORT ORDER BY                  |                              |  5978 |   665K|26542   (1)| 00:05:19 |
|*  2 |   HASH JOIN RIGHT OUTER         |                              |  5978 |   665K| 26541   (1)|00:05:19 |
|   3 |    TABLE ACCESS FULL            | PC_NBSCONF             |  7333 |   257K|   53   (0)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT SEMI         |                              |  5978 |   455K| 26486   (1)|00:05:18 |
|   5 |     VIEW                        |VW_NSO_1                      |   645 |  2580 |   14   (8)| 00:00:01 |
|*  6 |      HASH JOIN                  |                              |   645 | 12255 |   14   (8)| 00:00:01 |
|*  7 |       TABLE ACCESS BY INDEX ROWID|PC_USER_DMSION             |     4 |    44 |    2   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN         | INDEX_PC_USER_DMSION_USERID |    6 |       |    1   (0)| 00:00:01 |
|   9 |       TABLE ACCESS FULL         | PC_DMSION_SITE             | 11352 | 90816 |   11   (0)| 00:00:01 |
|* 10 |     TABLE ACCESS BY INDEX ROWID  |PC_NDCINFO                |26590 |  1921K| 26472   (1)| 00:05:18 |
|* 11 |      INDEX RANGE SCAN           | FK_PC_NDCINFO_GTIME      | 54004 |       |   231   (1)| 00:00:03|
-------------------------------------------------------------------------------------------------------------------

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

   2 -access("NB"."ID"(+)="A"."BOARD_ID")
   4 -access("A"."BOARD_ID"="$nso_col_1")
   6 - access("A"."DMSION_ID"="A"."DIMENTION_ID")
   7 - filter("A"."CHANNEL_TYPE"=1)
   8 - access("A"."USER_ID"=5055)
  10 - filter("A"."REPEAT_FLAG"=0)
  11 -access("A"."G_TIME">=TO_DATE('2012-04-17 11:26:11','yyyy-mm-dd hh24:mi:ss') AND
             "A"."G_TIME"<=TO_DATE('2012-04-18 11:26:11','yyyy-mm-dd hh24:mi:ss'))
      filter("A"."G_TIME">=TO_DATE('2012-04-17 11:26:11','yyyy-mm-dd hh24:mi:ss') AND
             "A"."G_TIME"<=TO_DATE('2012-04-1811:26:11', 'yyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       282  consistent gets
          0  physical reads
          0  redo size
        513  bytes sent via SQL*Net toclient
        458  bytes received via SQL*Netfrom client
          1  SQL*Net roundtrips to/fromclient
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> SELECT   *
  2    FROM   (  SELECT  a.id,
  3                      a.title,
  4                      a.G_TIME,
  5                      NB.name
  6               FROM      PC_NDCINFO a
  7                      LEFT JOIN
  8                         PC_NBSCONF NB
  9                      ON NB.id = a.board_id
10               WHERE  (a.G_TIME BETWEEN TO_DATE ('2012-04-17 11:26:11',
11                                                    'yyyy-MM-dd HH24:mi:ss')
12                                        AND  TO_DATE ('2012-04-18 11:26:11',
13                                                     'yyyy-MM-dd HH24:mi:ss'))
14                      AND (a.board_id IN
15                                (SELECT   DISTINCT a.board_id
16                                   FROM   PC_dmsion_site a
17                                  WHERE   a.dmsion_id IN
18                                               (SELECT   a.dimention_id
19                                                  FROM   PC_user_dmsion a
20                                                 WHERE   a.user_id = 5055
21                                                         AND a.channel_type =
22                                                               1)))
23                      AND a.repeat_flag = 0
24            ORDER BY  a.G_TIME DESC)
25   WHERE   ROWNUM < 10;

no rows selected

Elapsed: 00:03:36.18

Execution Plan
----------------------------------------------------------
Plan hash value: 1410016450

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                         | Rows  | Bytes | Cost (%CPU)|Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                              |     9 |  3690 |  614   (8)| 00:00:08 |
|*  1 |  COUNT STOPKEY                   |                              |       |      |           |          |
|   2 |   VIEW                           |                              |    9 |  3690 |   614   (8)|00:00:08 |
|   3 |    NESTED LOOPS OUTER            |                              |     9 |  1026 |  614   (8)| 00:00:08 |
|   4 |     NESTED LOOPS SEMI            |                              |     9 |   702 |  607   (8)| 00:00:08 |
|*  5 |      TABLE ACCESS BY INDEXROWID  | PC_NDCINFO               | 26590 |  1921K|    43  (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN DESCENDING |FK_PC_NDCINFO_GTIME       |    83 |      |     2   (0)| 00:00:01 |
|*  7 |      VIEW                        |VW_NSO_1                      |   145 |   580 |   14   (8)| 00:00:01 |
|*  8 |       HASH JOIN                  |                              |   645 | 12255 |   14   (8)| 00:00:01 |
|*  9 |        TABLE ACCESS BY INDEXROWID| PC_USER_DMSION             |     4 |    44 |    2   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN         | INDEX_PC_USER_DMSION_USERID |    6 |       |    1   (0)| 00:00:01 |
|  11 |        TABLE ACCESS FULL         | PC_DMSION_SITE             | 11352 | 90816 |   11   (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID  | PC_NBSCONF             |     1 |    36 |    1   (0)| 00:00:01 |
|* 13 |      INDEX UNIQUE SCAN           | PK_PC_NBSCONF          |     1 |      |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<10)
   5 - filter("A"."REPEAT_FLAG"=0)
   6 -access("A"."G_TIME">=TO_DATE('2012-04-17 11:26:11','yyyy-mm-dd hh24:mi:ss') AND
             "A"."G_TIME"<=TO_DATE('2012-04-1811:26:11', 'yyyy-mm-dd hh24:mi:ss'))
       filter("A"."G_TIME">=TO_DATE('2012-04-1711:26:11', 'yyyy-mm-dd hh24:mi:ss') AND
             "A"."G_TIME"<=TO_DATE('2012-04-1811:26:11', 'yyyy-mm-dd hh24:mi:ss'))
   7 -filter("A"."BOARD_ID"="$nso_col_1")
   8 - access("A"."DMSION_ID"="A"."DIMENTION_ID")
   9 - filter("A"."CHANNEL_TYPE"=1)
  10 - access("A"."USER_ID"=5055)
  13 -access("NB"."ID"(+)="A"."BOARD_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    2498574  consistent gets
          0  physical reads
          0  redo size
        513  bytes sent via SQL*Net toclient
        458  bytes received via SQL*Netfrom client
          1  SQL*Net roundtrips to/fromclient
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

[ 本帖最后由 stziyan 于 2012-4-18 21:45 编辑 ]

不了峰

执行计划 变了，看能不有加提示改到没有加rownum的那个执行计划

stziyan

sql改不了，加不了hint。问题是为什么会这样呢，之前我一直以为加上rownum速度会很快。

Maclean Liu(刘相兵

加入rownum 后 SQL optimizer优化器会知道 只需要返回少量的结果(COUNT STOPKEY) 这导致 优化器会选择NESTED LOOPS 这种 能够快速返回 result 的 join method 。

但是就 这个SQL的执行 运行效率 可以看到 NESTED LOOPS  反而比 HASH JOIN 要差。

从第一个执行计划看 计划中的 result card是 5978 而 实际 没有返回行 no rows selected， 说明统计信息 不准确 或者缺失。


建议你 重新收集统计信息，  或者 尝试加入hint 。

stziyan

谢啦，重新收集下统计信息，再看看

stziyan

统计信息都存在，我又重新手动收集了一下统计信息，问题仍然存在。

Maclean Liu(刘相兵

尝试 添加 use_hash hint 强制使用 hash join

czmmiao

加了rownum之后走了PK_PC_NBSCONF索引，可以采用下版主的建议，使用use_hash或者尝试下hint FK_PC_NDCINFO_GTIME索引和first_rows