Home » RDBMS Server » Server Administration » Free Blocks
Free Blocks [message #370936] Thu, 09 March 2000 12:30 Go to next message
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 Go to previous messageGo to next message
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
Re: Free Blocks, only single SQL query [message #370944 is a reply to message #370941] Fri, 10 March 2000 12:44 Go to previous message
Hari Kurup
Messages: 3
Registered: March 2000
Junior Member
Paul,
Thanx for the solution. I am sure its very easy doing it in PL/SQL procedure. But the need is to do it in a single SQL query.

Hope you can help me on this.

rgds,
Hari
Previous Topic: Audit column names in trigger
Next Topic: how do i call context sql query in the procedure which takes one input parameter
Goto Forum:
  


Current Time: Fri Oct 30 03:36:18 CDT 2020