CODEWITHSUNDEEP
sqldatabaserelational-database
Ohad
Ohad

Reputation: 1651

how to calculate number of blocks to be read and written in a query?

there is a table s(A,C)

A is a key.

number of records in s: ns= 20,000

block can contain 30 records from s

the question is:

how many blocks do I need to read and how many to write back for returning the answer in each query:

  select*
    from s 
    where A=100,000

  select*
   from s 
   where A<100,000

  select*
   from s 
   where A>100,000

i have calculated that:

s has 20,000/30=667 blocks

but I don't know how to approach this problem.

the answer should be a number (a calculation) and not a query in sql. It's about estimation of number of time we access the memory

Upvotes: 0

Views: 1984

Answers (1)

Metaphor
Metaphor

Reputation: 6415

If I understand correctly, you would do something like the following:

select rank() over (order by a) / 30 + 1 as block, *
from s
order by block

This will give each row a "block number" with 30 rows per block.

Upvotes: 2

Related Questions