首页 > 代码库 > Oracle rownum影响执行计划

Oracle rownum影响执行计划

2024-07-18 15:27:32   221人阅读

   今天调优一条SQL语句，由于SQL比较复杂，用autotrace很难一眼看出哪里出了问题，直接上10046。

SELECT AB.*
FROM (SELECT A.*, rownum RN
FROM (SELECT *
        from (SELECT DISTINCT (D.DEVICE_ID), F.FUNCTION_LOCATION_ID
                from GG_device D,
                     GG_CLASSIFY_CARD C,
                     GG_function_location F,
                     GG_fl_device L,
                     GG_tech_object_node n,
                     (SELECT N.TECH_OBJECT_ID
                        FROM GG_TECH_OBJECT_NODE N
                       WHERE N.NODE_TYPE = 2
                       START WITH N.TECH_OBJECT_ID = 15773325
                      CONNECT BY PRIOR
                                  N.TECH_OBJECT_NODE_ID = N.PARENT_ID) TAB
               where F.FUNCTION_LOCATION_ID = L.FUNCTION_LOCATION_ID
                 and L.Device_Id = d.device_id
                 and d.classify_id = c.classify_id
                 AND EXISTS
               (SELECT 1
                        FROM GG_TECH_OBJECT_NODE N
                       WHERE N.TECH_OBJECT_ID = D.DEVICE_ID
                         AND N.NODE_TYPE = 2)
                 AND D.CURRENT_STATUS = 0
                 AND D.IS_SHARE_DEVICE = 1
                 AND TAB.TECH_OBJECT_ID = D.DEVICE_ID
                 and n.tech_object_id = f.function_location_id
                 AND F.SITE_ID = 1021
                 AND C.ALIAS_NAME IN (‘A176‘)
              union all
              SELECT DISTINCT (D.DEVICE_ID), F.FUNCTION_LOCATION_ID
                FROM GG_DEVICE D,
                     GG_CLASSIFY_CARD C,
                     GG_FUNCTION_LOCATION F,
                     GG_tech_object_node n,
                     (SELECT N.TECH_OBJECT_ID
                        FROM GG_TECH_OBJECT_NODE N
                       WHERE N.NODE_TYPE = 2
                       START WITH N.TECH_OBJECT_ID = 15773325
                      CONNECT BY PRIOR
                                  N.TECH_OBJECT_NODE_ID = N.PARENT_ID) TAB
               WHERE D.CLASSIFY_ID = C.CLASSIFY_ID
                 AND F.FUNCTION_LOCATION_ID(+) =
                     D.FUNCTION_LOCATION_ID
                 and n.tech_object_id = f.function_location_id
                 AND EXISTS
               (SELECT 1
                        FROM GG_TECH_OBJECT_NODE N
                       WHERE N.TECH_OBJECT_ID = D.DEVICE_ID
                         AND N.NODE_TYPE = 2)
                 AND D.CURRENT_STATUS = 0
                 AND D.IS_SHARE_DEVICE = 0
                 AND TAB.TECH_OBJECT_ID = D.DEVICE_ID
                 AND F.SITE_ID = 1021
                 AND C.ALIAS_NAME IN (‘A176‘))) A
WHERE ROWNUM <= 25) AB

WHERE AB.RN > 0;

已用时间:  00: 00: 05.56

----------------------------------------------------------
Plan hash value: 1124467031
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |    18 | 67248 | 28951   (1)| 00:05:48 |
|*  1 |  VIEW                                   |                           |    18 | 67248 | 28951   (1)| 00:05:48 |
|*  2 |   COUNT STOPKEY                         |                           |       |       |         |     |
|   3 |    VIEW                                 |                           |    18 | 67014 | 28951   (1)| 00:05:48 |
|*  4 |     SORT ORDER BY STOPKEY               |                           |    18 | 67014 | 28951   (1)| 00:05:48 |
|   5 |      VIEW                               |                           |    18 | 67014 | 28950   (1)| 00:05:48 |
|   6 |       UNION-ALL                         |                           |       |       |         |     |
|   7 |        HASH UNIQUE                      |                           |     6 |  2064 | 15146   (1)| 00:03:02 |
|*  8 |         HASH JOIN                       |                           |     6 |  2064 | 15145   (1)| 00:03:02 |
|   9 |          TABLE ACCESS BY INDEX ROWID    | GG_TECH_OBJECT_NODE     |     1 |   102 |     3   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                  |                           |     6 |  2022 |  5842   (1)| 00:01:11 |
|  11 |            NESTED LOOPS SEMI            |                           |     5 |  1175 |  5827   (1)| 00:01:10 |
|  12 |             NESTED LOOPS                |                           |     5 |  1125 |  5817   (1)| 00:01:10 |
|* 13 |              HASH JOIN                  |                           |   172 | 32508 |  5645   (1)| 00:01:08 |
|* 14 |               HASH JOIN                 |                           |    77 | 13629 |  5601   (1)| 00:01:08 |
|* 15 |                TABLE ACCESS FULL        | GG_CLASSIFY_CARD         |     1 |    93 |    16   (0)| 00:00:01 |
|* 16 |                TABLE ACCESS FULL        | GG_DEVICE               | 22527 |  1847K|  5584   (1)| 00:01:08 |
|  17 |               TABLE ACCESS FULL         | GG_FL_DEVICE            | 74829 |   876K|    43   (3)| 00:00:01 |
|* 18 |              TABLE ACCESS BY INDEX ROWID| GG_FUNCTION_LOCATION    |     1 |    36 |     1   (0)| 00:00:01 |
|* 19 |               INDEX UNIQUE SCAN         | PK_GG_FUNCTION_LOCATION |     1 |       |     0   (0)| 00:00:01 |
|* 20 |             INDEX RANGE SCAN            | IDX_TECH_NODE_ID          |   482K|  4712K|     2   (0)| 00:00:01 |
|* 21 |            INDEX RANGE SCAN             | IDX_TECH_OBJECT_ID        |     1 |       |     2   (0)| 00:00:01 |
|  22 |          VIEW                           |                           |  1762K|    11M|  9291   (1)| 00:01:52 |
|* 23 |           FILTER                        |                           |       |       |         |     |
|* 24 |            CONNECT BY WITH FILTERING    |                           |       |       |         |     |
|  25 |             TABLE ACCESS BY INDEX ROWID | GG_TECH_OBJECT_NODE     |       |       |         |     |
|* 26 |              INDEX RANGE SCAN           | IDX_TECH_OBJECT_ID        |     1 |     7 |     3   (0)| 00:00:01 |
|* 27 |             HASH JOIN                   |                           |       |       |         |     |
|  28 |              CONNECT BY PUMP            |                           |       |       |         |     |
|  29 |              TABLE ACCESS FULL          | GG_TECH_OBJECT_NODE     |  1762K|    40M|  9291   (1)| 00:01:52 |
|  30 |             TABLE ACCESS FULL           | GG_TECH_OBJECT_NODE     |  1762K|    40M|  9291   (1)| 00:01:52 |
|  31 |        HASH UNIQUE                      |                           |    12 |  4056 | 13804   (1)| 00:02:46 |
|* 32 |         HASH JOIN                       |                           |    12 |  4056 | 13803   (1)| 00:02:46 |
|  33 |          TABLE ACCESS BY INDEX ROWID    | GG_TECH_OBJECT_NODE     |     1 |   102 |     3   (0)| 00:00:01 |
|  34 |           NESTED LOOPS                  |                           |    11 |  3641 |  4501   (1)| 00:00:55 |
|  35 |            NESTED LOOPS SEMI            |                           |    10 |  2290 |  4471   (1)| 00:00:54 |
|* 36 |             HASH JOIN                   |                           |    10 |  2190 |  4451   (1)| 00:00:54 |
|* 37 |              TABLE ACCESS FULL          | GG_CLASSIFY_CARD         |     1 |    93 |    16   (0)| 00:00:01 |
|* 38 |              TABLE ACCESS BY INDEX ROWID| GG_DEVICE               |     4 |   360 |     5   (0)| 00:00:01 |
|  39 |               NESTED LOOPS              |                           |  2823 |   347K|  4434   (1)| 00:00:54 |
|* 40 |                TABLE ACCESS FULL        | GG_FUNCTION_LOCATION    |   685 | 24660 |  2214   (1)| 00:00:27 |
|* 41 |                INDEX RANGE SCAN         | IDX_FLOCID                |     4 |       |     2   (0)| 00:00:01 |
|* 42 |             INDEX RANGE SCAN            | IDX_TECH_NODE_ID          |   482K|  4712K|     2   (0)| 00:00:01 |
|* 43 |            INDEX RANGE SCAN             | IDX_TECH_OBJECT_ID        |     1 |       |     2   (0)| 00:00:01 |
|  44 |          VIEW                           |                           |  1762K|    11M|  9291   (1)| 00:01:52 |
|* 45 |           FILTER                        |                           |       |       |         |     |
|* 46 |            CONNECT BY WITH FILTERING    |                           |       |       |         |     |
|  47 |             TABLE ACCESS BY INDEX ROWID | GG_TECH_OBJECT_NODE     |       |       |         |     |
|* 48 |              INDEX RANGE SCAN           | IDX_TECH_OBJECT_ID        |     1 |     7 |     3   (0)| 00:00:01 |
|* 49 |             HASH JOIN                   |                           |       |       |         |     |
|  50 |              CONNECT BY PUMP            |                           |       |       |         |     |
|  51 |              TABLE ACCESS FULL          | GG_TECH_OBJECT_NODE     |  1762K|    40M|  9291   (1)| 00:01:52 |
|  52 |             TABLE ACCESS FULL           | GG_TECH_OBJECT_NODE     |  1762K|    40M|  9291   (1)| 00:01:52 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("AB"."RN">0)
   2 - filter(ROWNUM<=25)
   4 - filter(ROWNUM<=25)
   8 - access("TAB"."TECH_OBJECT_ID"="D"."DEVICE_ID")
  13 - access("L"."DEVICE_ID"="D"."DEVICE_ID")
  14 - access("D"."CLASSIFY_ID"="C"."CLASSIFY_ID")
  15 - filter("C"."ALIAS_NAME"=‘A176‘)
  16 - filter("D"."IS_SHARE_DEVICE"=1 AND "D"."CURRENT_STATUS"=0)
  18 - filter("F"."SITE_ID"=1021)
  19 - access("F"."FUNCTION_LOCATION_ID"="L"."FUNCTION_LOCATION_ID")
  20 - access("N"."TECH_OBJECT_ID"="D"."DEVICE_ID" AND "N"."NODE_TYPE"=2)
  21 - access("N"."TECH_OBJECT_ID"="F"."FUNCTION_LOCATION_ID")
  23 - filter("N"."NODE_TYPE"=2)
  24 - filter("N"."TECH_OBJECT_ID"=15773325)
  26 - access("N"."TECH_OBJECT_ID"=15773325)
  27 - access("N"."PARENT_ID"=NULL)
  32 - access("TAB"."TECH_OBJECT_ID"="D"."DEVICE_ID")
  36 - access("D"."CLASSIFY_ID"="C"."CLASSIFY_ID")
  37 - filter("C"."ALIAS_NAME"=‘A176‘)
  38 - filter("D"."IS_SHARE_DEVICE"=0 AND "D"."CURRENT_STATUS"=0)
  40 - filter("F"."SITE_ID"=1021)
  41 - access("F"."FUNCTION_LOCATION_ID"="D"."FUNCTION_LOCATION_ID")
  42 - access("N"."TECH_OBJECT_ID"="D"."DEVICE_ID" AND "N"."NODE_TYPE"=2)
  43 - access("N"."TECH_OBJECT_ID"="F"."FUNCTION_LOCATION_ID")
  45 - filter("N"."NODE_TYPE"=2)
  46 - filter("N"."TECH_OBJECT_ID"=15773325)
  48 - access("N"."TECH_OBJECT_ID"=15773325)
  49 - access("N"."PARENT_ID"=NULL)
统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
     209163  consistent gets
          0  physical reads
          0  redo size
       2890  bytes sent via SQL*Net to client
      10811  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         63  sorts (memory)
          0  sorts (disk)
          1  rows processed

10046 trace的结果，可以一眼看出是递归出了问题，按照业务上来说一个节点下面没有多少数据啊，怎么会不走索引呢？

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  VIEW  (cr=209038 pr=0 pw=0 time=5665797 us)
      1   COUNT STOPKEY (cr=209038 pr=0 pw=0 time=5665774 us)
      1    VIEW  (cr=209038 pr=0 pw=0 time=5665768 us)
      1     SORT ORDER BY STOPKEY (cr=209038 pr=0 pw=0 time=5665745 us)
      1      VIEW  (cr=209038 pr=0 pw=0 time=5665649 us)
      1       UNION-ALL  (cr=209038 pr=0 pw=0 time=5665631 us)
      0        HASH UNIQUE (cr=25205 pr=0 pw=0 time=570538 us)
      0         HASH JOIN  (cr=25205 pr=0 pw=0 time=570360 us)
      0          TABLE ACCESS BY INDEX ROWID GG_TECH_OBJECT_NODE (cr=25205 pr=0 pw=0 time=570095 us)
      1           NESTED LOOPS  (cr=25205 pr=0 pw=0 time=570075 us)
      0            NESTED LOOPS SEMI (cr=25205 pr=0 pw=0 time=570069 us)
      0             NESTED LOOPS  (cr=25205 pr=0 pw=0 time=570066 us)
      0              HASH JOIN  (cr=25205 pr=0 pw=0 time=570062 us)
      0               HASH JOIN  (cr=25205 pr=0 pw=0 time=569617 us)
      1                TABLE ACCESS FULL GG_CLASSIFY_CARD (cr=68 pr=0 pw=0 time=1467 us)
  21206                TABLE ACCESS FULL GG_DEVICE (cr=25137 pr=0 pw=0 time=424214 us)
      0               TABLE ACCESS FULL GG_FL_DEVICE (cr=0 pr=0 pw=0 time=0 us)
      0              TABLE ACCESS BY INDEX ROWID GG_FUNCTION_LOCATION (cr=0 pr=0 pw=0 time=0 us)
      0               INDEX UNIQUE SCAN PK_GG_FUNCTION_LOCATION (cr=0 pr=0 pw=0 time=0 us)(object id 508068)
      0             INDEX RANGE SCAN IDX_TECH_NODE_ID (cr=0 pr=0 pw=0 time=0 us)(object id 541613)
      0            INDEX RANGE SCAN IDX_TECH_OBJECT_ID (cr=0 pr=0 pw=0 time=0 us)(object id 508645)
      0          VIEW  (cr=0 pr=0 pw=0 time=0 us)
      0           FILTER  (cr=0 pr=0 pw=0 time=0 us)
      0            CONNECT BY WITH FILTERING (cr=0 pr=0 pw=0 time=0 us)
      0             TABLE ACCESS BY INDEX ROWID GG_TECH_OBJECT_NODE (cr=0 pr=0 pw=0 time=0 us)
      0              INDEX RANGE SCAN IDX_TECH_OBJECT_ID (cr=0 pr=0 pw=0 time=0 us)(object id 508645)
      0             HASH JOIN  (cr=0 pr=0 pw=0 time=0 us)
      0              CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=0 us)
      0              TABLE ACCESS FULL GG_TECH_OBJECT_NODE (cr=0 pr=0 pw=0 time=0 us)
      0             TABLE ACCESS FULL GG_TECH_OBJECT_NODE (cr=0 pr=0 pw=0 time=0 us)
      1        HASH UNIQUE (cr=183833 pr=0 pw=0 time=5095035 us)
      1         HASH JOIN  (cr=183748 pr=0 pw=0 time=5090111 us)
    170          TABLE ACCESS BY INDEX ROWID GG_TECH_OBJECT_NODE (cr=15772 pr=0 pw=0 time=7653 us)
    341           NESTED LOOPS  (cr=15610 pr=0 pw=0 time=189743 us)
    170            NESTED LOOPS SEMI (cr=15268 pr=0 pw=0 time=5170 us)
    170             HASH JOIN  (cr=14926 pr=0 pw=0 time=3232 us)
      1              TABLE ACCESS FULL GG_CLASSIFY_CARD (cr=68 pr=0 pw=0 time=830 us)
   2867              TABLE ACCESS BY INDEX ROWID GG_DEVICE (cr=14858 pr=0 pw=0 time=28976 us)
   4728               NESTED LOOPS  (cr=13282 pr=0 pw=0 time=94565 us)
   1667                TABLE ACCESS FULL GG_FUNCTION_LOCATION (cr=9937 pr=0 pw=0 time=13539 us)
   3060                INDEX RANGE SCAN IDX_FLOCID (cr=3345 pr=0 pw=0 time=12458 us)(object id 507929)
    170             INDEX RANGE SCAN IDX_TECH_NODE_ID (cr=342 pr=0 pw=0 time=3171 us)(object id 541613)
    170            INDEX RANGE SCAN IDX_TECH_OBJECT_ID (cr=342 pr=0 pw=0 time=1854 us)(object id 508645)
     31          VIEW  (cr=167976 pr=0 pw=0 time=4864861 us)
     31           FILTER  (cr=167976 pr=0 pw=0 time=4864794 us)
     56            CONNECT BY WITH FILTERING (cr=167976 pr=0 pw=0 time=4865653 us)
      1             TABLE ACCESS BY INDEX ROWID GG_TECH_OBJECT_NODE (cr=4 pr=0 pw=0 time=58 us)
      1              INDEX RANGE SCAN IDX_TECH_OBJECT_ID (cr=3 pr=0 pw=0 time=25 us)(object id 508645)
     55             HASH JOIN  (cr=167972 pr=0 pw=0 time=1264029 us)
     56              CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=56 us)
7048956              TABLE ACCESS FULL GG_TECH_OBJECT_NODE (cr=167972 pr=0 pw=0 time=488 us)
      0             TABLE ACCESS FULL GG_TECH_OBJECT_NODE (cr=0 pr=0 pw=0 time=0 us)          

  

    在无意中测试发现，去掉分页的语句之后，就非常快了，揣测是COUNT STOPKEY造成的，如果数据量大的做分页，肯定是可以提升性能的，但此条SQL语句只是返回一条数据。我的结论是rownum可以改变执行计划。       

SQL> SELECT *
from (SELECT DISTINCT (D.DEVICE_ID), F.FUNCTION_LOCATION_ID
      from GG_device D,
           GG_CLASSIFY_CARD C,
           GG_function_location F,
           GG_fl_device L,
           GG_tech_object_node n,
           (SELECT N.TECH_OBJECT_ID
              FROM GG_TECH_OBJECT_NODE N
             WHERE N.NODE_TYPE = 2
             START WITH N.TECH_OBJECT_ID = 15773325
            CONNECT BY PRIOR N.TECH_OBJECT_NODE_ID = N.PARENT_ID) TAB
     where F.FUNCTION_LOCATION_ID = L.FUNCTION_LOCATION_ID
       and L.Device_Id = d.device_id
       and d.classify_id = c.classify_id
       AND EXISTS (SELECT 1
              FROM GG_TECH_OBJECT_NODE N
             WHERE N.TECH_OBJECT_ID = D.DEVICE_ID
               AND N.NODE_TYPE = 2)
       AND D.CURRENT_STATUS = 0
       AND D.IS_SHARE_DEVICE = 1
       AND TAB.TECH_OBJECT_ID = D.DEVICE_ID
       and n.tech_object_id = f.function_location_id
       AND F.SITE_ID = 1021
       AND C.ALIAS_NAME IN (‘A176‘)
    union all
    SELECT DISTINCT (D.DEVICE_ID), F.FUNCTION_LOCATION_ID
      FROM GG_DEVICE D,
           GG_CLASSIFY_CARD C,
           GG_FUNCTION_LOCATION F,
           GG_tech_object_node n,
           (SELECT N.TECH_OBJECT_ID
              FROM GG_TECH_OBJECT_NODE N
             WHERE N.NODE_TYPE = 2
             START WITH N.TECH_OBJECT_ID = 15773325
            CONNECT BY PRIOR N.TECH_OBJECT_NODE_ID = N.PARENT_ID) TAB
     WHERE D.CLASSIFY_ID = C.CLASSIFY_ID
       AND F.FUNCTION_LOCATION_ID(+) = D.FUNCTION_LOCATION_ID
       and n.tech_object_id = f.function_location_id
       AND EXISTS (SELECT 1
              FROM GG_TECH_OBJECT_NODE N
             WHERE N.TECH_OBJECT_ID = D.DEVICE_ID
               AND N.NODE_TYPE = 2)
       AND D.CURRENT_STATUS = 0
       AND D.IS_SHARE_DEVICE = 0
       AND TAB.TECH_OBJECT_ID = D.DEVICE_ID
       AND F.SITE_ID = 1021
       AND C.ALIAS_NAME IN (‘A176‘));
已用时间:  00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 1345020195
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                            |     2 |  7446 |    41   (5)| 00:00:01 |
|   1 |  VIEW                                     |                            |     2 |  7446 |    41   (5)| 00:00:01 |
|   2 |   UNION-ALL                               |                            |       |       |         |             |
|   3 |    HASH UNIQUE                            |                            |     1 |   345 |    21   (5)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID           | GG_TECH_OBJECT_NODE      |     1 |   103 |     3   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                         |                            |     1 |   345 |    20   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                        |                            |     1 |   242 |    17   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                       |                            |     1 |   206 |    16   (0)| 00:00:01 |
|   8 |         NESTED LOOPS SEMI                 |                            |     1 |   194 |    15   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                     |                            |     1 |   184 |    13   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                    |                            |     3 |   273 |    10   (0)| 00:00:01 |
|  11 |            VIEW                           |                            |     3 |    21 |     6   (0)| 00:00:01 |
|* 12 |             FILTER                        |                            |       |       |         |             |
|* 13 |              CONNECT BY WITH FILTERING    |                            |       |       |         |             |
|  14 |               TABLE ACCESS BY INDEX ROWID | GG_TECH_OBJECT_NODE      |       |       |         |             |
|* 15 |                INDEX RANGE SCAN           | IDX_TECH_OBJECT_ID1        |     1 |     7 |     3   (0)| 00:00:01 |
|  16 |               NESTED LOOPS                |                            |       |       |         |             |
|  17 |                BUFFER SORT                |                            |       |       |         |             |
|  18 |                 CONNECT BY PUMP           |                            |       |       |         |             |
|  19 |                TABLE ACCESS BY INDEX ROWID| GG_TECH_OBJECT_NODE      |     3 |    72 |     6   (0)| 00:00:01 |
|* 20 |                 INDEX RANGE SCAN          | IDX_TECH_OBJECT_PARENT_ID1 |     3 |       |     3   (0)| 00:00:01 |
|* 21 |               TABLE ACCESS FULL           | GG_TECH_OBJECT_NODE      |     3 |    72 |     6   (0)| 00:00:01 |
|* 22 |            TABLE ACCESS BY INDEX ROWID    | GG_DEVICE                |     1 |    84 |     2   (0)| 00:00:01 |
|* 23 |             INDEX UNIQUE SCAN             | PK_GG_DEVICE             |     1 |       |     1   (0)| 00:00:01 |
|* 24 |           TABLE ACCESS BY INDEX ROWID     | GG_CLASSIFY_CARD          |     1 |    93 |     1   (0)| 00:00:01 |
|* 25 |            INDEX UNIQUE SCAN              | SYS_C00468549              |     1 |       |     0   (0)| 00:00:01 |
|* 26 |          INDEX RANGE SCAN                 | IDX_TECH_NODE_ID1          |   587K|  5736K|     2   (0)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN                  | PK_GG_FL_DEVICE          |     2 |    24 |     1   (0)| 00:00:01 |
|* 28 |        TABLE ACCESS BY INDEX ROWID        | GG_FUNCTION_LOCATION     |     1 |    36 |     1   (0)| 00:00:01 |
|* 29 |         INDEX UNIQUE SCAN                 | PK_GG_FUNCTION_LOCATION  |     1 |       |     0   (0)| 00:00:01 |
|* 30 |       INDEX RANGE SCAN                    | IDX_TECH_OBJECT_ID1        |     1 |       |     2   (0)| 00:00:01 |
|  31 |    HASH UNIQUE                            |                            |     1 |   339 |    20   (5)| 00:00:01 |
|  32 |     TABLE ACCESS BY INDEX ROWID           | GG_TECH_OBJECT_NODE      |     1 |   103 |     3   (0)| 00:00:01 |
|  33 |      NESTED LOOPS                         |                            |     1 |   339 |    19   (0)| 00:00:01 |
|  34 |       NESTED LOOPS SEMI                   |                            |     1 |   236 |    16   (0)| 00:00:01 |
|  35 |        NESTED LOOPS                       |                            |     1 |   226 |    14   (0)| 00:00:01 |
|  36 |         NESTED LOOPS                      |                            |     1 |   133 |    13   (0)| 00:00:01 |
|  37 |          NESTED LOOPS                     |                            |     3 |   291 |    10   (0)| 00:00:01 |
|  38 |           VIEW                            |                            |     3 |    21 |     6   (0)| 00:00:01 |
|* 39 |            FILTER                         |                            |       |       |         |             |
|* 40 |             CONNECT BY WITH FILTERING     |                            |       |       |         |             |
|  41 |              TABLE ACCESS BY INDEX ROWID  | GG_TECH_OBJECT_NODE      |       |       |         |             |
|* 42 |               INDEX RANGE SCAN            | IDX_TECH_OBJECT_ID1        |     1 |     7 |     3   (0)| 00:00:01 |
|  43 |              NESTED LOOPS                 |                            |       |       |         |             |
|  44 |               BUFFER SORT                 |                            |       |       |         |             |
|  45 |                CONNECT BY PUMP            |                            |       |       |         |             |
|  46 |               TABLE ACCESS BY INDEX ROWID | GG_TECH_OBJECT_NODE      |     3 |    72 |     6   (0)| 00:00:01 |
|* 47 |                INDEX RANGE SCAN           | IDX_TECH_OBJECT_PARENT_ID1 |     3 |       |     3   (0)| 00:00:01 |
|* 48 |              TABLE ACCESS FULL            | GG_TECH_OBJECT_NODE      |     3 |    72 |     6   (0)| 00:00:01 |
|* 49 |           TABLE ACCESS BY INDEX ROWID     | GG_DEVICE                |     1 |    90 |     2   (0)| 00:00:01 |
|* 50 |            INDEX UNIQUE SCAN              | PK_GG_DEVICE             |     1 |       |     1   (0)| 00:00:01 |
|* 51 |          TABLE ACCESS BY INDEX ROWID      | GG_FUNCTION_LOCATION     |     1 |    36 |     1   (0)| 00:00:01 |
|* 52 |           INDEX UNIQUE SCAN               | PK_GG_FUNCTION_LOCATION  |     1 |       |     0   (0)| 00:00:01 |
|* 53 |         TABLE ACCESS BY INDEX ROWID       | GG_CLASSIFY_CARD          |     1 |    93 |     1   (0)| 00:00:01 |
|* 54 |          INDEX UNIQUE SCAN                | SYS_C00468549              |     1 |       |     0   (0)| 00:00:01 |
|* 55 |        INDEX RANGE SCAN                   | IDX_TECH_NODE_ID1          |   587K|  5736K|     2   (0)| 00:00:01 |
|* 56 |       INDEX RANGE SCAN                    | IDX_TECH_OBJECT_ID1        |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  12 - filter("N"."NODE_TYPE"=2)
  13 - filter("N"."TECH_OBJECT_ID"=15773325)
  15 - access("N"."TECH_OBJECT_ID"=15773325)
  20 - access("N"."PARENT_ID"=NULL)
  21 - access("N"."PARENT_ID"=NULL)
  22 - filter("D"."IS_SHARE_DEVICE"=1 AND "D"."CURRENT_STATUS"=0)
  23 - access("TAB"."TECH_OBJECT_ID"="D"."DEVICE_ID")
  24 - filter("C"."ALIAS_NAME"=‘A176‘)
  25 - access("D"."CLASSIFY_ID"="C"."CLASSIFY_ID")
  26 - access("N"."TECH_OBJECT_ID"="D"."DEVICE_ID" AND "N"."NODE_TYPE"=2)
  27 - access("L"."DEVICE_ID"="D"."DEVICE_ID")
  28 - filter("F"."SITE_ID"=1021)
  29 - access("F"."FUNCTION_LOCATION_ID"="L"."FUNCTION_LOCATION_ID")
  30 - access("N"."TECH_OBJECT_ID"="F"."FUNCTION_LOCATION_ID")
  39 - filter("N"."NODE_TYPE"=2)
  40 - filter("N"."TECH_OBJECT_ID"=15773325)
  42 - access("N"."TECH_OBJECT_ID"=15773325)
  47 - access("N"."PARENT_ID"=NULL)
  48 - access("N"."PARENT_ID"=NULL)
  49 - filter("D"."IS_SHARE_DEVICE"=0 AND "D"."CURRENT_STATUS"=0)
  50 - access("TAB"."TECH_OBJECT_ID"="D"."DEVICE_ID")
  51 - filter("F"."SITE_ID"=1021)
  52 - access("F"."FUNCTION_LOCATION_ID"="D"."FUNCTION_LOCATION_ID")
  53 - filter("C"."ALIAS_NAME"=‘A176‘)
  54 - access("D"."CLASSIFY_ID"="C"."CLASSIFY_ID")
  55 - access("N"."TECH_OBJECT_ID"="D"."DEVICE_ID" AND "N"."NODE_TYPE"=2)
  56 - access("N"."TECH_OBJECT_ID"="F"."FUNCTION_LOCATION_ID")
统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        738  consistent gets
          0  physical reads
          0  redo size
       2606  bytes sent via SQL*Net to client
      10273  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
          1  rows processed

   用另一种方式实现分页。
SELECT AB.*
  FROM  (SELECT tt.*,row_number()over(ORDER BY SORT_NO, name) nr
                   from (SELECT DISTINCT (D.DEVICE_ID),
                                         D.NAME,
                                         N.Full_Path SORT_NO,
                                         F.FUNCTION_LOCATION_ID
                           from GG_device D,
                                GG_CLASSIFY_CARD C,
                                GG_function_location F,
                                GG_fl_device L,
                                GG_tech_object_node n,
                                (SELECT N.TECH_OBJECT_ID
                                   FROM GG_TECH_OBJECT_NODE N
                                  WHERE N.NODE_TYPE = 2
                                  START WITH N.TECH_OBJECT_ID = 15773325
                                 CONNECT BY PRIOR
                                             N.TECH_OBJECT_NODE_ID = N.PARENT_ID) TAB
                          where F.FUNCTION_LOCATION_ID =
                                L.FUNCTION_LOCATION_ID
                            and L.Device_Id = d.device_id
                            and d.classify_id = c.classify_id
                            AND EXISTS
                          (SELECT 1
                                   FROM GG_TECH_OBJECT_NODE N
                                  WHERE N.TECH_OBJECT_ID = D.DEVICE_ID
                                    AND N.NODE_TYPE = 2)
                            AND D.CURRENT_STATUS = 0
                            AND D.IS_SHARE_DEVICE = 1
                            AND TAB.TECH_OBJECT_ID = D.DEVICE_ID
                            and n.tech_object_id = f.function_location_id
                            AND F.SITE_ID = 1021
                            AND C.ALIAS_NAME IN (‘A176‘)
                         union all
                         SELECT DISTINCT (D.DEVICE_ID),
                                         D.NAME,
                                         N.Full_Path SORT_NO,
                                         F.FUNCTION_LOCATION_ID
                           FROM GG_DEVICE D,
                                GG_CLASSIFY_CARD C,
                                GG_FUNCTION_LOCATION F,
                                GG_tech_object_node n,
                                (SELECT N.TECH_OBJECT_ID
                                   FROM GG_TECH_OBJECT_NODE N
                                  WHERE N.NODE_TYPE = 2
                                  START WITH N.TECH_OBJECT_ID = 15773325
                                 CONNECT BY PRIOR
                                             N.TECH_OBJECT_NODE_ID = N.PARENT_ID) TAB
                          WHERE D.CLASSIFY_ID = C.CLASSIFY_ID
                            AND F.FUNCTION_LOCATION_ID(+) =
                                D.FUNCTION_LOCATION_ID
                            and n.tech_object_id = f.function_location_id
                            AND EXISTS
                          (SELECT 1
                                   FROM GG_TECH_OBJECT_NODE N
                                  WHERE N.TECH_OBJECT_ID = D.DEVICE_ID
                                    AND N.NODE_TYPE = 2)
                            AND D.CURRENT_STATUS = 0
                            AND D.IS_SHARE_DEVICE = 0
                            AND TAB.TECH_OBJECT_ID = D.DEVICE_ID
                            AND F.SITE_ID = 1021
                            AND C.ALIAS_NAME IN (‘A176‘)) tt
                   ) AB where ab.nr between 1 and 25;
  
已用时间:  00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 3880620066
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                            |     2 |  7472 |    42   (8)| 00:00:01 |
|*  1 |  VIEW                                       |                            |     2 |  7472 |    42   (8)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK                   |                            |     2 |  7446 |    42   (8)| 00:00:01 |
|   3 |    VIEW                                     |                            |     2 |  7446 |    41   (5)| 00:00:01 |
|   4 |     UNION-ALL                               |                            |       |       |         |     |
|   5 |      HASH UNIQUE                            |                            |     1 |   345 |    21   (5)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID           | GG_TECH_OBJECT_NODE      |     1 |   103 |     3   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                         |                            |     1 |   345 |    20   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                        |                            |     1 |   242 |    17   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                       |                            |     1 |   206 |    16   (0)| 00:00:01 |
|  10 |           NESTED LOOPS SEMI                 |                            |     1 |   194 |    15   (0)| 00:00:01 |
|  11 |            NESTED LOOPS                     |                            |     1 |   254 |    13   (0)| 00:00:01 |
|  12 |             NESTED LOOPS                    |                            |     3 |   273 |    10   (0)| 00:00:01 |
|  13 |              VIEW                           |                            |     3 |    21 |     6   (0)| 00:00:01 |
|* 14 |               FILTER                        |                            |       |       |         |     |
|* 15 |                CONNECT BY WITH FILTERING    |                            |       |       |         |     |
|  16 |                 TABLE ACCESS BY INDEX ROWID | GG_TECH_OBJECT_NODE      |       |       |         |     |
|* 17 |                  INDEX RANGE SCAN           | IDX_TECH_OBJECT_ID1        |     1 |     7 |     3   (0)| 00:00:01 |
|  25 |                 NESTED LOOPS                |                            |       |       |         |     |
|  19 |                  BUFFER SORT                |                            |       |       |         |     |
|  20 |                   CONNECT BY PUMP           |                            |       |       |         |     |
|  21 |                  TABLE ACCESS BY INDEX ROWID| GG_TECH_OBJECT_NODE      |     3 |    72 |     6   (0)| 00:00:01 |
|* 22 |                   INDEX RANGE SCAN          | IDX_TECH_OBJECT_PARENT_ID1 |     3 |       |     3   (0)| 00:00:01 |
|* 23 |                 TABLE ACCESS FULL           | GG_TECH_OBJECT_NODE      |     3 |    72 |     6   (0)| 00:00:01 |
|* 24 |              TABLE ACCESS BY INDEX ROWID    | GG_DEVICE                |     1 |    84 |     2   (0)| 00:00:01 |
|* 25 |               INDEX UNIQUE SCAN             | PK_GG_DEVICE             |     1 |       |     1   (0)| 00:00:01 |
|* 26 |             TABLE ACCESS BY INDEX ROWID     | GG_CLASSIFY_CARD          |     1 |    93 |     1   (0)| 00:00:01 |
|* 27 |              INDEX UNIQUE SCAN              | SYS_C00468549              |     1 |       |     0   (0)| 00:00:01 |
|* 28 |            INDEX RANGE SCAN                 | IDX_TECH_NODE_ID1          |   587K|  5736K|     2   (0)| 00:00:01 |
|* 29 |           INDEX RANGE SCAN                  | PK_GG_FL_DEVICE          |     2 |    24 |     1   (0)| 00:00:01 |
|* 30 |          TABLE ACCESS BY INDEX ROWID        | GG_FUNCTION_LOCATION     |     1 |    36 |     1   (0)| 00:00:01 |
|* 31 |           INDEX UNIQUE SCAN                 | PK_GG_FUNCTION_LOCATION  |     1 |       |     0   (0)| 00:00:01 |
|* 32 |         INDEX RANGE SCAN                    | IDX_TECH_OBJECT_ID1        |     1 |       |     2   (0)| 00:00:01 |
|  33 |      HASH UNIQUE                            |                            |     1 |   339 |    20   (5)| 00:00:01 |
|  34 |       TABLE ACCESS BY INDEX ROWID           | GG_TECH_OBJECT_NODE      |     1 |   103 |     3   (0)| 00:00:01 |
|  35 |        NESTED LOOPS                         |                            |     1 |   339 |    19   (0)| 00:00:01 |
|  36 |         NESTED LOOPS SEMI                   |                            |     1 |   236 |    16   (0)| 00:00:01 |
|  37 |          NESTED LOOPS                       |                            |     1 |   226 |    14   (0)| 00:00:01 |
|  38 |           NESTED LOOPS                      |                            |     1 |   133 |    13   (0)| 00:00:01 |
|  39 |            NESTED LOOPS                     |                            |     3 |   291 |    10   (0)| 00:00:01 |
|  40 |             VIEW                            |                            |     3 |    21 |     6   (0)| 00:00:01 |
|* 41 |              FILTER                         |                            |       |       |         |     |
|* 42 |               CONNECT BY WITH FILTERING     |                            |       |       |         |     |
|  43 |                TABLE ACCESS BY INDEX ROWID  | GG_TECH_OBJECT_NODE      |       |       |         |     |
|* 44 |                 INDEX RANGE SCAN            | IDX_TECH_OBJECT_ID1        |     1 |     7 |     3   (0)| 00:00:01 |
|  45 |                NESTED LOOPS                 |                            |       |       |         |     |
|  46 |                 BUFFER SORT                 |                            |       |       |         |     |
|  47 |                  CONNECT BY PUMP            |                            |       |       |         |     |
|  48 |                 TABLE ACCESS BY INDEX ROWID | GG_TECH_OBJECT_NODE      |     3 |    72 |     6   (0)| 00:00:01 |
|* 49 |                  INDEX RANGE SCAN           | IDX_TECH_OBJECT_PARENT_ID1 |     3 |       |     3   (0)| 00:00:01 |
|* 50 |                TABLE ACCESS FULL            | GG_TECH_OBJECT_NODE      |     3 |    72 |     6   (0)| 00:00:01 |
|* 51 |             TABLE ACCESS BY INDEX ROWID     | GG_DEVICE                |     1 |    90 |     2   (0)| 00:00:01 |
|* 52 |              INDEX UNIQUE SCAN              | PK_GG_DEVICE             |     1 |       |     1   (0)| 00:00:01 |
|* 53 |            TABLE ACCESS BY INDEX ROWID      | GG_FUNCTION_LOCATION     |     1 |    36 |     1   (0)| 00:00:01 |
|* 54 |             INDEX UNIQUE SCAN               | PK_GG_FUNCTION_LOCATION  |     1 |       |     0   (0)| 00:00:01 |
|* 55 |           TABLE ACCESS BY INDEX ROWID       | GG_CLASSIFY_CARD          |     1 |    93 |     1   (0)| 00:00:01 |
|* 56 |            INDEX UNIQUE SCAN                | SYS_C00468549              |     1 |       |     0   (0)| 00:00:01 |
|* 57 |          INDEX RANGE SCAN                   | IDX_TECH_NODE_ID1          |   587K|  5736K|     2   (0)| 00:00:01 |
|* 58 |         INDEX RANGE SCAN                    | IDX_TECH_OBJECT_ID1        |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------


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


   1 - filter("AB"."NR">=1 AND "AB"."NR"<=25)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "SORT_NO","NAME")<=25)
  14 - filter("N"."NODE_TYPE"=2 AND 1<=25)
  15 - filter("N"."TECH_OBJECT_ID"=15773325)
  17 - access("N"."TECH_OBJECT_ID"=15773325)
  22 - access("N"."PARENT_ID"=NULL)
  23 - access("N"."PARENT_ID"=NULL)
  24 - filter("D"."IS_SHARE_DEVICE"=1 AND "D"."CURRENT_STATUS"=0)
  25 - access("TAB"."TECH_OBJECT_ID"="D"."DEVICE_ID")
  26 - filter("C"."ALIAS_NAME"=‘A176‘)
  27 - access("D"."CLASSIFY_ID"="C"."CLASSIFY_ID")
  28 - access("N"."TECH_OBJECT_ID"="D"."DEVICE_ID" AND "N"."NODE_TYPE"=2)
  29 - access("L"."DEVICE_ID"="D"."DEVICE_ID")
  30 - filter("F"."SITE_ID"=1021)
  31 - access("F"."FUNCTION_LOCATION_ID"="L"."FUNCTION_LOCATION_ID")
  32 - access("N"."TECH_OBJECT_ID"="F"."FUNCTION_LOCATION_ID")
  41 - filter("N"."NODE_TYPE"=2 AND 1<=25)
  42 - filter("N"."TECH_OBJECT_ID"=15773325)
  44 - access("N"."TECH_OBJECT_ID"=15773325)
  49 - access("N"."PARENT_ID"=NULL)
  50 - access("N"."PARENT_ID"=NULL)
  51 - filter("D"."IS_SHARE_DEVICE"=0 AND "D"."CURRENT_STATUS"=0)
  52 - access("TAB"."TECH_OBJECT_ID"="D"."DEVICE_ID")
  53 - filter("F"."SITE_ID"=1021)
  54 - access("F"."FUNCTION_LOCATION_ID"="D"."FUNCTION_LOCATION_ID")
  55 - filter("C"."ALIAS_NAME"=‘A176‘)
  56 - access("D"."CLASSIFY_ID"="C"."CLASSIFY_ID")
  57 - access("N"."TECH_OBJECT_ID"="D"."DEVICE_ID" AND "N"."NODE_TYPE"=2)
  58 - access("N"."TECH_OBJECT_ID"="F"."FUNCTION_LOCATION_ID")
统计信息
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        863  consistent gets
          0  physical reads
          0  redo size
       2890  bytes sent via SQL*Net to client
      10807  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         76  sorts (memory)
          0  sorts (disk)
          1  rows processed  


联系
我们