I think the following is what is going on. The blobs are not
really instantiated in memory in one sense, but the entire
container is read through the cache a page at a time.

The base container of a derby table is a sequence of pages.  Derby
stores blob data in the same container as the rest of the data,
in a page link chain.  There is a map of pages used only for
space allocation, not page types.  So in the scan case Derby
visits every page in the container and then checks whether each
page is a "main" page or not.  Main pages are returned up the
stack to access to process each row.  In the case of your
query no blob data is actually accessed so "access" never requests
the column data for the blob data - but the whole table is
still read through the cache.  There is no support in the base
container implementation to "skip" to the next main page - that
is what indexes provide.

This is an interesting case for the optimizer.  My guess is that
the optimizer never considers using an index if there is no
where clause unless the index "covers" (includes all the columns
of the query).  I am not sure how hard it would be to get the
optimizer to consider using the index in this case, and I am
not sure if the existing costing handles this blob case - but
from your experiment is sort of looks like the costing is right
as the optimizer is choosing the index when you added the
dummy where - I am sure the costing of that where indicated that
it would scan the entire index.

With performance issues like this a lot is made much more obvious
if you can include the query plan of the 2 queries.

Piet Blok wrote:
> Hi all,
> 
> When experimenting with BLOB's I ran into a performance issue
> that I cannot completely explain, but it could be a bug.
> 
> Given the following table:
> 
> CREATE TABLE BLOB_TABLE (
> BLOB_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, 
> INCREMENT BY 1),
> BLOB_SIZE BIGINT NOT NULL,
> BLOB_CRC BIGINT NOT NULL,
> BLOB_NAME VARCHAR(255) NOT NULL,
> BLOB_DATA BLOB(2G) NOT NULL,
> UNIQUE (BLOB_CRC, BLOB_SIZE),
> PRIMARY KEY (BLOB_ID)
> );
> 
> which is populated with 27 rows,
> where the sum of all BLOB sizes is 5,885,060,164 bytes
> (about 200 MB average per BLOB, but ranging from 10 MB to 750 MB).
> 
> Some queries on this table are executed really
> fast (almost instantaneous response).
> 
> However, the following query needs about 10 minutes to complete:
> 
> SELECT BLOB_ID, BLOB_NAME, BLOB_SIZE, BLOB_CRC FROM BLOB_TABLE;
> 
> I reasoned that maybe Derby is scanning the whole table
> (including the blob contents) so I tried to add a dummy WHERE
> clause (dummy because all BLOB_ID's are greater than 0)
> to offer a clue as to what rows (all of course) are needed,
> as follows
> 
> SELECT BLOB_ID, BLOB_NAME, BLOB_SIZE, BLOB_CRC FROM BLOB_TABLE WHERE 
> BLOB_ID > 0;
> 
> and that helped: instantaneous response.
> 
> But I really think that the original query,
> without the where clause, should not be any slower.
> 
> 
> I am using Derby 10.1.3.1 embedded, Windows XP and Sun Java 1.5.0_06.
> Both queries executed with a Statement, not a PreparedStatement.
> 
> Kind regards,
> 
> Piet Blok
> 
> 
> 
>