Geek asks and answers

How does this query generate two join statements in the execution plan?

Table structure with a clustered unique index on empno.

CREATE TABLE [dbo].[EMP](
    [EMPNO] [int] NOT NULL,
    [ENAME] [varchar](10) NULL,
    [JOB] [varchar](9) NULL,
    [MGR] [int] NULL,
    [HIREDATE] [datetime] NULL,
    [SAL] [int] NULL,
    [COMM] [int] NULL,
    [DEPTNO] [int] NULL
) ON [PRIMARY]

Inquiry

SELECT sal,sum(sal) over(PARTITION BY empno)
FROM emp

Request Plan

enter image description here

+5
source share
2 answers

In plans with window units, a common sub-buffer coil is usually used. A good record of this type of plan is here. Separation and general sub-expression of Spool

Suppose the table has the following rows

CREATE TABLE [dbo].[EMP](
    [EMPNO] [int] NOT NULL,
    [SAL] [int] NULL) 

INSERT INTO [dbo].[EMP] 
VALUES (1,1),
       (1,2), 
       (1,3),
       (1,4), 
       (2,1),
       (2,2)

It has a total of 6 lines with 2 different values EMPNO. The actual execution plan showing the actual number of lines emitted is below.

enter image description here

, , , (.. EMPNO).

( ) tempdb. , , .

( ), SUM([SAL]), ( ), .

, , 3 ( )

+3

OVER : FROM table AS x INNER JOIN (SELECT , AggregateWithoutOverClause (...)... FROM...) AS y ON x.PartitionColumns = y.PartitionColumns( - NOT NULL).

:

SET STATISTICS IO ON;
SET NOCOUNT ON;

-- OP query
SELECT sal,sum(sal) over(PARTITION BY empno) 
FROM emp;

-- Reqwriten query
SELECT a.sal, b.SumSal
FROM emp a
INNER JOIN (SELECT EMPNO, SUM(sal) AS SumSal FROM emp GROUP BY EMPNO) b ON a.EMPNO = b.EMPNO;

:

sal         
----------- -----------
1           10
2           10
3           10
4           10
1           3
2           3

Table 'Worktable'. Scan count 3, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'EMP'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

sal         SumSal
----------- -----------
1           10
2           10
3           10
4           10
1           3
2           3

Table 'Worktable'. Scan count 3, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'EMP'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

: enter image description here

: enter image description here

/ " " .

0

More articles:


All Articles