一条数据。我的结论是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 AC |