MATERIALIZE hint with CONNECT BY issue
I have the following query (simplified for illustration purposes)
WITH MANAGERS AS (
SELECT /*+ MATERIALIZE */ A.EMPLID
FROM HIERARCHY A
WHERE EXISTS (
SELECT C.EMPLID
FROM COMPENSATION C
WHERE C.EMPLID=A.EMPLID
AND C.YEAR='2011'
AND C.PLAN='EXEC')
CONNECT BY PRIOR A.EMPLID=A.MANAGER_ID START WITH A.EMPLID='E700025')
SELECT COUNT(*) FROM MANAGERS
COUNT(*)
----------------------
10
1 rows selected
and yet, when I do SELECT * FROM MANAGERS, I get 5000+ rows, e.g.
WITH MANAGERS AS (
SELECT /*+ MATERIALIZE */ A.EMPLID
FROM HIERARCHY A
WHERE EXISTS (
SELECT C.EMPLID
FROM COMPENSATION C
WHERE C.EMPLID=A.EMPLID
AND C.YEAR='2011'
AND C.PLAN='EXEC')
CONNECT BY PRIOR A.EMPLID=A.MANAGER_ID START WITH A.EMPLID='E700025')
SELECT * FROM MANAGERS
If I remove the materialize hint, then I get 10 rows which is what I expected. However, it takes a long time to execute.
Is there a restriction with using materialize hint on CONNECT BY subqueries?