Page Allocating

[Stanislav Gromov <gr...@relex.ru>]

I have an question.

As I can understand, Derby don't load whole file(table) in the memory.
It reads pages from it.
So. Question. How does it allocates Pages?
For Example, this SQL Statement
SELECT *
FROM TABLE1, TABLE2
WHERE TABLE1.ID = TABLE2.ID

How does it works?
Does Derby try to read both files (one for TABLE1 and one for TABLE2) 
entirely (all Pages),
and then make Result?
Or how?

Or this Example.
SELECT * FROM TABLE1 WHERE TABLE1.ID=3

Does Derby try to read file entirely (page by page)?
and then make Result?
Or it allocate only needed pages and read only them?

ps.Sorry for my bad English... ;-)

Re: Page Allocating

[Suresh Thalamati <su...@gmail.com>]

Stanislav Gromov wrote:

> I have an question.
>
> As I can understand, Derby don't load whole file(table) in the memory.
> It reads pages from it.


That's correct. Derby has a page cache (a.k.a buffer cache in OS) where 
pages are stored in the memory when they are
read from the disk or before writing to the disk.


> So. Question. How does it allocates Pages?

Default page cache size is 1000 ,   new pages are allocated in the 
memory and filled with the data read from the disk.
When cache becomes full , it uses algorithms similar to LRU concept to 
replace a  page in the cache. It also
schedules page cleaner request to back ground thread when it   hits some 
threshold values.

> For Example, this SQL Statement
> SELECT *
> FROM TABLE1, TABLE2
> WHERE TABLE1.ID = TABLE2.ID
>
> How does it works?
> Does Derby try to read both files (one for TABLE1 and one for TABLE2) 
> entirely (all Pages),
> and then make Result?
> Or how?

>
> Or this Example.
> SELECT * FROM TABLE1 WHERE TABLE1.ID=3
>
> Does Derby try to read file entirely (page by page)?
> and then make Result?
> Or it allocate only needed pages and read only them?
>
 It reads pages from the disk as needed into the page cache.  If  the 
table in the above query does not have indexes it has
to read all the pages from the above tables, but as I mentioned above it 
will read pages as it process.

Tuning guide provides  some  information  about page cache and 
parameters  to change the default values:
 http://incubator.apache.org/derby/docs/tuning/

Thanks
-suresh