CODEWITHSUNDEEP
sqloracle-database
JohnHC
JohnHC

Reputation: 11205

Is there a maximum number of levels for an Oracle connect by level?

I have a connect by level query as part of a chain of CTEs. This runs fine until it hits the connect by level, which causes the query to time out.

The CTE above returns 250 rows, the maximum ldepth of the heirarchy will be somewhere around 13-14 levels.

Is there a limit to the number of levels deep the connect by level query can return?

Upvotes: 0

Views: 1308

Answers (2)

user5683823
user5683823

Reputation:

The number of levels is limited by the memory available at runtime. 13-14 levels is super-safe from that point of view.

If there are no other complications and the issue is just "too many levels", the memory limit is reached relatively quickly and you get an error message when that happens. If your query is running for a very long time, there must be other reasons.

The most likely suspect is an incomplete CONNECT BY clause, where you don't have enough conditions and too many rows are being generated AT EACH LEVEL. That can generate a very large number of rows at relatively low levels.

Upvotes: 2

MT0
MT0

Reputation: 168361

No:

SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 1e38;

Upvotes: 2

Related Questions