Sample Chapter: Efficient PL/SQL

Filling in the Blanks

It's difficult in SQL to generate result sets of data that don't necessarily exist in the database at all. For example, for the requirement to generate a list of database objects created in the last two weeks by day, every day must be listed even if no objects were created. The conventional workaround to this problem is a "filler" table—an example of which you'll see in the next section of this chapter. We can demonstrate this to produce the required list of recently created objects. First we create a table, src , which contains the digits 1 to 9999 as rows. (The name "src" was chosen because this table will serve as an arbitrary source of rows.)

Once again, understanding what facilities are available in PL/SQL could lead to a solution that does not require an additional table, which also resolves the issue of how many rows in src are enough. The pipeline function facility that is new to version 9 can also be used as a mechanism for artificially generating as many rows as we see fit. (See Chapter 5 for a full description of pipelined functions. 3) First we need to create a nested table collection type (because all pipelined functions must return such a datatype).

Instead of using the src table, we can now call our pipeline function PIPE_DATE to artificially create as many rows as we desire. In this case, we only require 14 rows back from the function. Our query now looks like this.

We can also assist the optimizer by telling it how many rows we will be returning from our pipeline function. We can use the CARDINALITY hint to assist Oracle with its optimization. In the example just shown, we can tell the optimizer that there will be 14 rows returned as follows: