Poor performance of CONNECT BY PRIOR
Hi All
I am facing performance issue of one view which is using CONNECT BY PRIOR.
The View is as below:
CREATE OR REPLACE VIEW P_VIEW_NAME AS
SELECT xpn.quote_id,
version_no
/*, ROW_NUMBER() OVER(ORDER BY quote_id, version_no asc)*/
,
discount
FROM TABLE_A xpn
UNION
SELECT DISTINCT connect_by_root (xpm.new_quote_id) AS quote_id,
version_no
/*, ROW_NUMBER() OVER(ORDER BY quote_id, version_no asc)*/
,
discount
FROM TABLE_B xpm,
TABLE_A xpn
WHERE xpn.quote_id = xpm.original_quote_id
AND xpm.status = 'C'
AND xpm.new_quote_id IS NOT NULL