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:
- Chunking tables 1: Genesis
- Chunking tables 2: Requirement
- Chunking tables 3: working with blocks
- Chunking tables 4: Histograms
- Chunking tables 5: chunk boundaries
- Chunking tables 6: JOIN solution
- Chunking tables 7: prior sys_guid() ???
- Chunking tables 8: second solution
To generate rows quickly, it is common to use the
CONNECT BY clause.
select level num from dual connect by level <= 5;
This is a simple “hierarchical” query that uses the
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.
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;
Using the very same technique as before:
select range_id, range_end, level from u connect by level <= range_end;
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
select range_id, range_end, level from u connect by nocycle level <= range_end and range_id = prior range_id;
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.
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;
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 );