I want to optimize the sql query. It internally performing Hash Join , so it going for full table scan to get the result .
alter session set "_hash_join_enabled" = false;
If I use the above command, oracle taking nested loop and there is no full table scan.
Can anyone suggest me the real time application use of this?
On
Generally, any parameter that begins with an underscore is an undocumented parameter. It is unwise to set any undocumented parameter in a production system unless Oracle Support specifically tells you to do so. That will almost always come after they have confirmed that you are encountering a particular bug and have helped you determine that the best workaround is to set one of the undocumented parameters.
Disabling hash joins for the session means that any run by that session would not consider using a hash join. At a minimum, before you set something like that, you'd want to analyze the plans of every query that session could possibly run (most likely every query your application could run) to determine whether they are currently using a hash join and what the expected performance impact would be if they used some other join. And you'd want to do a full performance regression test in a lower environment to determine what impact the change has on application performance. It would be rare for a system not to have some queries that benefit from using hash joins.
If the problem is a single query, making any parameter changes at the session level is likely to be overkill. It makes much more sense to address problems with individual queries at the level of the individual query. Most often, when the Oracle optimizer chooses a bad plan, the issue is that the statistics you have on one or more objects are incorrect. That leads Oracle to believe that the table scan will be more efficient than it actually is or to expect that the nested loop join will be less efficient than it really is. It may be that statistics are out of date or that a particular statistic (i.e. a histogram on a column with skewed data) is missing. If you can fix the issue by fixing the statistics, that's the preferred approach.
If you can't fix the issue by fixing the statistics, you could consider applying hints to the query. You could set an undocumented parameter in a hint in a particular query but it would be preferable to stick with the documented hints. And if I was trying to change the plan, I'd want hints that tell the optimizer what you want to happen, like the use_nl hint, rather than hints that tell the optimizer what isn't allowed. If you disable hash joins, Oracle might well start doing a merge join instead of a nested loop join down the road either because of a statistics change or following a database upgrade.
The problem is not the full table scan but the time to execute a query. The optimizer chooses hash join and FTS because it thinks it is faster than nested loops. Usually this is a problem with the statistics, or the way the query is written.
If your really want to do it by a hint like this, then use it at the statement level, like the comment said (e;g. opt_param('_hash_join_enabled','false'), or use_nl(put the table names here in the order you want)).