ASK TOM
Code Listing 4: Factored subquery solution
with data1 as
(
select row_number()over(order by study_site) as rno,
study_site Site_no, cnt tot_rec from t
)
,
rec (rno, Site_no, tot_rec, Total, flg) as
(
select rno, Site_no, tot_rec, tot_rec, 0
from data1 where rno= 1
union all
select d.rno, d.Site_no, d.tot_rec,
case when r.total + d.tot_rec > 65000
Then d.tot_rec
Else r.total + d.tot_rec END,
case when r.total + d.tot_rec > 65000
Then r.flg+ 1
Else r.flg END
from data1 d, rec r
where d.rno=r.rno+ 1
)
select min(site_no), max(site_no), sum(tot_rec)
from rec
group by flg
order by flg
Code Listing 5: SQL MODEL clause solution
study_site tot_rec
—————— —————
1001 10000
1002 20000
1003 30500
1004 50000
1005 25000
1006 36000
1007 28000
1008 21000
I need running totals that do not exceed
65,000, and for every running total, I need
to get the starting site number, ending site
number, and the sum of records for those sites:
start_site end_site running_total
————— ————— ———————
1001 1003 60500
1004 1004 50000
1005 1006 61000
1007 1008 49000
SELECT s, MAX(e), MAX(sm) FROM (
SELECT s, e, cnt, sm FROM t
MODEL DIMENSION BY(row_number()
over(order by study_site) rn)
MEASURES(study_site s, study_site e, cnt, cnt sm)
RULES(sm[rn > 1] =
CASE WHEN (sm[cv() - 1] + cnt[cv()]) > 20000
OR cnt[cv()] > 20000
THEN cnt[cv()]
ELSE sm[cv() - 1] + cnt[cv()]
END,
s[rn > 1] =
CASE WHEN(sm[cv() - 1] + cnt[cv()]) > 20000
OR cnt[cv()] > 20000
THEN s[cv()]
ELSE s[cv() - 1]
END))
GROUP BY s ORDER BY s;
managing many queries attempting to
run concurrently, with the end result that
queuing provides the best overall runtimes,
from an average, minimum, and maximum
runtime perspective.
So, in short, the main concepts you need
to unlearn for Oracle Exadata are
•;How;you;approach;data;loads. Slow-by-slow mechanisms that worked for thousands or a few million rows simply do not
scale up and work for tens of millions or
billions of rows.
•;What;tools;you;use. It’s the twenty-first
century—you need to reevaluate your
toolset if it dates back to the 1900s.
GRoupinG;RanGes
I need to write a SQL statement to group data
in ranges. Here is the data:
As you can see, sites 1001, 1002, and 1003
form the first group and site 1004 starts the
second group in my output. (If site 1004
remained in the first group, the running total
would have been 110,500, which exceeds
65,000, so the first group is full and I start
the second group. The second group is 1004
all by itself because if I had put 1005 in
with it, the running total would have again
exceeded 65,000.)
