1 / 27

The Model Clause

The Model Clause. A case study at E.On. Introduction. Oliver.Hayden@eon.com. A little about me, the company I work for, the team I work in and today’s presentation. A brief introduction to the model clause. Example statement:. select empno , ename , sal from emp where deptno = 10

elijah
Download Presentation

The Model Clause

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Model Clause A case study at E.On

  2. Introduction Oliver.Hayden@eon.com A little about me, the company I work for, the team I work in and today’s presentation.

  3. A brief introduction to the model clause • Example statement: select empno, ename, sal from emp where deptno = 10 MODEL dimension by (empno) measures (ename, sal) rules () / http://docs.oracle.com/cd/B28359_01/server.111/b28313/sqlmodel.htm

  4. Scenario 1: Quarterly Critical Patch Updates

  5. create table lod (sid varchar2(20), host varchar2(20), dbrole varchar2(10)); insert into lod values ('orcl1','box1','prmy'); insert into lod values ('orcl1','box3','stby'); insert into lod values ('orcl2','box3','prmy'); insert into lod values ('orcl2','box4','stby'); insert into lod values ('orcl3','box4','prmy'); insert into lod values ('orcl4','box4','stby'); insert into lod values ('orcl4','box5','prmy'); insert into lod values ('orcl5','box2','prmy'); insert into lod values ('orcl5','box1','stby'); insert into lod values ('orcl6','box6','prmy'); insert into lod values ('orcl7','box7','prmy'); insert into lod values ('orcl7','box6','stby'); insert into lod values ('orcl8','box8','prmy'); commit; var n number begin select count(*) into :n from lod; end; /

  6. select clump, sid, host, dbrole, rn from lod model dimension by (sid, host, dbrole) measures (rownum as clump,rownum as rn) rules iterate(10000) until (iteration_number =:n) ( --update clumps for the hosts to the lowest value currently for identical hosts clump[any,host,any]= least ( nvl(min(clump) over (partition by host order by clump rows between unbounded preceding and unbounded following),:n) ) , --update clump for the sids to be the lowest of current clump values clump[any,host,any]= least ( nvl(min(clump) over (partition by sid order by clump rows between unbounded preceding and unbounded following),:n) ) ) /

  7. Intermediate step.

  8. Intermediate step.

  9. Intermediate step.

  10. Final output.

  11. The previous example was simplified, here we also take into account Oracle version alter table lod add (version varchar2(20) default '10g'); update lod set version ='11g' where sid = 'orcl2'; commit;

  12. select version, clump, sid, host, dbrole, rn from lod MODEL partition by (version) dimension by (sid, host, dbrole) measures (rownum as clump,rownum as rn) rules iterate(10000) until (iteration_number =:n) ( --update clumps for the hosts to the lowest value currently for identical hosts clump[any,host,any]= least ( nvl(min(clump) over (partition by host order by clump rows between unbounded preceding and unbounded following),:n) ) , --update clump for the sids to be the lowest of current clump values clump[any,host,any]= least ( nvl(min(clump) over (partition by sid order by clump rows between unbounded preceding and unbounded following),:n) ) ) order by clump /

  13. Scenario 2: Missing data in a time-series Original requirement arose during a project to investigate whether objects are being used.

  14. desc dbm_apps.monitor_usage_aud@gfcspw01

  15. with data as (select day as orig_day, value ,day start_dt , nvl(lag(day) over (order by day desc nulls last),day)-1 end_dt from (select trunc(datefield) as day, count(*) as value from dbm_apps.monitor_usage_aud@gfcspw01 group by trunc(datefield)) order by day ) select value as quantity,s2 as time_period from data MODEL partition by (orig_day, start_dt, end_dt) dimension by (0 as z) measures (1 x,value, start_dt s2, end_dt e2) rules sequential order iterate (5) until ( previous(s2[ITERATION_NUMBER]) >= previous(e2[ITERATION_NUMBER]) ) ( x[ITERATION_NUMBER]=ITERATION_NUMBER ,s2[ITERATION_NUMBER]=cv(start_dt)+ITERATION_NUMBER ,e2[ITERATION_NUMBER]=cv(end_dt) ) order by s2, value

  16. Scenario 3: team rota There is a need for our team assignments to be easily visible to both ourselves and our customers. We also need to make sure that resource has been assigned.

  17. create table pp_rota (DUTY VARCHAR2(20) ,PERSON NOT NULL VARCHAR2(20) ,WHEN DATE) /

  18. select when , duty||'.'||person, duty, decode(person,'MISSING',null,person) as real_person from pp_rota model partition by (when) dimension by (duty) measures (person) rules ( person['livebuild']=nvl(person[cv(duty)],'MISSING') ,person['backflush']=nvl(person[cv(duty)],'MISSING') ,person['systest']=nvl(person[cv(duty)],'MISSING') ,person['ep']=nvl(person[cv(duty)],'MISSING') ) order by when, duty

  19. Wrap up...

More Related