Hello,i am on 10.2.0.3.0 and i have problems when i try to use materialize hint.
If i go with this query :
WITH a AS
(SELECT
ca.customer_id_high, ca.customer_id,
NVL (ca.cscomptaxno, ca.passportno) jmbg
FROM customer_all@vipbscs ca
WHERE ca.cstype <> 'd'),
b AS
(SELECT
co.customer_id, co.co_id contract_id,
cis.billing.cotomsisdn (co.co_id) msisdn
FROM contract_all@vipbscs co),
c AS
(SELECT pah.cid
FROM pa_head_agent pah, pa_teams t, pa_team_agents ta
WHERE pah.agent_id = ta.agent_id
AND t.ID = ta.team_id
AND t.ID IN (21)
AND ta.agent_id IN (10210))
SELECT b.contract_id, b.msisdn, a.customer_id, a.jmbg, ka.nr ka_nr
FROM a, b, ka_head ka
WHERE a.customer_id = ka.cid(+) AND a.customer_id = b.customer_id(+)
CONNECT BY PRIOR a.customer_id = a.customer_id_high
START WITH a.customer_id IN (SELECT cid
FROM c)
It takes years to finish.
And if i go with this query, exeplain plan is showing let's say exceptable execution time estimation, but it fails with ORA-12840: cannot access a remote table after parallel/insert direct load txn :
WITH a AS
(SELECT /*+ MATERIALIZE */
ca.customer_id_high, ca.customer_id,
NVL (ca.cscomptaxno, ca.passportno) jmbg
FROM customer_all@vipbscs ca
WHERE ca.cstype <> 'd'),
b AS
(SELECT /*+ MATERIALIZE */
co.customer_id, co.co_id contract_id,
cis.billing.cotomsisdn (co.co_id) msisdn
FROM contract_all@vipbscs co),
c AS
(SELECT pah.cid
FROM pa_head_agent pah, pa_teams t, pa_team_agents ta
WHERE pah.agent_id = ta.agent_id
AND t.ID = ta.team_id
AND t.ID IN (21)
AND ta.agent_id IN (10210))
SELECT b.contract_id, b.msisdn, a.customer_id, a.jmbg, ka.nr ka_nr
FROM a, b, ka_head ka
WHERE a.customer_id = ka.cid(+) AND a.customer_id = b.customer_id(+)
CONNECT BY PRIOR a.customer_id = a.customer_id_high
START WITH a.customer_id IN (SELECT cid
FROM c)
Can you suggest something i could do optimize this query ?
I can post explan plan if you thing it could help.