CONNECT BY LEVEL performance
497150Mar 4 2008 — edited Mar 7 2008I have a view that returns set of years based on CONNECT BY LEVEL. In the code, the year returned is joined to other views/tables and that seems to cause perfromance issues.
I need help to rewrite to view so I could get the same result set but I could have better perfromance on Oracle 9.2.0.7.
Thanks a lot, mj
this is the view :
SELECT DISTINCT year FROM ((SELECT (t.min_year + rownum - 1) AS year
FROM ( SELECT MIN(year) AS min_year, (MAX(year) - MIN(year) + 1) AS counted FROM account ) t CONNECT BY LEVEL <= t.counted )
UNION SELECT (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-1) AS year FROM DUAL) WHERE year IS NOT NULL;
This is the way the view is in use:
select count(*) from acct_hist p, hist h, my_Connect_By_view y
where p.PID=h.PID and p.year=y.year and date_submitted > (date_due + 1)