Lucas Jellema On Table Functions And The Model Clause

Mark Rittman

Nov 21, 2004 • 1 min read

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.

Sign up for more like this.