Hi all
I have a serious problem.
First of all i wrote a view as:
SELECT
Unit.ID UnitID,
Unit.parent ParentUnitID,
DocTyp.ID DocTypID,
DocTyp.RTDT_CODE DocTypCode,
DocTypRel.RTDR_RTDT_REF_REL2 DocTypParent,
COUNT(Doc.ID) TotalDoc
FROM
oc_unit Unit
INNER JOIN
tb_rtdoc Doc
ON Doc.RTDC_DL_REF1=Unit.id
LEFT OUTER JOIN
TB_RTDOCTYPE DocTyp
ON DOC.RTDC_RTDT_REF=DocTyp.ID
LEFT OUTER JOIN
TB_RTDOCTYPEREL DocTypRel
ON DocTypRel.RTDR_RTDT_REF_REL1=DocTyp.ID
GROUP BY
ROLLUP(Unit.ID,
Unit.parent,
DocTyp.ID,
DocTyp.RTDT_CODE,
DocTypRel.RTDR_RTDT_REF_REL2)
ORDER BY UnitID,
ParentUnitID,
DocTypID,
DocTypCode,
DocTypParent;
It works great. Then I wrote a query base on this view:
SELECT
LEVEL,
UnitID,
ParentUnitID,
DocTypID,
DocTypCode,
DocTypParent,
SUBSTR((SYS_CONNECT_BY_PATH ( UnitID ,'.')),2) FullPath,
TotalDoc
FROM VW_Unit_Doc_DocTyp_Summary
START WITH ParentUnitID IS NULL
CONNECT BY PRIOR UnitID=ParentUnitID
But it works terrible! it takes more than 2 mins to exectue.
How can I improve my query performance?
Thanks alot