On 02/21/2011 10:22 AM, Isuru Anuranga wrote:
When there is a join operation to be performed, which table is most likely to be selected as inner relation? table with less data or more data.
It doesn't really work like that; it's not a probabilistic thing, exactly. Instead, it's an algorithm called "Cost Based Optimization", which dates back 40 years or so. The basic idea is that the optimizer enumerates all possible alternatives for evaluating the query, and chooses the one which (in its estimation) will be the cheapest. You can find quite a bit of detailed information about Derby's optimization algorithms in the Derby docs here: http://db.apache.org/derby/docs/10.7/tuning/ctunoptimz23977.html There's also information in the Derby wiki here: http://wiki.apache.org/db-derby/LanguageOptimize And, of course, in the Derby source code :) For more general information on Cost Based Optimization, this Wikipedia article is great place to start: http://en.wikipedia.org/wiki/Query_optimizer thanks, bryan
