Quick Understanding Oracle Hash Connections ____oracle

Learn about Oracle hash connections quickly

[ Introduction] Here you will need to set hash_join_enabled=true for an Oracle hash connection to be valid, by default this parameter is TRUE, and do not forget to set the Hash_area_size parameter.

Before giving you a detailed introduction to the Oracle hash connection, let's begin by understanding that Oracle reads Row source, and then provides a comprehensive overview of Oracle hash connections, which is useful for everyone. In the nested loops connection, Oracle reads each row in the row Source1 and then checks in row sourc2 for matching rows, all matched rows are placed in the result set, and the next row in row Source1 is processed.

This process continues until all the rows in row source1 are processed. This is one of the fastest ways to get the first matching row from a join operation, and this type of connection can be used in statements that require a quick response, with the response speed as the primary goal.

This method can be more efficient if driving row source (external table) is small and has a unique index on inner row source (internal table), or if there is a highly selective, non unique index. One advantage of NESTED loops that there are no other connection methods is that you can return rows that have already been connected without having to wait for all of the connection operations to be processed before returning data, which enables fast response times.

If you do not use parallel operations, the best driver tables are those that can return fewer rows of data after the where constraint is applied, so a large table may also be called a driver table, with the key looking at the constraints. For parallel queries, we often choose a large table as the driving table, because the large table can take full advantage of the parallel functionality. Of course, sometimes using parallel operations on a query is not necessarily more efficient than querying without using parallel operations, because it is possible that only a small number of rows per table meet the constraints, but also depends on whether your hardware configuration can support parallelism (such as whether there are multiple CPUs, multiple hard disk controllers), so specific problems to deal with.

　　

Example of the NL connection:

Sql >Explain plan for select A.dname,b.sql from dept a,emp b where a.deptno = B.deptno; Query plan SELECT STATEMENT [CHOOSE] cost=5 NESTED LOOPS table access full DEPT [analyzed] table access full EMP [A Nalyzed]

Oracle Hash connection (hash join, HJ)

This connection was introduced after Oracle 7.3, which is theoretically more efficient than NL and SMJ and is used only in the CBO optimizer.

The smaller row source is used to construct the hash table and the bitmap, and the 2nd row source is used to be hansed and matched with the hash table generated by the first row source for further connection. Bitmap is used as a quick way to find out if there is a matching row in the hash table. In particular, this lookup method is more useful when the hash table is larger than it is fully accommodated in memory. This connection method also has the concept of the so-called driver table in the NL connection, which is constructed as the driver table of the hash table and the bitmap table, which is highly efficient when the constructed hash table and bitmap can be accommodated in memory.

　　

Example of a hash connection:

Sql >Explain plan for select/*+ use_hash (EMP) * * empno from EMP, dept where emp.deptno = Dept.deptno; Query plan SELECT STATEMENT [CHOOSE] cost=3 HASH JOIN table access full DEPT TABLE access full EMP

To make an Oracle hash connection valid, you need to set Hash_join_enabled=true, which is TRUE by default, and don't forget to set the Hash_area_size parameter so that the Oracle hash connection runs efficiently. Because an Oracle hash connection runs in memory of the specified size of the parameter, too small a parameter makes the Oracle hash connection less performance than the other connection methods.

To summarize, in which case the connection method is better:

Sort--Merge joins (sort merge Join, SMJ):

A The efficiency of this connection is relatively high for non equivalence connections.

b If there is an index on the associated column, the effect is better.

c The connection method is better than the NL connection for connecting 2 large row source.

D) But if the row source returned by the sort merge is too large, it can cause database performance to degrade because of excessive I/O when querying the data in the table with too many rowid.