Sample Chapter: Efficient PL/SQL, Page 13
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.)
SQL> create table SRC (x number )pctfree 0; Table created. SQL> insert into SRC 2 select rownum 3 from all_objects 4 where rownum <10000; 9999 rows created.
Now we'll create a copy of the ALL_OBJECTS view in a table T1 with the created column truncated down to remove the time component from the date.
SQL> create table T1 as 2 select trunc(created)created 3 from all_objects; Table created.
By joining to the src table, we can report the objects created in the last two weeks, even if no objects were created on a particular day. A simple outer-join serves the purpose.
SQL> select trunc(sysdate)-14+x created,count(created)no_of_obj 2 from t1,src 3 where trunc(sysdate)-14+x =t1.created(+) 4 and x <=14 5 group by trunc(sysdate)-14+x 6 / CREATED NO_OF_OBJ --------- -------------- 30/MAY/03 0 31/MAY/03 0 01/JUN/03 0 02/JUN/03 0 03/JUN/03 0 04/JUN/03 0 05/JUN/03 0 06/JUN/03 0 07/JUN/03 0 08/JUN/03 0 09/JUN/03 41 10/JUN/03 4 11/JUN/03 6 12/JUN/03 8
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).
SQL> create or replace 2 type date_list is table of date; 3 / Type created.
Next we create a pipelined function that pipes back rows ranging from a provided starting date (parameter p_start_date) up to any limit we specify (parameter p_limit).
SQL> create or replace 2 function pipe_date(p_start date,p_limit number) 3 return date_list pipelined is 4 begin 5 for i in 0 ..p_limit-1 loop 6 pipe row (p_start +i); 7 end loop; 8 return; 9 end; 10 / Function created.
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.
SQL> select column_value,count(created)no_of_obj 2 from t1,table(pipe_date(trunc(sysdate)-14,14)) 3 where column_value =t1.created(+) 4 group by column_value 5 / COLUMN_VA NO_OF_OBJ --------- -------------- 29/MAY/03 0 30/MAY/03 0 31/MAY/03 0 01/JUN/03 0 02/JUN/03 0 03/JUN/03 0 04/JUN/03 0 05/JUN/03 0 06/JUN/03 0 07/JUN/03 0 08/JUN/03 0 09/JUN/03 41 10/JUN/03 4 11/JUN/03 6
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:
SQL> select /*+CARDINALITY(t 14)*/ column_value,count(created)no_of_obj 2 from t1,table(pipe_date(trunc(sysdate)-14,14))t 3 where column_value =t1.created(+) 4 group by column_value 5 /