do you think this is a bug? or am i doing something incorrectly?
I think the behavior you are seeing is a bug, and should be addressed. It's clear that the query plans are different, and that the one query plan is dramatically inferior, as it chooses a table scan rather than using the available index. The table scan requires touching all 350,000 rows in the table, and thus your performance difference is dramatic. I don't know what is causing this, but certainly the optimizer has some very delicate handling of the predicates (s.ticker='OIH' and o.fd >= '20081001', in your case) as it is manipulating the various tables involved in the query, and it could well be that the optimizer is failing to "push down" the predicate through the view, causing it to fail to recognize that the index could be used effectively in the query. Can you package up your findings and open an issue in JIRA? If you can provide a complete standalone test program, including the sample data, that would be great, but if you could at least start with the information that you've already provided (your SQL.TXT file and your derby.log file), that would be a great start. thanks, bryan
