Rahul K wrote:First of all, as a rule we should let the optimizer choose the join method and the best way to perform it, so your question is theoretical.
...When performing a Hash join ,which table should be used to make the hash table ,the larger one or the smaller one and why ?
Rahul K wrote:You should never use joining or index hints in production code. That would be making the assumption that you as the developer know better than the oracle database how to get the data from data sets that change over time, and that your assumption will always be better in the future. That's ridiculous and is the cause of many people's 'performance issues' (which are usually fixed by simply removing such stupid hints). The hints are there to help analyse performance issues and identify where a database design is poor or where a query is written badly, but should most definitely not be considered a way to 'fix' performance issues.
Yes, its always left to the optimizer to chose. But when i went through the manual ,it said ,if you have some different requirement of data .i.e. for a better response time (we have to quicky display the top ten transactions of someone's bank statement) ,we can hint the optimizer to use Nested loop rather than hash join or sort merge.
Rahul K wrote:Never say never. There are some hints which are acceptable, such as FIRST_ROWS, APPEND etc. The ones that shouldn't be used in production (without damn good justification) are those that tell the optimizer how to join or access tables/data.
So what I understand is :
1) Never use hints ...
2)If we don't have appropriate statistics on our object then the default values(rowlen,row number,distinct column values) chosen by the CBO can be inappropriate and hence the plan can be inappropriate.Yes, having stats up to date is important.
3)Since COB transforms our query in a number of ways ,changes the join order,join method ,access paths etc so its quite logical that the optimizer would have checked for the strategy which we are asking the COB to use ,using hints .Not necessarily. The plan you create using hints may be soooo way off the mark, that the optimiser doesn't even evaluate it, based on the statistics on the tables etc. that it knows about.
William Robertson wrote:Yes absolutely... but sorry I wasn't clear.
Well, the optimiser will ignore a hint if it's syntactically invalid or logically impossible. Otherwise it has to use it.
William Robertson wrote:That's a good way of phrasing it. :)
Ah, see what you meant. Agreed, if there are a million logically possible execution plans, the optimizer cannot be expected to evaluate every single one, and we would not generally want it to. Therefore a hint may make it consider something that would otherwise have been off its radar.