Oracle Optimization – Generate Rows Based on Value in Two Columns

optimizationoracleoracle-10gquery-performance

I have the following table in Oracle:

  ID     START  END
_ID01   AB050   AB100
_ID01   AB101   AB150
_ID02   AB0251  AB0300
_ID01   AB00301 AB00302

I want to generate as many rows and every records defines. So the first need 50 more with a new col start with AB50 up to AB100. Everything works fine, but if I don't add that junk row, the sql developer can't solve the problem, just loading.

I tried with some random SQL hint but none of them worked. Need some advice or hint what's "wrong" with my working query, because I don't think that's normal behavior.

And my SQL query is the following.

     with m as (
      select  to_number(substr(t.START,3)) as num
      from FCS_FP_TOMB t
      where t.START ='AB0251' --<--- THIS ROW IS A JUNK BUT DOESNT WORK WITHOUT IT
     ),
      n as (
      select level - 1 as n
      from m
      connect by level <= m.num + 1
     )
select  ft0.*, regexp_substr(ft0.START,'([A-Z])+')||to_char(to_number(substr(ft0.START,3)) + n.n) as "bizonylat"
from FCS_FP_TOMB ft0 join
     n
     on to_number(substr(ft0.START,3)) + n.n <= to_number(substr(ft0.END,3))

 order by 2,3,7
     ;

The out put:

...
_ID01   AB050   AB100   AB098
_ID01   AB050   AB100   AB099
_ID01   AB050   AB100   AB100
_ID01   AB101   AB150   AB101
_ID01   AB101   AB150   AB102
...

EDIT: I tried MATERIALIZE and INLINE hints, and my Oracle server is 10g.

Best Answer

You have an error in your query. The CTE m will return not a single row, but as many rows as in FCS_FP_TOMB. Then you are putting all of those rows into the row generator CTE n, whereas a row generator construct like you're using should get a single row.

What I would do is to define m to return a single row with the minimum and maximum values, and then subtract these in n to get the number of rows you need to generate.

Something like (untested):

with m as (
  select max(to_number(substr(t.END,3))) - min(to_number(substr(t.START,3))) as num
  from FCS_FP_TOMB t
), n as (
  select level - 1 as n
  from m
  connect by level <= m.num + 1
)
select ft0.*, regexp_substr(ft0.START,'([A-])+')||to_char(to_number(substr(ft0.START,3)) + n.n) as "bizonylat"
from FCS_FP_TOMB ft0
join n on to_number(substr(ft0.START,3)) + n.n <= to_number(substr(ft0.END,3))
order by 2,3,7;
Related Question