HASH JION AND NESTED JION

ZookeeperPrinciples of HASH_JIONWhen we associate hash_join, we usually have two tables. The smaller tables are called: the larger build input table. We call it probe input; so how does oracle associate these two tables? First, oracle will select build input and read it into the memory. Here oracle will give hash_join a separate memory called hash area. If build input is less than the specified hash area, then hash the probe input. If the build input is greater than the specified hash area, the build input and probe input are partitioned to different partitions, if the split size is appropriate, it will be read into the hash area for hash processing. If the split size is large, oracle will perform nested-loops hash join Processing, until the hash area size is met. Details: http://hwhuang.iteye.com/blog/1479076ABOUT NESTED JOINThe connection method involves the concept of a driver table (External table. In fact, this connection is a two-layer nested loop problem, so the smaller the outer loop table, the better. In theory, the number of loops will be reduced, this is why we use a small table or a table with a small row source as the theoretical basis of the outer circular table. In theory, this processing method will reduce the number of I/O in the right choice, but this is not an absolute basis. We decided that the table is the driver table, that is, the External table has an important impact on the SQL Execution efficiency. The internal connection method is: row1 of row source1 --> probe row source2 (driver table, External table)
Row2 of row source1 --> probe row source2 (driver table, External table) row3 of row source1 --> probe row source2 (driver table, External table) from its running principle, we can also see the importance of row source2 selection. Of course, if row source1 is relatively small, this execution method will be more efficient. In general, if row source1 accesses row source2 through index, the efficiency will be higher. Of course, the premise is that row source2 must have an index. Row source1 matches all rows of row source2. If a matching result exists, it is placed in the result set to be returned. Here, we may think that using this method, we must follow the two simplest conditions in principle. Of course, if it is not met, it doesn't matter, but the performance will be worse. (1) We should try to make a smaller selection when selecting the driver table (External table, in this way, theoretically physical I/O will be less. (2) try to create an index on the drive table, so that the query efficiency will be higher.