db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Queries satisfiable from indexes
Date Wed, 21 Mar 2007 00:20:57 GMT
You must have set a different default for page size to get 16k index 
page, you could try 32k it might make scan a little faster - but as
you point out the current query and derby architecture requires a full scan
of all the rows in the index since it only has 1 value for mail_id,
and then compare them all to eliminate the huge number of duplicates.

It is interesting to think about "skipping index prefixes".  In this
case we could use the index to eliminate a lot of duplicates we don't
really care about (I have no idea how to teach the optimizer this
concept).  For this particular case a better use of the index
would have been something like:

open index scan multi-probe on index with mail_id >= ? and mail_id < ?
get 1st row, next tag key = N (where N is 1st tag returned)
reopen index scan multi-probe on index with mail_id = ?  and tag > next 
tag key
return this row and set new next tag key = N (where N is the value just 

Could this be generalized as some optimized "Distinct scan", maybe only
chosen if we new that the number of duplicates indicated it was better
to travel up and down the tree rather than scan across at the leaf?


Dan Karp wrote:
>>You can use the following to get information about page size and
>>number of pages in your table/indexes:
> Well, you're absolutely right.  It's hitting all 326 pages in the index:
>    NAME                 |IDX |PAGE |PAGE|PAGE|SIZE |SAVING
>    =====================+====+=====+====+====+=====+======
>    I_MAIL_ITEM_TAGS_DATE|1   |326  |0   |1   |16384|0
> So I guess that means there's no way to speed up the query short of teaching Derby to
check only the index prefixes when possible?

View raw message