e
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 ( |