表t1、
PONO f1 f2
--------------------------
10 A red
10 B red
10 C Blue
20 A Green
20 A Blue
10 XH Pink 表t2、
PONO ff1 ff2 ff3
----------------------------------
10 R A M
10 G A S
20 B A S
20 P B M
20 Bl B L 有两表t1和t2,当执行下面的连接时,
select t1.*,t2.* from t1 inner join t2 on t1.PONO=t2.PONO
系统的执行过程是怎样的?
是不是t1中将第一条记录和t2的所有记录进行对比,若有符合的的就筛选出来,然后将t1中的第二条记录和t2的所有记录进行对比,若有符合的的就筛选出来;
直到将t1中将最后一条记录和t2的所有记录进行对比执行完毕吗?
PONO f1 f2
--------------------------
10 A red
10 B red
10 C Blue
20 A Green
20 A Blue
10 XH Pink 表t2、
PONO ff1 ff2 ff3
----------------------------------
10 R A M
10 G A S
20 B A S
20 P B M
20 Bl B L 有两表t1和t2,当执行下面的连接时,
select t1.*,t2.* from t1 inner join t2 on t1.PONO=t2.PONO
系统的执行过程是怎样的?
是不是t1中将第一条记录和t2的所有记录进行对比,若有符合的的就筛选出来,然后将t1中的第二条记录和t2的所有记录进行对比,若有符合的的就筛选出来;
直到将t1中将最后一条记录和t2的所有记录进行对比执行完毕吗?
至于是循环嵌套匹配还是hash匹配就看你的表数据和关联关系了
The optimizer chose one table to process first,and for each row that qualified ( based on any WHERE clause conditions involving columns in that table),SQL Server used the JOIN clause to find all matching rows in the second table. The JOIN clause usually performed an equality comparison between a column in the first table and a column in the second.You can think of nested-loop joins as being the default type of join.
MERGE JOIN
It is appropriate when both input sets to the JOIN operation are ordered by the values in the join column, as would be the case when both have a clustered index on the column you're useing to join the tables. The optimizer usually chooses a merge join when clusted indexes exist on the join column in both tables. SQL Server can perform merge joins only when at leat one of the inputs is known to have unique values in the join column.If both inputs can have duplicates, SQL Server can't process a merge jon by making only one pass through each table,so the optimizer usually choose a nested-loop join instead.
HASH JOIN
SQL Server most often uses hash joins when no useful indexs for joining the tables exists. When performing a hash join on two tables, SQL Server uses one tables (called the build input) to build the hash buckets, each of which contains all the existing data values that generate the same value when the hash function is applied to them. Then, it inspects the other table(called the probe input) one row at a time an tries to find matching values in the hash buckets.
EXECUTE ORDER
The optimizer evaluates possible plans from the simplest to more complex plansThe simplest plan is to use the "default" nested-loop join ,so the optimizer evalueates that type first.
If neither nested-loop join nor merge joins will give good performance, the optimizer considers hash joins.Scans Versus Seeks
Don’t mistake Index Scans for Index Seeks
Seek is the only efficient strategy for finding individual rows or a range of qualifying rows Scan generally involves reading all rows of an index and is expensive for large tables unless you really need most of them
If a table has a Clustered Index, a ‘Table’ scan will always be shown as a Clustered Index Scan