A Case for Reference Models

I've used the SQL Model Clause on occasion. I've spoken on the topic as recently as last Friday to our internal development staff. I tried to stress to them that it's something that belongs in the toolbox, but it's not a tool you will pick up frequently.

One of the topics mentioned during the preso was REFERENCE MODELS. While I was aware of reference models, I never had the need to employ them.  I have no real world experience utilizing them.

Afterwards, Martin D'Souza asked for an example using REFERENCE MODELS. I was more than a little suspicious. I figured he had a requirement somewhere that he thought could best be implemented using a model clause and REFERENCE MODELS. I was right to be suspicious.

What he asked for was multiple Top-N queries within the same result set. Put another way, using the EMP table, in col A he wants the 5 highest salaries, while in col B he wants the 5 oldest dates of hire. THERE IS NO CORRELATION BETWEEN COLUMNS.

Using the MODEL clause and two REFERENCE MODELS, we were able to give him precisely what he was looking for.


select top_salary,
       recent_dates 
from dual
model 
reference t_salary on (
   select rn,sal 
     from (select ROW_NUMBER() OVER (ORDER BY sal DESC) rn,
                  sal 
             from scott.emp) 
    where rownum <=5)
   dimension by (rn)
   measures (sal)
reference t_dates on (
   select rn,hiredate 
     from (select ROW_NUMBER() OVER (ORDER BY hiredate) rn,
                  hiredate 
             from scott.emp)
    where rownum <=5)
   dimension by(rn)
   measures (hiredate)
main t_main
   dimension by (rownum as x)
   measures (0 as top_salary, SYSDATE as recent_dates)
   rules iterate (5) (
     top_salary[iteration_number+1]   = t_salary.sal[iteration_number+1],
     recent_dates[iteration_number+1] = t_dates.hiredate[iteration_number+1]
     )


I'm sure there are other ways to satisfy Martin's requirement - there always are.

The reason I wanted to share this is that my perception of REFERENCE MODELS has changed. Whereas before, I figured REFERENCE MODELS would best be used to access supplemental or complementary information to the main model. I expect they could also improve readability. I now understand that REFERENCE MODELS can be used to pull together information that is completely non-correlated.....in our case associated only by row number.

Thanks, Martin. You made me look at things differently.


...our journey continues.