Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CONNECT BY LEVEL performance

497150Mar 4 2008 — edited Mar 7 2008
I 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)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 4 2008
Added on Mar 4 2008
7 comments
2,231 views