Rittman Mead - Delivered Intelligence

Lucas Jellema from Amis has written up an
excellent blog article
on a real-life use of table functions and the Oracle 10g model clause.

"From time to time there is a need in my SQL
queries to have access to a table with a specific number of records. Sometimes
I want to explode the results from a query double, treble etc. the number of
records. On other occasions I want to select from a specific range or outer
join with a specified range to have an entry for every rank, date etc. More
concrete examples (in terms of EMP and
DEPT):

• select the number of employees hired on every day of the week
  include entries for days on which no one was hired
   
• select the employees ranked on even positions when ordered by salary
  (note: there are many ways to approach this)
   
• present for the numbers 1 to 5 the number of employees that manage
  that number of subordinates
   
• select all employees as many times as there are Clerks

Of course the old way of doing this was create a utility or helper table
that contained a very large number of records (something like dual but with
many rows). Each row typically only contained a number. At other times you
also have created tables with seven records (days of the week), twelve records
(months of the year), 26 records (letters in the alphabet) etc. However, these
could all have been derived in views from the one table with just many numeric
values. You could also fake this table by using a view or table in your schema
that you know to be always available and contain a substantial number of
records. Tom Kyte s favorite for example is ALL_OBJECTS.
We will look at the modern way: TABLE FUNCTIONS
(Oracle 9i) and the MODEL clause (10g)."

You can read the rest of the article, with code examples,
here.

Posted in Oracle Database |