June 24, 2018
Hot Topics:

Sample Chapter: Efficient PL/SQL

  • February 5, 2004
  • By Beck, Kallman, Katz, Knox, and McDonald
  • Send Email »
  • More Articles »

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 /

---------    --------------
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)*/
  2  from t1,table(pipe_date(trunc(sysdate)-14,14))t
  3  where column_value =t1.created(+)
  4  group by column_value
  5  /

Page 13 of 19

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.


We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date