需要一些严重的帮助与自我加入问题 [英] Need some serious help with self join issue

问题描述

你可能知道，你不能用自联接来索引视图。实际上甚至连同一个表的两个连接，即使它在技术上不是自连接。一对夫妇从微软想出了一个工作。但它太复杂了我不明白!!!

问题的解决方案在这里： http://jmkehayias.blogspot.com/2008/12/creating-indexed-view-with-self- join.html

我想要应用此工作的视图为：

  create VIEW vw_lookup_test 
 WITH SCHEMABINDING 
 AS 
 select 
 count_big（*）as [count_all]，
 awc_txt，
 city_nm，
 str_nm，
 stru_no，
 o.circt_cstdn_nm [owner]，
 t.circt_cstdn_nm [tech]，
 dvc.circt_nm，
 data_orgtn_yr 
从
（dbo.dvc 
加入dbo.circt 
在dvc.circt_nm = circt.circt_nm）
在circt上加入dbo.circt_cstdn o 
。 circt_cstdn_user_id）
 join dbo.circt_cstdn t 
 on dvc.circt_cstdn_user_id = t.circt_cstdn_user_id 
 group by 
 awc_txt，
 city_nm，
 str_nm ，
 stru_no，
 o.circt_cstdn_nm，
 t.circt_cstdn_nm，
 dvc.circt_nm，
 data_orgtn_yr 
 go 
  
 
 
 
任何帮助都会非常遗憾！
 
 
 
 
 
 
 
编辑：所以我发现这也将工作。注意，我在第一个索引视图中加入表一次，第二次在第二个非索引视图中加入表。
  alter VIEW vw_lookup_owner_test2 
 WITH SCHEMABINDING 
 AS 
 select 
 count_big（*）as [countAll]，
 awc_txt，
 city_nm，
 str_nm， 
 stru_no，
 dvc.circt_nm，
 circt_cstdn_nm，
 data_orgtn_yr，
 dvc.circt_cstdn_user_id 
来自dbo.dvc 
 join dbo.circt 
 on dvc.circt_nm = circt.circt_nm 
 join dbo.circt_cstdn o 
 on circt.circt_cstdn_user_id = o.circt_cstdn_user_id 
 group by 
 awc_txt，
 city_nm， 
 str_nm，
 stru_no，
 dvc.circt_nm，
 circt_cstdn_nm，
 data_orgtn_yr，
 dvc.circt_cstdn_user_id 
 go 
  
和
  CREATE UNIQUE CLUSTERED INDEX [idx_vw_lookup_owner2_test1] ON [dbo]。[vw_lookup_owner_test2] 
（
 [awc_txt] ASC，
 [city_nm] ASC，
 [str_nm] ASC，
 [ ASC，
 [circt_cstdn_user_id] ASC 
）WITH（PAD_INDEX = OFF，ASC，
 [circt_cstdn_user]] ASC，
 [circt_cstdn_user] STATISTICS_NORECOMPUTE = OFF，SORT_IN_TEMPDB = OFF，IGNORE_DUP_KEY = OFF，DROP_EXISTING = OFF，ONLINE = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY] 
 GO 
  pre> 
 
 
和
  create view vw_lookup_dvc_loc 
 as 
 select 
 awc_txt，
 city_nm，
 str_nm，
 stru_no，
 circt_nm，
 o.circt_cstdn_nm as [owner]，
 --o.circt_cstdn_user_id，
 t.circt_cstdn_nm as tech，
 data_orgtn_yr 
来自vw_lookup_owner_test2 o使用（NOEXPAND）
在o.circt_cstdn_user_id = t上连接circt_cstdn t 
。 circt_cstdn_user_id 
 group by 
 awc_txt，
 city_nm，
 str_nm，
 stru_no，
 circt_nm，
 o.circt_cstdn_nm，
 data_orgtn_yr ，
 t.circt_cstdn_nm 
 --o.circt_cstdn_user_id 
  
我希望在第一个视图上添加索引。我不知道这个解决方案（或解决方案）是否真的会加速生产，但我会让你知道。
解决方案
这是我从blogpost获得的
 
 
 
 
 
 
假设你想在dbo.circt_cstdn上加入2次，想要像
  owner tech 
 rowA a.nm b.nm 
 ... 
  
 
 
 
 
而不是将值转换为2列，并添加一个附加列来说明哪个行是哪个列。请注意，行1.1和行1.2具有相同的数据（除了名称和列）
 名称为
 row1.1 nm owner 
 row1.2 nm tech 
 ... 
  
 
 
 
 
然后，您可以围绕所有者和技术的最大名称列。注意 - 最大的函数只是欺骗PIVOT（需要一些聚合函数），你可以使用任何聚合函数返回相同的值，如果只有一个记录
 owner tech 
 row1 nm nm 
 ... 
 
 
 
 
 
 
现在，如果我们为您的查询执行此操作
 
 
 
创建一个表d，像这样一个
  i 
 1 
 2 
  
 
 
 
 
将查询的第一部分
  SELECT 
 count_big（*）as [count_all]，
 awc_txt，
 city_nm ，
 str_nm，
 stru_no，
 dvc.circt_nm，
 data_orgtn_yr 
 FROM 
 dbo.dvc 
 INNER在dvc上加入dbo.circt。 circt_nm = circt.circt_nm 
 CROSS JOIN dbo.d 
 GROUP BY 
 awc_txt，city_nm，str_nm，stru_no，dvc.circt_nm，data_orgtn_yr，di 
  
 
 
 
 
现在，如果Di为1，则使用所有者的行，如果Di为2，则使用tech 
  SELECT 
 count_big（*）as [count_all]，
 awc_txt，
 city_nm，
 str_nm，
 stru_no，
 dvc.circt_nm，
 data_orgtn_yr，
 case 
 WHEN di = 1 THEN'Owner'
 WHEN di = 2 THEN'Tech'
 END 
 FROM 
 dbo.dvc 
 INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm 
 CROSS JOIN dbo.d 
 GROUP BY 
 awc_txt， city_nm，str_nm，stru_no，dvc.circt_nm，data_orgtn_yr，
 case 
 WHEN di = 1 THEN'Owner'
 WHEN di = 2 THEN'Tech'
 END 
  
 
 
 
 
现在添加nm列。如果它是一个所有者行（d.i = 1）和dvc，如果它是一个技术行（d.i = 2），获取您加入circt_cstdn与circt的名称。注意 - 我在这里尝试了一个快捷方式，将此放在连接条件。如果它不工作尝试博客方式（在circt.circt_cstdn_user_id 或 dvc.circt_cstdn_user_id上执行连接，然后使用WHERE子句过滤掉）
  SELECT 
 count_big（*）as [count_all]，
 awc_txt，
 city_nm，
 str_nm，
 stru_no，
 dvc.circt_nm，
 data_orgtn_yr，
 case 
 WHEN di = 1 THEN'Owner'
 WHEN di = 2 THEN'Tech'
 END as PersonType，
 circt_cstdn_nm 
 FROM 
 dbo.dvc 
 INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm 
 CROSS JOIN dbo.d 
 INNER JOIN dbo.circt_cstdn on circt_cstdn_user_id = 
 CASE 
 WHEN di = 1 THEN circt.circt_cstdn_user_id 
 WHEN di = 2 THEN dvc.circt_cstdn_user_id 
 END 
 GROUP BY 
 awc_txt，city_nm，str_nm，stru_no，dvc.circt_nm，data_orgtn_yr，
 case 
 WHEN di = 1 THEN'Owner'
 WHEN di = 2 THEN'Tech'
 END，
 circt_cstdn_nm 
  
 
 
 
 
使用该视图创建视图并创建索引
 
 
 
  create VIEW vw_lookup_test_imed 
 WITH SCHEMABINDING 
 AS 
<< query above> 
 GO 
 
拼写创建INDEX 
  
 
 
 
您PIVOT将PersonType列转换为Owner和Tech列
  SELECT 
 count_all，
 awc_txt，
 city_nm，
 str_nm，
 stru_no，
 dvc.circt_nm，
 data_orgtn_yr，
 [Owner]，
 [Tech] 
 FROM 
（
 SELECT 
 count_all，
 awc_txt，
 city_nm，
 str_nm，
 stru_no，
 dvc.circt_nm，
 data_orgtn_yr，
 PersonType，
 circt_cstdn_nm 
 FROM dbo.vw_lookup_test_imed WITH（NOEXPAND）
）src 
 PIVOT 
（
 MAX circt_cstdn_nm）
 for PersonType IN（[Owner]，[Tech]）
）pvt 
  
 
 
 
如果有语法错误（必须有很多原因，我现在没有访问数据库）。让我知道。
 
Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from microsoft came up with a work around. But it's so complicated I don't understand it!!!

The solution to the problem is here: http://jmkehayias.blogspot.com/2008/12/creating-indexed-view-with-self-join.html

The view I want to apply this work around to is:
create VIEW vw_lookup_test
WITH SCHEMABINDING
AS
select
count_big(*) as [count_all],
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm [owner],
t.circt_cstdn_nm [tech],
dvc.circt_nm,
data_orgtn_yr 
from 
((dbo.dvc 
join dbo.circt 
on dvc.circt_nm = circt.circt_nm) 
join dbo.circt_cstdn o
on circt.circt_cstdn_user_id = o.circt_cstdn_user_id)
join dbo.circt_cstdn t
on dvc.circt_cstdn_user_id = t.circt_cstdn_user_id
group by
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm,
t.circt_cstdn_nm,
dvc.circt_nm,
data_orgtn_yr 
go
Any help would be greatly apreciated!!!

Thanks so much in advance!

EDIT : So I found that this will also work. Notice that I join to the table once in the first indexed view, and the second time in teh second non-indexed view.
alter VIEW vw_lookup_owner_test2
WITH SCHEMABINDING  
AS 
select
count_big(*) as [countAll],
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
circt_cstdn_nm,
data_orgtn_yr,
dvc.circt_cstdn_user_id
from dbo.dvc 
join dbo.circt
on dvc.circt_nm = circt.circt_nm
join dbo.circt_cstdn o
on circt.circt_cstdn_user_id = o.circt_cstdn_user_id
group by
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
circt_cstdn_nm,
data_orgtn_yr,
dvc.circt_cstdn_user_id
go
and 
CREATE UNIQUE CLUSTERED INDEX [idx_vw_lookup_owner2_test1] ON [dbo].[vw_lookup_owner_test2] 
(
    [awc_txt] ASC,
    [city_nm] ASC,
    [str_nm] ASC,
    [stru_no] ASC,
    [circt_nm] ASC,
    [circt_cstdn_nm] ASC,
    [data_orgtn_yr] ASC,
    [circt_cstdn_user_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
and 
create view vw_lookup_dvc_loc
as
select
awc_txt,
city_nm,
str_nm,
stru_no,
circt_nm,
o.circt_cstdn_nm as [owner],
--o.circt_cstdn_user_id,
t.circt_cstdn_nm as tech,
data_orgtn_yr
from vw_lookup_owner_test2 o With (NOEXPAND)
join circt_cstdn t
on o.circt_cstdn_user_id = t.circt_cstdn_user_id
group by
awc_txt,
city_nm,
str_nm,
stru_no,
circt_nm,
o.circt_cstdn_nm,
data_orgtn_yr,
t.circt_cstdn_nm
--o.circt_cstdn_user_id
I can then create additon indexes on the first view as I wish. I'm not sure if this solution (or the workaround for that matter) will actually speed up preformance but i'll let you know.
解决方案
Here's what I got from the blogpost


Let's say you want to join 2 times on dbo.circt_cstdn i.e. you want something like
         owner       tech
rowA     a.nm        b.nm
...

Instead of getting the values into 2 columns you get it into 2 rows (2 for each row above) and add an additional column to say which row is for which column. Note that row 1.1 and row 1.2 have the same data (except for the name and for columns)
         name   for
row1.1   nm     owner
row1.2   nm     tech
...

Then you pivot on max of name column for owner and tech. Note - the max function is just to trick the PIVOT (which requires some aggregate function), you could use any aggregate function that returns the same value if there is only one record
      owner    tech
row1       nm       nm
...


Now if we do this for your query

Create a table d, like this one
 i
 1
 2

Cross join the first part of your query with this
SELECT 
     count_big(*) as [count_all], 
     awc_txt, 
     city_nm, 
     str_nm, 
     stru_no, 
     dvc.circt_nm, 
     data_orgtn_yr
FROM  
     dbo.dvc  
     INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
     CROSS JOIN dbo.d  
GROUP BY
     awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, d.i

Now let's use the row for owner if D.i is 1, and the tech if D.i is 2
SELECT 
     count_big(*) as [count_all], 
     awc_txt, 
     city_nm, 
     str_nm, 
     stru_no, 
     dvc.circt_nm, 
     data_orgtn_yr,
     Case 
         WHEN d.i = 1 THEN 'Owner'
         WHEN d.i = 2 THEN 'Tech'
     END
FROM  
     dbo.dvc  
     INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
     CROSS JOIN dbo.d 
GROUP BY
     awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, 
     Case 
         WHEN d.i = 1 THEN 'Owner'
         WHEN d.i = 2 THEN 'Tech'
     END  

Now add the nm column. To get the name you join circt_cstdn with circt if it's an owner row (d.i = 1), and with dvc if it's a tech row (d.i = 2). Note - I tried a shortcut here by putting this in the join condition. If it doesn't work try the blog post way (do a join on circt.circt_cstdn_user_id OR dvc.circt_cstdn_user_id, and then use the WHERE clause to filter out)
SELECT 
     count_big(*) as [count_all], 
     awc_txt, 
     city_nm, 
     str_nm, 
     stru_no, 
     dvc.circt_nm, 
     data_orgtn_yr,
     Case 
         WHEN d.i = 1 THEN 'Owner'
         WHEN d.i = 2 THEN 'Tech'
     END as PersonType,
     circt_cstdn_nm
FROM  
     dbo.dvc  
     INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
     CROSS JOIN dbo.d 
     INNER JOIN dbo.circt_cstdn on circt_cstdn_user_id = 
          CASE
               WHEN d.i = 1 THEN circt.circt_cstdn_user_id
               WHEN d.i = 2 THEN dvc.circt_cstdn_user_id
          END
GROUP BY
     awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, 
     Case 
         WHEN d.i = 1 THEN 'Owner'
         WHEN d.i = 2 THEN 'Tech'
     END,
     circt_cstdn_nm

Create a view using that and create the index
create VIEW vw_lookup_test_imed
WITH SCHEMABINDING 
AS
    <<query above>>  
GO

spell to create INDEX

Now you PIVOT to convert the PersonType column to Owner and Tech columns
SELECT 
     count_all, 
     awc_txt, 
     city_nm, 
     str_nm, 
     stru_no, 
     dvc.circt_nm, 
     data_orgtn_yr,
     [Owner], 
     [Tech] 
FROM 
( 
     SELECT 
          count_all, 
          awc_txt, 
          city_nm, 
          str_nm, 
          stru_no, 
          dvc.circt_nm, 
          data_orgtn_yr,
          PersonType,
          circt_cstdn_nm
     FROM dbo.vw_lookup_test_imed WITH (NOEXPAND) 
) src 
PIVOT 
( 
     MAX(circt_cstdn_nm) 
     FOR PersonType IN ([Owner], [Tech]) 
) pvt 

If there are syntax errors (there's bound to be lots cause I don't have access to a database right now) let me know.

                        
这篇关于需要一些严重的帮助与自我加入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！