-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathinstructions.sql
196 lines (195 loc) · 6.65 KB
/
instructions.sql
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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
--select count(*) from posts
--select count(*) from comments
--select * from user_sys_privs;
--select * from dba_tab_privs
--select * from dba_role_privsv
----------------------------------
--explain plan set statement_id='q1' for
--set autotrace on
--create materialized view vq1
--build immediate
--refresh complete on demand enable query rewrite
--as
select
u.id,
badgecount,
questioncount,
round(cast(badgecount as float)/questioncount, 2) as ratio
from users u
inner join (
select userid, count(id) as badgecount
from badges
where upper(name) like '%popular question%'
group by userid
) pop on u.id = pop.userid
inner join (
select owneruserid, count(id) as questioncount
from posts
where posttypeid = 1
group by owneruserid
) q on u.id = q.owneruserid
where badgecount >= 10
order by ratio desc
--select * from plan_table where statement_id='q1'
--set autot on exp
--set autotrace on stat
--select plan_table_output from table(dbms_xplan.display());
--create bitmap index idx_posts_type_id on posts(posttypeid)
--alter session set query_rewrite_integrity = trusted;
--alter session set query_rewrite_enabled = true;
------------------------------------ok
--explain plan set statement_id='q2' for
--set autotrace on
select id,
(select count(*) from posts
where
posttypeid = 1 and
lasteditoruserid = u.id and
owneruserid != u.id ) questionedits,
( select count(*) from posts
where
posttypeid = 2 and
lasteditoruserid = u.id and
owneruserid != u.id) answeredits,
(select count(*) from posts
where
lasteditoruserid = u.id and
owneruserid != u.id) totaledits
from users u
order by totaledits desc
--select * from plan_table where statement_id='q2'
--set autot on exp
--set autotrace on stat
--create index idx_posts_last_editor on posts(lasteditoruserid)
--create index idx_posts_owner_user on posts(owneruserid)
--create index idx_posts_owner_last_user_type on posts(lasteditoruserid,owneruserid,posttypeid)
--------------------------------------ok
--explain plan set statement_id='q3' for
--set autotrace on
--create materialized view vq3
--build immediate
--refresh complete on demand enable query rewrite
--as
select u.id,
round((cast(count(a.id) as float) / cast((select count(*) from posts p where p.owneruserid = u.id and posttypeid = 1) as float) * 100),2) as selfanswerpercentage
from posts q
inner join posts a on q.acceptedanswerid = a.id
inner join users u on u.id = q.owneruserid
where q.owneruserid = a.owneruserid
group by u.id, displayname
having count(a.id) > 1
order by selfanswerpercentage desc
--select * from plan_table where statement_id='q3'
--set autot on exp
--set autotrace on stat
--create index idx_posts_accept_answer on posts(acceptedanswerid)
-------------------------------------- ok
--explain plan set statement_id='q4' for
--set autotrace on
--create materialized view vq4
--build immediate
--refresh complete on demand enable query rewrite
--as
select t.postid, t.upvotes, t.downvotes, p.body, p.score, p.owneruserid from (
select
postid,
sum(case when votetypeid = 2 then 1 else 0 end) as upvotes,
sum(case when votetypeid = 3 then 1 else 0 end) as downvotes
from votes where votetypeid in (2,3)
group by postid
) t inner join posts p on t.postid = p.id
where downvotes>(upvotes * 0.5)
order by upvotes desc
--select * from plan_table where statement_id='q4'
--set autot on exp
--set autotrace on stat
--create bitmap index idx_votes_type_id on votes(votetypeid)
--------------------------------------ok
--explain plan set statement_id='q5' for
--set autotrace on
--create materialized view vq5_d
--build immediate
--refresh complete on demand enable query rewrite
--as
select
p.owneruserid,
p.id,
p.score
from posts p
inner join posts q on q.id = p.parentid
where p.posttypeid = 2 and p.score > 5
and q.score > 3 and q.answercount = 1
and q.acceptedanswerid = p.id
--select * from plan_table where statement_id='q5'
--set autot on exp
--set autotrace on stat
--create index idx_posts_score on posts(score)
--create index idx_posts_parent_id on posts(parentid)
--create index idx_posts_answer_count on posts(answercount)
--create index idx_posts_score_answer_count on posts(score,answercount) -- conditions where
--drop index idx_posts_score
--drop index idx_posts_answer_count
--------------------------------------ok
--explain plan set statement_id='q6' for
--set autotrace on
--create materialized view vq6
--build immediate
--refresh complete on demand enable query rewrite
--as
select
parentid,
count(id)
from posts
where posttypeid = 2 and length(body) <= 1500
and upper(body) like '%ja%'
group by parentid
having count(id) > 1
order by count(id) desc
--select * from plan_table where statement_id='q6'
--set autot on exp
--set autotrace on stat
--create index idx_posts_len_body on posts(length(body))
--create index idx_posts_body on posts(upper(body)) --not on datatype varray, nested table, lob, ref...
--------------------------------------
--explain plan set statement_id='q7' for
--set autotrace on
--create materialized view vq7
--build immediate
--refresh complete on demand enable query rewrite
--as
select users.id,
count(posts.id) as answers,
cast(avg(cast(score as float)) as numeric(6,2)) as average_answer_scor
from
posts
inner join
users on users.id = owneruserid
where
posttypeid = 2
group by
users.id, displayname
having
count(posts.id) > 10
order by
average_answer_scor desc
--set autot on exp
------------------------------------- view exemples
create materialized view --vq0 as select count(*) from users
create materialized view --vq1 build immediate refresh complete on commit as select count(*) from comments
drop materialized view --view?
------------ create view logs : stage table
create materialized view log on --posts with rowid(answercount,score) including new values;
drop materialized view log on --posts
------------ show views and log views
select mview_name, refresh_mode, refresh_method, last_refresh_type, to_char(last_refresh_date, 'yyyy-mm-dd hh24:mi:ss') last_refresh_date from all_mviews
select * from user_mview_logs
------------ update view
exec dbms_mview.refresh('vq1')
exec dbms_mview.refresh('vq3')
exec dbms_mview.refresh('vq4')
exec dbms_mview.refresh('vq5_d')
exec dbms_mview.refresh('vq6')
exec dbms_mview.refresh('vq7')
------------ update posts score
update posts set score = score*1.5 where rownum = 200
update posts set answercount = answercount*10 where rownum = 200