Les08-排序关联.ppt

Sorting and Joining Objectives After completing this lesson, you should be able to do the following: Optimize sort performance Describe different join techniques Explain join optimization Find optimal join execution plans Sort Operations SORT UNIQUE SORT AGGREGATE SORT GROUP BY SORT JOIN SORT ORDER BY Tuning Sort Performance Sorting large sets can be expensive, so you should tune sort parameters. Note that DISTINCT, GROequijoind most set operators cause implicit sorts. Minimize sorting by one of the following: Try to avoid DISTINCT and GROUP BY. Use UNION ALL instead of UNION. Enable index access to avoid sorting. Top-N SQL Join Terminology Join statement Join predicate, nonjoin predicate Single-row predicate Join Terminology Natural join Join with nonequal predicate Join Operations A join operation combines the output from two row sources and returns one resulting row source. Join operation types include the following: Nested loops join Sort/merge join Hash join Cluster join Nested Loops Joins One of the two tables is defined as the outer table (or the driving table). The other table is called the inner table. For each row in the outer table, all matching rows in the inner table are retrieved. Nested Loops Join Plan When Are Nested Loop Joins Used? Nested loop joins are used when: Joining a few rows that have a good driving condition Order of tables is important USE_NL(table1 table2)hint is used Hash Joins A hash join is executed as follows: Both tables are split into as many partitions as required, using a full table scan. For each partition pair, a hash table is built in memory on the smallest partition. The other partition is used to probe the hash table. Hash Join Plan When Are Hash Joins Used? Hash joins are used if either of the following conditions is true: A large amount of data needs to be joined. A large fraction of the table needs to be joined Use the USE_HASH hint, and use correct values for the following initialization parameters: HASH_AREA_SI