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 Tue, 20 Mar 2007 23:13:48 GMT

Dan Karp wrote:
>>In the case of the query plan you presented, it seems
>>to me that Derby is in fact using the index, and not the
>>base table; that's what I believe this line is saying:
>>>Index Scan ResultSet for MAIL_ITEM using index I_MAIL_ITEM_TAGS_DATE
> I believe that's included for every query satisfied using an index.  But the number of
pages accessed indicates that it actually loaded all the rows from the table:
>    scan information:
> Bit set of columns fetched={0, 1}
> Number of columns fetched=2
> Number of deleted rows visited=20458
> Number of pages visited=501
> Number of rows qualified=141512
> Number of rows visited=161970
> I think that's *way* too many pages hit if we're just using the index.  I've had a query
on this table that sorted 33,000 rows without an index, and it only visited 300+ pages.
page size of indexes and base tables do not have to be the same size. 
If you have not set page size yourself, giving us your ddl we can tell 
you default page size derby will pick.

View raw message