CODEWITHSUNDEEP
sqloracle-databaseoracle11g
michael nesterenko
michael nesterenko

Reputation: 14449

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)?

Upvotes: 0

Views: 104

Answers (1)

GolezTrol
GolezTrol

Reputation: 116140

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.

Upvotes: 2

Related Questions