What is "connect by pump" mean
74483Sep 20 2005 — edited Sep 20 2005I have written a below hierarchical query where the START WITH
what is the meaning of "CONNECT BY PUMP " in the explain plan output.?
Regards
NTRAO
select et.parent_id
, display_value
from
( SELECT parent_id
, element_id
, application_id
FROM DT_ELEMENT_TREES et
WHERE et.application_id = 12 AND et.image_id = 9
CONNECT
BY PRIOR parent_id = node_id
AND PRIOR application_id = application_id
AND PRIOR image_id = image_id
START WITH node_id = 834
ORDER BY LEVEL DESC
) et
JOIN DT_ELEMENTS el ON el.ELEMENT_Id = et.element_Id
JOIN DT_TEXTS t ON el.text_Id = t.text_Id
This runs with the following explain plan:
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT Cost = 4 [CHOOSE]
NESTED LOOPS []
NESTED LOOPS []
VIEW []
SORT ORDER BY []
FILTER []
CONNECT BY WITH FILTERING []
NESTED LOOPS []
TABLE ACCESS FULL DT_ELEMENT_TREES [ANALYZED]
TABLE ACCESS BY USER ROWID DT_ELEMENT_TREES [ANALYZED]
NESTED LOOPS []
BUFFER SORT []
CONNECT BY PUMP []
TABLE ACCESS BY INDEX ROWID DT_ELEMENT_TREES [ANALYZED]
INDEX UNIQUE SCAN PK_DT_ELEMENT_TREE [ANALYZED]
TABLE ACCESS BY INDEX ROWID DT_ELEMENTS [ANALYZED]
INDEX UNIQUE SCAN PK_DT_ELEMENTS [ANALYZED]
TABLE ACCESS BY INDEX ROWID DT_TEXTS [ANALYZED]
INDEX UNIQUE SCAN PK_DT_TEXTS [ANALYZED]