Strange behaviour of hierarchical query
I have a hierarchical query that returns same row (by rowid) several times:
select rowid, regexp_substr(col, '[^; ]+', 1, level) data, level from (select * from table1 where rowid in ('rowid1', 'rowid2')) connect by regexp_substr(col, '[^; ]+', 1, level) is not null
This query returns following result (excerpt):
rowid data level AADAxrADkAACic5AAA val1 1 AADAxrADkAACic5AAA val2 2 AADAxrADkAACic5AAA val3 3 AADAxrADkAACic5AAA val4 4 AADAxrADkAACic5AAA val5 5 AADAxrADkAACic5AAA val6 6
How can that happen that same row is parent and child of itself?
Also if I want to restrict by rowid which children I want to get:
connect by regexp_substr(col, '[^; ]+', 1, level) is not null and prior rowid = rowid
Oracle tells me that I have a cycle in my data? That seems reasonable because I have row with same rowid as a parent and child of itself, but how it worked in the previous case (without and prior rowid = rowid)?
It looks like this can happen if col would contain something like 'val1;val2;val3;val4'. Then each next level would still match the same row, since you're not using any field value of the previous row, but just the level.
SQL-Fiddle proving my suggestion: http://sqlfiddle.com/#!4/10b11/1/0
Oracle tries to detect cycling, but only checks for this if the prior keyword is used in the query. This fenomenon is also more or less described on this page: http://www.dba-oracle.com/t_advanced_sql_connect_by_loop.htm under the heading 'CONNECT BY without PRIOR':
CONNECT BY without PRIOR
A very popular usage of hierarchical query, documented by Vadim Tropashko in his book SQL Design Patterns, is to generate rows.
SELECT SYS_CONNECT_BY_PATH(DUMMY, '/') FROM DUAL CONNECT BY LEVEL<4; SYS_CONNECT_BY_PATH(DUMMY,'/') -------------------------------- /X /X/X /X/X/X
NOTE: According to the official documentation, PRIOR is mandatory. Oracle Database SQL Language Reference (11.1) “In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator”.
The single row of dual is both the parent and the child of itself but no loop is generated. It is a very efficient way to generate rows.