Chunking tables 7: prior sys_guid() ???

The second solution for dividing a table into equal chunks does not do a JOIN. Instead it expands extent rows that contain several chunk boundaries, using an obscure method that I need to explain.

Current list of posts: 

Generating rows

To generate rows quickly, it is common to use the CONNECT BY clause.

select level num
from dual
connect by level <= 5;
NUM
1
2
3
4
5

 

This is a simple “hierarchical” query that uses the LEVEL pseudocolumn. LEVEL starts at 1 and increments every time the CONNECT BY is executed. The CONNECT BY condition says to keep going as long as LEVEL is less than or equal to 5.

Note that there is no reference to a PRIOR row in the condition, since there is only one row in the DUAL table.

Expanding rows

Here is where things get tricky.

Suppose I have two rows and I want to expand both of them to get one row for every integer in the range. For example, take this table:

drop table U purge;

create table U as
select 1 range_id, 2 range_end from dual
union all
select 2, 3 from dual;

select * from u;
RANGE_ID RANGE_END
1 2
2 3

 

Using the very same technique as before:

select range_id, range_end, level
from u
connect by level <= range_end;
RANGE_ID RANGE_END LEVEL
1 2 1
1 2 2
2 3 3
2 3 2
2 3 3
2 3 1
1 2 2
2 3 3
2 3 2
2 3 3

 

What is this mess? It looks like I’m starting with each row and connecting to the other row – which makes sense because I’m not saying to stay on the same row. Let’s try again:

select range_id, range_end, level
from u
connect by level <= range_end
and range_id = prior range_id

Error report - SQL Error: ORA-01436: CONNECT BY loop in user data

Now I made a reference to something PRIOR – the range_id. Oracle sees that the same range_id is accessed twice in a row, so it assumes there is an infinite loop and aborts the execution.

There is a way to avoid that error, using the NOCYCLE keyword:

select range_id, range_end, level
from u
connect by nocycle level <= range_end
and range_id = prior range_id;
RANGE_ID RANGE_END LEVEL
1 2 1
2 3 1

 

Well, I didn’t get the error, but Oracle still considers that doing the same range_id twice would be a loop, so it stops first.

Enter SYS_GUID()

What we need is to add something to the prior row that will make Oracle think it is different. SYS_GUID() is a very low-cost function that returns a nonrepeating value. If we refer to PRIOR SYS-GUID() in a condition, that is enough to make the prior row unique and to prevent the perception of an infinite loop.

select range_id, range_end, level
from u
connect by level <= range_end
and range_id = prior range_id
and prior sys_guid() is not null;
RANGE_ID RANGE_END LEVEL
1 2 1
1 2 2
2 3 1
2 3 2
2 3 3

 

In the next post, I will use this technique to expand extent rows that contain multiple chunks.

P.S. If you have version 12c, you can use the LATERAL clause, which will expand each row without bending your mind.

select * from u,
lateral(
  select level lvl from dual
  connect by level <= range_end
);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s