Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

What is "connect by pump" mean

74483Sep 20 2005 — edited Sep 20 2005
I 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]
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 18 2005
Added on Sep 20 2005
3 comments
2,227 views