Problem with ORDER BY – and Indexies

[Ralph Soika <Ra...@imixs.com>]

Hi,
I have a strange problem where I did not found a solution and need some
help.
I am using a EQL/SQL Statement with a ORDER BY clause which fails and did
not return the result in the right order.
The background is a JEE EJB 3.0 application deployed on glassfish using
DerbyDB and TopLink. So I have about 15 Tables generated by the TopLink-OR
Mapper with different relations ships.
I am working with an EQL statement using multiple joins. See the following
example:

SELECT orgunit FROM Entity AS orgunit 

	JOIN orgunit.textItems AS t1

	JOIN orgunit.textItems AS t2

	WHERE t1.itemName = 'type' 

	 AND t1.itemValue = 'project'

	 AND t2.itemName = 'txtname' 

 ORDER BY t2.itemValue asc

The tables where generated by the Toplink OR-Mapper during deployment of my
EJB application.
First I want to make clear that everything works perfect if I did not change
anything on the generated Database. My EQL Statement succeeds with no
problems. 
After a while I recognized a little bit performance loose. So I read about
creating Indexes manually inside the derby db.
I started to create indexes for the affected  columns in my database tables.


CREATE INDEX READERITEM_INDEX_VALUE ON READERITEM (ITEMVALUE);
CREATE INDEX READERITEM_INDEX_NAME ON READERITEM (ITEMNAME);
CREATE INDEX READERITEM_INDEX_VALUE_NAME ON READERITEM (ITEMNAME,
ITEMVALUE);

Now the performance increased considerable. But for some reason which I did
not understand the ORDER BY clause failed now! The result set agaon is
correct but it isn't ordered by my criteria!
When I drop the Index everything works again perfect but a little bit
slower. When I change the ORDER BY clause do sort descending .....DESC  the
result is also ordered correctly descending! But with the ascending Index it
seems not to get the right ordered resultset :-(

Now I suppose that the database is maybe corrupted because I can work with a
backup which seems to work correctly with a nearly identical amount of data
I can insert new records and the result set is ordered well.

Can anybody give me an Idea whats is going on there?
Is there a way to check if a index is corrupted? Is there a limit of
generated indexes?
Dropping and recreating the index did not help.

Thanks for any suggestions and any help 

Ralph
-- 
View this message in context: http://www.nabble.com/Problem-with-ORDER-BY-%E2%80%93-and-Indexies-tp19656164p19656164.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.