This is a puzzle I've set myself following a discussion about making choices between SQL and PL/SQL - so it's not urgent, not important, and not serious.
If I query dba_extents for a given table (e.g. sys.source$) the extent information looks like this:
select file_id, block_id, blocks
from dba_extents
where owner = 'SYS'
and segment_name = 'SOURCE$'
order by file_id, block_id
;
---------- ---------- ----------
| 1 | 1504 | 8 |
| 1 | 8168 | 8 |
| 1 | 8176 | 8 |
| 1 | 8192 | 8 |
| 1 | 8288 | 8 |
| 1 | 8440 | 8 |
| 1 | 10072 | 8 |
...
| 1 | 77568 | 128 |
| 1 | 77696 | 128 |
| 1 | 77824 | 128 |
| 1 | 78080 | 128 |
| 1 | 89984 | 128 |
...
80 rows selected.
I have a piece of code which reads the exent list, joins it to a list of numbers to enumerate every block in each extent, sorts the blocks by file_id and block_id, applies an ntile(12) to result set, then picks the first and last block in each tile to produce an output which is essentially 12 rows of (first_file_id, first_block_id, last_file_id, last_block_id) - which I can convert to a covering set of rowid ranges for the table. (This is essentially what dbms_parallel_execute does when you create rowid chunks - except it uses PL/SQL to do it).
My SQL does exactly the job needed, but is significantly slower than the PL/SQL equivalent - we're only talking a few seconds across the board for very large objects, so the difference is irrelevant for real production purposes - largely, I think, because I expand the size of the initial result set from the number of extents to the number of blocks then shrink it back down again while the PL/SQL can simply walk through the extent definitions doing simple arithmetic.
I'm sure there's a MODEL clause way of avoiding the explosion, and I'd love to see it if someone has the time, but I keep thinking I'm close to an analytic solution but can't quite get there. So if anyone can come up with a solution that would be even better than a model solution - failing that, can someone prove it can't be done efficiently in simple analytic SQL.
UPDATE: I forgot to state explicitly that the point of doing the block explosion and ntile() was that it was a simple strategy for getting the same number (+/-1) of block in every rowid range.
Regards
Jonathan Lewis
Message was edited by: Jonathan Lewis