| Free Blocks [message #370936] |
Thu, 09 March 2000 12:30  |
Hari Kurup
Messages: 3
Registered: March 2000
|
Junior Member |
|
|
Hi,
I need help for the following:
A table contains a list of block numbers that are free. This block need not be contiguous. I want a query that would accept the min number of free blocks required and return a list of min and max block number between whom there are equal or more number of free blocks than requested.
eg: a Table T with col B representing free block numbers
B
2
3
4
5
10
11
20
21
22
23
47
48
49
So if the min number of free blocks required is 3 then the query should return me the output in the following format.
Min_Block_Num Max_Block_Num Free_Blocks
2 5 4
20 23 4
47 48 3
Hope someone can help me.
Thanx,
Hari
|
|
|
|
| Re: Free Blocks [message #370941 is a reply to message #370936] |
Fri, 10 March 2000 07:56  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Hari,
This is fairly simple using PL/SQL - create a PROCEDURE which accepts the min free blocks as a parameter. Declare a cursor to select col B from table T order by col B ascending, a numeric variable for num_free_blks, a numeric for first_blk, and a numeric variable for prior_blk_num. In the pl/sql block, open the cursor, fetch the first record, move B to prior_blk_num and first_blk, set num_free_blks to 1. Loop through the remaining records. For each record
if col b = prior_blk_num + 1 then increment
num_free_blks by 1, move b to prior_blk_num
else if num_free_blks >= min_free_blks then
print first_blk, prior_blk, num_free_blks; move b to first_blk, prior_blk, set num_free_blks to 1
else move b to first_blk, prior_blk, set num_free_blks to 1. After exiting loop if num_free_blks >= min_free_blks then
print first_blk, prior_blk, num_free_blks;
close the cursor.
Hope this helps,
Paul
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
