|
Долгое создание MATERIALIZED VIEW
#39287288
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
1.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Ну очень долго создается MATERIALIZED VIEW...
Хотя запрос в ней выполняется всего 3-4 сек.
MATERIALIZED VIEW 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
CREATE MATERIALIZED VIEW ZAGS_DATA1.V_PEOPLE_ZAGS (GUID,ID,SURNAME,NAME,PATRONYMIC,ID_PEOPLE_INFO,ID_GENDER
,DAY_BIRTH,MONTH_BIRTH,YEAR_BIRTH,ID_LOCATION_BORN,ID_LOCATION,ID_TYPE_IDENT_DOC,SERIES_IDENT_DOC,NUMB_IDENT_DOC)
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT people.guid, people.ID, people.SURNAME, people.NAME, people.PATRONYMIC, people.ID_PEOPLE_INFO,
people.ID_GENDER, people.DAY_BIRTH, people.MONTH_BIRTH, people.YEAR_BIRTH, people.ID_LOCATION AS id_location_born,
ppl.ID_LOCATION, pdoc.ID_TYPE_IDENT_DOC, pdoc.SERIES_IDENT_DOC, pdoc.NUMB_IDENT_DOC
FROM
(
SELECT people_all.guid, people_all.ID, people_all.SURNAME, people_all.NAME, people_all.PATRONYMIC,
people_all.ID_PEOPLE_INFO, people_all.ID_GENDER, people_all.DAY_BIRTH, people_all.MONTH_BIRTH,
people_all.YEAR_BIRTH, people_all.ID_LOCATION
FROM
(
SELECT lpp.guid, PP.ID, PP.SURNAME, PP.NAME, PP.PATRONYMIC, PP.ID_PEOPLE_INFO, PP.ID_ROLES_PEOPLE,
PP.DATE_REC, RP.FLAG_PROTAGONIST, PP.ID_GENDER, PI.DAY_BIRTH, PI.MONTH_BIRTH, PI.YEAR_BIRTH, PL.ID_LOCATION,
COUNT (pp.id) OVER (PARTITION BY lpp.guid) AS count_people,
MAX (pp.id) OVER (PARTITION BY lpp.guid, RP.FLAG_PROTAGONIST) AS max_id_pro,
COUNT (DISTINCT RP.FLAG_PROTAGONIST) OVER (PARTITION BY lpp.guid) AS cound_distinct_pro
FROM t_people pp
INNER JOIN LNK_PEOPLES_GUID lpp ON PP.ID_UNIQ = LPp.ID_PEOPLE
LEFT JOIN t_people_info pi ON PP.ID_PEOPLE_INFO = pi.id
LEFT JOIN LNK_PEOPLES_LOCATION pl ON PL.ID_PEOPLE = PP.ID AND PL.ID_TYPE_LOCATION = 1
LEFT JOIN SP_ROLES_PEOPLE rp ON rp.id = PP.ID_ROLES_PEOPLE
INNER JOIN
(
SELECT LP.GUID, MAX(TRUNC (p.date_az)) mday
FROM LNK_PEOPLES_GUID lp
INNER JOIN t_people p ON LP.ID_PEOPLE = P.ID_UNIQ AND P.FLAG_VALID = 1
GROUP BY LP.GUID
) max_day
ON PP.DATE_AZ >= max_day.mday AND LPP.GUID = max_day.guid
) people_all
WHERE cound_distinct_pro <= 1 AND people_all.id = people_all.max_id_pro
UNION
SELECT people_all.guid, people_all.ID, people_all.SURNAME, people_all.NAME, people_all.PATRONYMIC, people_all.ID_PEOPLE_INFO, people_all.ID_GENDER,
people_all.DAY_BIRTH, people_all.MONTH_BIRTH, people_all.YEAR_BIRTH, people_all.ID_LOCATION
FROM
(
SELECT lpp.guid, PP.ID, PP.SURNAME, PP.NAME, PP.PATRONYMIC, PP.ID_PEOPLE_INFO, PP.ID_ROLES_PEOPLE,
PP.DATE_REC, RP.FLAG_PROTAGONIST, PP.ID_GENDER, PI.DAY_BIRTH, PI.MONTH_BIRTH, PI.YEAR_BIRTH, PL.ID_LOCATION,
COUNT (pp.id) OVER (PARTITION BY lpp.guid) AS count_people,
MAX (pp.id) OVER (PARTITION BY lpp.guid, RP.FLAG_PROTAGONIST) AS max_id_pro,
COUNT (DISTINCT RP.FLAG_PROTAGONIST) OVER (PARTITION BY lpp.guid) AS cound_distinct_pro
FROM t_people pp
INNER JOIN LNK_PEOPLES_GUID lpp ON PP.ID_UNIQ = LPp.ID_PEOPLE
LEFT JOIN t_people_info pi ON PP.ID_PEOPLE_INFO = pi.id
LEFT JOIN LNK_PEOPLES_LOCATION pl ON PL.ID_PEOPLE = PP.ID AND PL.ID_TYPE_LOCATION = 1
LEFT JOIN SP_ROLES_PEOPLE rp ON rp.id = PP.ID_ROLES_PEOPLE
INNER JOIN
(
SELECT LP.GUID, MAX(TRUNC (p.date_az)) mday
FROM LNK_PEOPLES_GUID lp
INNER JOIN t_people p ON LP.ID_PEOPLE = P.ID_UNIQ AND P.FLAG_VALID = 1
GROUP BY LP.GUID
) max_day
ON PP.DATE_AZ >= max_day.mday AND LPP.GUID = max_day.guid
) people_all
WHERE cound_distinct_pro > 1
AND people_all.id = people_all.max_id_pro
AND people_all.FLAG_PROTAGONIST = 1
) people
LEFT JOIN
(
SELECT lpp.guid, MAX (pl.id_location) AS id_location
FROM t_people pp
INNER JOIN LNK_PEOPLES_GUID lpp ON PP.ID_UNIQ = lpp.ID_PEOPLE
INNER JOIN
(
SELECT LP.GUID, MAX (TRUNC (p.date_az)) mday
FROM LNK_PEOPLES_GUID lp
INNER JOIN t_people p ON LP.ID_PEOPLE = P.ID_UNIQ AND P.FLAG_VALID = 1
GROUP BY LP.GUID
) max_day
ON PP.DATE_AZ >= max_day.mday AND LPP.GUID = max_day.guid
LEFT JOIN LNK_PEOPLES_LOCATION pl ON PL.ID_PEOPLE = PP.ID AND PL.ID_TYPE_LOCATION = 2
GROUP BY lpp.guid
) ppl ON ppl.guid = people.guid
LEFT JOIN
(
SELECT lpp.guid, MAX (pd.ID) AS id_doc
FROM t_people pp
INNER JOIN LNK_PEOPLES_GUID lpp ON PP.ID_UNIQ = lpp.ID_PEOPLE
LEFT JOIN t_people_info pi ON PP.ID_PEOPLE_INFO = pi.id
LEFT JOIN T_PEOPLE_DOC pd ON pi.ID_DOC_PASSPORT = pd.ID
WHERE ID_TYPE_IDENT_DOC IS NOT NULL
AND SERIES_IDENT_DOC IS NOT NULL
AND NUMB_IDENT_DOC IS NOT NULL
GROUP BY lpp.guid
) ppd ON ppd.guid = people.guid
LEFT JOIN T_PEOPLE_DOC pdoc ON ppd.id_doc = pdoc.ID
;
В чем может быть причина ? |
|
|
