SQL Server/T-SQL Tutorial/System Settings/SHOWPLAN TEXT

Материал из SQL эксперт
Перейти к: навигация, поиск

Show Join hints for INNER HASH JOIN

   <source lang="sql">

4> 5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20) 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> select * from employee; 2> GO id first_name last_name salary start_Date region city


---------- ---------- ------------ ----------------------- ---------- --------------------
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto

(9 rows affected) 1> 2> CREATE TABLE title( 3> id INTEGER, 4> job_title VARCHAR(20) 5> ); 6> GO 1> INSERT INTO title VALUES (1, "developer"); 2> INSERT INTO title VALUES (1, "manager"); 3> INSERT INTO title VALUES (2, "tester"); 4> INSERT INTO title VALUES (2, "programmer"); 5> INSERT INTO title VALUES (3, "boss"); 6> INSERT INTO title VALUES (4, "sales"); 7> INSERT INTO title VALUES (5, "market"); 8> INSERT INTO title VALUES (6, "coder"); 9> INSERT INTO title VALUES (7, "tester"); 10> INSERT INTO title VALUES (8, "developer"); 11> INSERT INTO title VALUES (9, "manager"); 12> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SET SHOWPLAN_TEXT ON 4> GO 1> SELECT p.first_Name 2> FROM employee p 3> INNER HASH JOIN title r ON 4> r.ID = p.ID 5> GO Warning: The join order has been enforced because a local join hint is used. StmtText


SELECT p.first_Name FROM employee p INNER HASH JOIN title r ON r.ID = p.ID (1 rows affected) StmtText



 |--Hash Match(Inner Join, HASH:([p].[id])=([r].[id]), RESIDUAL:([master].[dbo].[employee].[id] as [p].[id]=[master].[d

bo].[title].[id] as [r].[id]))

      |--Clustered Index Scan(OBJECT:([master].[dbo].[employee].[PK__employee__03DB89B3] AS [p]))
      |--Table Scan(OBJECT:([master].[dbo].[title] AS [r]))

(3 rows affected) 1> 2> 3> SET SHOWPLAN_TEXT OFF 4> GO 1> 2> 3> 4> drop table employee; 5> drop table title; 6> GO 1></source>


Using Join Hints

   <source lang="sql">

3> 4> 5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20) 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> select * from employee; 2> GO id first_name last_name salary start_Date region city


---------- ---------- ------------ ----------------------- ---------- --------------------
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto

(9 rows affected) 1> 2> CREATE TABLE title( 3> id INTEGER, 4> job_title VARCHAR(20) 5> ); 6> GO 1> INSERT INTO title VALUES (1, "developer"); 2> INSERT INTO title VALUES (1, "manager"); 3> INSERT INTO title VALUES (2, "tester"); 4> INSERT INTO title VALUES (2, "programmer"); 5> INSERT INTO title VALUES (3, "boss"); 6> INSERT INTO title VALUES (4, "sales"); 7> INSERT INTO title VALUES (5, "market"); 8> INSERT INTO title VALUES (6, "coder"); 9> INSERT INTO title VALUES (7, "tester"); 10> INSERT INTO title VALUES (8, "developer"); 11> INSERT INTO title VALUES (9, "manager"); 12> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SET SHOWPLAN_TEXT ON 3> GO 1> 2> 3> SELECT p.first_Name, 4> r.job_title 5> FROM employee p 6> INNER JOIN title r ON 7> r.ID = p.ID 8> GO StmtText


SELECT p.first_Name, r.job_title FROM employee p INNER JOIN title r ON r.ID = p.ID (1 rows affected) StmtText



 |--Nested Loops(Inner Join, OUTER REFERENCES:([r].[id]))
      |--Table Scan(OBJECT:([master].[dbo].[title] AS [r]))
      |--Clustered Index Seek(OBJECT:([master].[dbo].[employee].[PK__employee__00FF1D08] AS [p]), SEEK:([p].[id]=[maste

r].[dbo].[title].[id] as [r].[id]) ORDERED FORWARD) (3 rows affected) 1> 2> 3> SET SHOWPLAN_TEXT OFF 4> GO 1> 2> drop table employee; 3> drop table title; 4> GO 1></source>