分页查询导致特别费时的问题

Question

哪位大侠遇到过使用四五个表连接后，再进行rownum或者row_number()分页时，速度特慢，大概要2分钟左右才出结果。
单独进行四五个表连接大概5秒之内就能出结果。
哪位知道是什么原因导致的啊？

Answer 1

加上FIRST_ROWS提示看看.
SELECT /*+ FIRST_ROWS */ * FROM ...

Answer 2

使用了/*+ FIRST_ROWS */的确解决了慢的问题，原理是什么啊？
还有其他的方法吗？

Answer 3

看看这个
http://blog.csdn.net/fw0124/article/details/6692022

Answer 4

没有进行分页的语句：大概3秒出结果，一旦加上rownum分页就要很长时间才能查出结果
SELECT GU.GUEST_NAME,
               GU.ADDR_ONE || GU.ADDR_TWO || GU.ADDR_THREE || GU.ADDR_FOUR GUADDR,
               DEV.STB_TYPE,
               SIG.SIGNAL_STRENGTH,
               SIG.TUNER_SNR,
               SIG.SIGNAL_BER,
               CM.UP_CM_SIGNAL_STRENGTH,
               CM.DOWN_CM_SIGNAL_STRENGTH
          FROM (SELECT MAC_ADDR, SIGNAL_STRENGTH, SIGNAL_BER, TUNER_SNR
                  FROM UT_SIGNAL_NEW USIG
                 WHERE TIME = (SELECT MAX(TIME)
                                 FROM UT_SIGNAL_NEW
                                WHERE MAC_ADDR = USIG.MAC_ADDR)) SIG
          FULL JOIN (SELECT MAC_ADDR,
                           DOWN_CM_SIGNAL_STRENGTH,
                           UP_CM_SIGNAL_STRENGTH
                      FROM UT_CM_NEW UCM
                     WHERE TIME = (SELECT MAX(TIME)
                                     FROM UT_CM_NEW
                                    WHERE MAC_ADDR = UCM.MAC_ADDR)) CM ON (UPPER(SIG.MAC_ADDR) =
                                                                          UPPER(CM.MAC_ADDR))
          JOIN (SELECT MAC_ADDR
                 FROM UT_ONOFF_NEW UTNE
                WHERE TIME = (SELECT MAX(TIME)
                                FROM UT_ONOFF_NEW
                               WHERE MAC_ADDR = UTNE.MAC_ADDR)
                  AND TYPE = 0) AA ON (UPPER(SIG.MAC_ADDR) =
                                      UPPER(AA.MAC_ADDR) or
                                      UPPER(AA.MAC_ADDR) =
                                      UPPER(CM.MAC_ADDR))
          left outer JOIN GUEST GU ON (UPPER(GU.MAC_ADDR) =
                                      UPPER(AA.MAC_ADDR))
          LEFT OUTER JOIN (SELECT DISTINCT MAC_ADDR, STB_TYPE
                            FROM PB_DEVICE_INFO) DEV ON (UPPER(GU.MAC_ADDR) =
                                                        UPPER(DEV.MAC_ADDR))
         WHERE (SIGNAL_STRENGTH BETWEEN
               (SELECT MINVALUE
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'SIGNAL_STRENGTH') AND
               (SELECT MAXVALUE
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'SIGNAL_STRENGTH') OR
               SIGNAL_STRENGTH IS NULL)
           AND (SIGNAL_BER BETWEEN
               (SELECT MINVALUE_BER
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'SIGNAL_BER') AND
               (SELECT MAXVALUE_BER
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'SIGNAL_BER') OR SIGNAL_BER IS NULL)
           AND (TUNER_SNR BETWEEN
               (SELECT MINVALUE
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'TUNER_SNR') AND
               (SELECT MAXVALUE
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'TUNER_SNR') OR TUNER_SNR IS NULL)
           AND (DOWN_CM_SIGNAL_STRENGTH BETWEEN
               (SELECT MINVALUE
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'DOWN_CM_STRENGTH') AND
               (SELECT MAXVALUE
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'DOWN_CM_STRENGTH') OR
               DOWN_CM_SIGNAL_STRENGTH IS NULL)
           AND (UP_CM_SIGNAL_STRENGTH BETWEEN
               (SELECT MINVALUE
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'UP_CM_SIGNAL_STRENGTH') AND
               (SELECT MAXVALUE
                   FROM RANGE
                  WHERE TYPE = 1
                    AND NAME = 'UP_CM_SIGNAL_STRENGTH') OR
               UP_CM_SIGNAL_STRENGTH IS NULL)
ORDER BY AA.MAC_ADDR

Answer 5

分页的SQL语句的执行计划：
Execution Plan
----------------------------------------------------------
Plan hash value: 2104735380-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |     9 |  3141 |       | 28083  (74)| 00:05:37 |
|*  1 |  COUNT STOPKEY                         |                |       |       |       |            |          |
|   2 |   VIEW                                 |                |   965M|   313G|       | 28083  (74)| 00:05:37 |
|*  3 |    SORT ORDER BY STOPKEY               |                |   965M|   188G|       | 28083  (74)| 00:05:37 |
|*  4 |     HASH JOIN RIGHT OUTER              |                |   965M|   188G|       | 28083  (74)| 00:05:37 |
|   5 |      VIEW                              |                | 15947 |   280K|       |   222   (2)| 00:00:03 |
|   6 |       HASH UNIQUE                      |                | 15947 |   358K|  1344K|   222   (2)| 00:00:03 |
|   7 |        TABLE ACCESS FULL               | PB_DEVICE_INFO | 26294 |   590K|       |    84   (2)| 00:00:02 |
|*  8 |      HASH JOIN                         |                |  6054K|  1108M|       | 16590  (58)| 00:03:20 |
|   9 |       VIEW                             | VW_SQ_6        |  4486 | 98692 |       |   823   (5)| 00:00:10 |
|  10 |        HASH GROUP BY                   |                |  4486 | 94206 |       |   823   (5)| 00:00:10 |
|  11 |         TABLE ACCESS FULL              | UT_ONOFF_NEW   |   296K|  6086K|       |   794   (2)| 00:00:10 |
|* 12 |       HASH JOIN RIGHT OUTER            |                |   400M|    63G|       | 11091  (43)| 00:02:14 |
|  13 |        TABLE ACCESS FULL               | GUEST          |  2948 |   187K|       |    26   (0)| 00:00:01 |
|  14 |        NESTED LOOPS                    |                |    13M|  1360M|       |  6388   (2)| 00:01:17 |
|* 15 |         VIEW                           |                |  5599 |   442K|       |   182   (6)| 00:00:03 |
|  16 |          UNION-ALL                     |                |       |       |       |            |          |
|* 17 |           HASH JOIN RIGHT OUTER        |                |  5597 |   524K|       |    91   (6)| 00:00:02 |
|  18 |            VIEW                        |                |     2 |    76 |       |     5  (20)| 00:00:01 |
|* 19 |             TABLE ACCESS BY INDEX ROWID| UT_CM_NEW      |     1 |    27 |       |     1   (0)| 00:00:01 |
|  20 |              NESTED LOOPS              |                |     2 |    88 |       |     5  (20)| 00:00:01 |
|  21 |               VIEW                     | VW_SQ_1        |     2 |    34 |       |     4  (25)| 00:00:01 |
|  22 |                HASH GROUP BY           |                |     2 |    42 |       |     4  (25)| 00:00:01 |
|  23 |                 TABLE ACCESS FULL      | UT_CM_NEW      |     5 |   105 |       |     3   (0)| 00:00:01 |
|* 24 |               INDEX RANGE SCAN         | CM_MAC_INDEX   |     3 |       |       |     0   (0)| 00:00:01 |
|* 25 |            HASH JOIN                   |                |  5597 |   317K|       |    85   (4)| 00:00:02 |
|  26 |             VIEW                       | VW_SQ_2        |  5597 |   136K|       |    43   (5)| 00:00:01 |
|  27 |              HASH GROUP BY             |                |  5597 |   114K|       |    43   (5)| 00:00:01 |
|  28 |               TABLE ACCESS FULL        | UT_SIGNAL_NEW  | 10535 |   216K|       |    41   (0)| 00:00:01 |
|  29 |             TABLE ACCESS FULL          | UT_SIGNAL_NEW  | 10535 |   339K|       |    41   (0)| 00:00:01 |
|* 30 |           HASH JOIN ANTI               |                |     2 |   104 |       |    91   (6)| 00:00:02 |
|* 31 |            TABLE ACCESS BY INDEX ROWID | UT_CM_NEW      |     1 |    27 |       |     1   (0)| 00:00:01 |
|  32 |             NESTED LOOPS               |                |     2 |    88 |       |     5  (20)| 00:00:01 |
|  33 |              VIEW                      | VW_SQ_5        |     2 |    34 |       |     4  (25)| 00:00:01 |
|  34 |               HASH GROUP BY            |                |     2 |    42 |       |     4  (25)| 00:00:01 |
|  35 |                TABLE ACCESS FULL       | UT_CM_NEW      |     5 |   105 |       |     3   (0)| 00:00:01 |
|* 36 |              INDEX RANGE SCAN          | CM_MAC_INDEX   |     3 |       |       |     0   (0)| 00:00:01 |
|  37 |            VIEW                        | VW_SQ_4        |  5597 | 44776 |       |    85   (4)| 00:00:02 |
|* 38 |             HASH JOIN                  |                |  5597 |   207K|       |    85   (4)| 00:00:02 |
|  39 |              VIEW                      | VW_SQ_3        |  5597 | 95149 |       |    43   (5)| 00:00:01 |
|  40 |               HASH GROUP BY            |                |  5597 |   114K|       |    43   (5)| 00:00:01 |
|  41 |                TABLE ACCESS FULL       | UT_SIGNAL_NEW  | 10535 |   216K|       |    41   (0)| 00:00:01 |
|  42 |              TABLE ACCESS FULL         | UT_SIGNAL_NEW  | 10535 |   216K|       |    41   (0)| 00:00:01 |
|* 43 |          TABLE ACCESS FULL             | RANGE          |     1 |    22 |       |     2   (0)| 00:00:01 |
|* 44 |           TABLE ACCESS FULL            | RANGE          |     1 |    22 |       |     2   (0)| 00:00:01 |
|* 45 |            TABLE ACCESS FULL           | RANGE          |     1 |    21 |       |     3   (0)| 00:00:01 |
|* 46 |             TABLE ACCESS FULL          | RANGE          |     1 |    21 |       |     3   (0)| 00:00:01 |
|* 47 |              TABLE ACCESS FULL         | RANGE          |     1 |    22 |       |     3   (0)| 00:00:01 |
|* 48 |               TABLE ACCESS FULL        | RANGE          |     1 |    22 |       |     3   (0)| 00:00:01 |
|* 49 |                TABLE ACCESS FULL       | RANGE          |     1 |    22 |       |     3   (0)| 00:00:01 |
|* 50 |                 TABLE ACCESS FULL      | RANGE          |     1 |    22 |       |     3   (0)| 00:00:01 |
|* 51 |                  TABLE ACCESS FULL     | RANGE          |     1 |    22 |       |     3   (0)| 00:00:01 |
|* 52 |                   TABLE ACCESS FULL    | RANGE          |     1 |    22 |       |     3   (0)| 00:00:01 |
|* 53 |         TABLE ACCESS FULL              | UT_ONOFF_NEW   |  2427 | 58248 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Answer 6

Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter(ROWNUM<10)
   3 - filter(ROWNUM<10)
   4 - access(UPPER("GU"."MAC_ADDR")=UPPER("DEV"."MAC_ADDR"(+)))
   8 - access("TIME"="VW_COL_1" AND "MAC_ADDR"="UTNE"."MAC_ADDR")
  12 - access(UPPER("GU"."MAC_ADDR"(+))=UPPER("MAC_ADDR"))
  15 - filter(("from$_subquery$_008"."SIGNAL_STRENGTH">= (SELECT /*+ */ "MINVALUE" FROM "RANGE" "RANGE"
              WHERE "NAME"='SIGNAL_STRENGTH' AND TO_NUMBER("TYPE")=1) AND "from$_subquery$_008"."SIGNAL_STRENGTH"<=
              (SELECT /*+ */ "MAXVALUE" FROM "RANGE" "RANGE" WHERE "NAME"='SIGNAL_STRENGTH' AND TO_NUMBER("TYPE")=1)
              OR "from$_subquery$_008"."SIGNAL_STRENGTH" IS NULL) AND ("from$_subquery$_008"."SIGNAL_BER">= (SELECT
              /*+ */ "MINVALUE_BER" FROM "RANGE" "RANGE" WHERE "NAME"='SIGNAL_BER' AND TO_NUMBER("TYPE")=1) AND
              "from$_subquery$_008"."SIGNAL_BER"<= (SELECT /*+ */ "MAXVALUE_BER" FROM "RANGE" "RANGE" WHERE
              "NAME"='SIGNAL_BER' AND TO_NUMBER("TYPE")=1) OR "from$_subquery$_008"."SIGNAL_BER" IS NULL) AND
              ("from$_subquery$_008"."TUNER_SNR">= (SELECT /*+ */ "MINVALUE" FROM "RANGE" "RANGE" WHERE
              "NAME"='TUNER_SNR' AND TO_NUMBER("TYPE")=1) AND "from$_subquery$_008"."TUNER_SNR"<= (SELECT /*+ */
              "MAXVALUE" FROM "RANGE" "RANGE" WHERE "NAME"='TUNER_SNR' AND TO_NUMBER("TYPE")=1) OR
              "from$_subquery$_008"."TUNER_SNR" IS NULL) AND ("from$_subquery$_008"."DOWN_CM_SIGNAL_STRENGTH">=
              (SELECT /*+ */ "MINVALUE" FROM "RANGE" "RANGE" WHERE "NAME"='DOWN_CM_STRENGTH' AND TO_NUMBER("TYPE")=1)
              AND "from$_subquery$_008"."DOWN_CM_SIGNAL_STRENGTH"<= (SELECT /*+ */ "MAXVALUE" FROM "RANGE" "RANGE"
              WHERE "NAME"='DOWN_CM_STRENGTH' AND TO_NUMBER("TYPE")=1) OR
              "from$_subquery$_008"."DOWN_CM_SIGNAL_STRENGTH" IS NULL) AND
              ("from$_subquery$_008"."UP_CM_SIGNAL_STRENGTH">= (SELECT /*+ */ "MINVALUE" FROM "RANGE" "RANGE" WHERE
              "NAME"='UP_CM_SIGNAL_STRENGTH' AND TO_NUMBER("TYPE")=1) AND
              "from$_subquery$_008"."UP_CM_SIGNAL_STRENGTH"<= (SELECT /*+ */ "MAXVALUE" FROM "RANGE" "RANGE" WHERE
              "NAME"='UP_CM_SIGNAL_STRENGTH' AND TO_NUMBER("TYPE")=1) OR "from$_subquery$_008"."UP_CM_SIGNAL_STRENGTH"
              IS NULL))
  17 - access(UPPER("MAC_ADDR")=UPPER("CM"."MAC_ADDR"(+)))
  19 - filter("TIME"="VW_COL_1")
  24 - access("MAC_ADDR"="UCM"."MAC_ADDR")
  25 - access("TIME"="VW_COL_1" AND "MAC_ADDR"="USIG"."MAC_ADDR")
  30 - access("VW_COL_1"=UPPER("MAC_ADDR"))
  31 - filter("TIME"="VW_COL_1")
  36 - access("MAC_ADDR"="UCM"."MAC_ADDR")
  38 - access("TIME"="VW_COL_1" AND "MAC_ADDR"="USIG"."MAC_ADDR")
  43 - filter("NAME"='SIGNAL_STRENGTH' AND TO_NUMBER("TYPE")=1)
  44 - filter("NAME"='SIGNAL_STRENGTH' AND TO_NUMBER("TYPE")=1)
  45 - filter("NAME"='SIGNAL_BER' AND TO_NUMBER("TYPE")=1)
  46 - filter("NAME"='SIGNAL_BER' AND TO_NUMBER("TYPE")=1)
  47 - filter("NAME"='TUNER_SNR' AND TO_NUMBER("TYPE")=1)
  48 - filter("NAME"='TUNER_SNR' AND TO_NUMBER("TYPE")=1)
  49 - filter("NAME"='DOWN_CM_STRENGTH' AND TO_NUMBER("TYPE")=1)
  50 - filter("NAME"='DOWN_CM_STRENGTH' AND TO_NUMBER("TYPE")=1)
  51 - filter("NAME"='UP_CM_SIGNAL_STRENGTH' AND TO_NUMBER("TYPE")=1)
  52 - filter("NAME"='UP_CM_SIGNAL_STRENGTH' AND TO_NUMBER("TYPE")=1)
  53 - filter("TYPE"=0 AND (UPPER("SIG"."MAC_ADDR")=UPPER("MAC_ADDR") OR
              UPPER("MAC_ADDR")=UPPER("CM"."MAC_ADDR")))