Choosing between SQL and PL/SQL

If you prefer to read in German, then you can find a translation here.


On my post Calculating probabilities with N throws of a die, I received a comment by Narendra saying:

I hope you are not serious about your last statement with some comments I'm sure it's not that hard to maintain....:)

But I was serious. However, it's a sentiment I hear a lot: after my presentation Do more with SQL I received similar comments claiming PL/SQL would be easier in some cases. And according to Iggy Fernandez, Steven Feuerstein has said here:

Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.

And I don't get it.

Well, I can understand why that would be a first reaction when seeing some undocumented long piece of SQL containing some of the newer SQL constructs. But when thinking a bit longer about the subject, I don't think it's a totally fair reaction. Here's why.

1. When faced with a challenging problem, most people tend to resort to the language they are most comfortable with. I know I do. For example, I'm way better with PL/SQL than I am with Java. So when faced with a hard algorithm, I'll always use PL/SQL. And I bet a Java programmer reasons the other way round. So when saying that straight SQL is harder to maintain than PL/SQL, I guess you are really saying that your PL/SQL skills are very good, but your SQL skills are, well, somewhat less than very good. That's no problem at all, since you will still be able to build applications effectively. But I don't think the language itself is to blame, it's the skills of the people talking that language.



2. In production code, I see PL/SQL code more often being documented with comments than SQL code. But every piece of production code that isn't straightforward should be documented. Why does SQL code rarely contain comments? Probably because most SQL statements in production are of the basic SELECT ... FROM ... WHERE kind. And when used to not commenting those easy SQL statements, the harder ones are almost automatically lacking comments as well. When you start adding comments for SQL code, as well as for your PL/SQL code, then it will be a reason less for why you may find SQL code harder to grasp.



3. In PL/SQL you are able to split up a complex task into many simple tasks. Each task is then performed by a single function or a procedure. And those functions and procedures have clear names, making the code self documenting. This is called modularizing your code and you've probably been taught about this subject in school already. Complex SQL used to be just a giant piece of text lacking this ability, but from Oracle9i onwards, Oracle gave us the WITH-clause (also known as subquery factoring). With this clause you can give a meaningful name to each subpiece of SQL. Thus, the same kind of modularization we achieve with PL/SQL, is possible with SQL since 9i as well.

And so I think SQL is equally readable, just a little more compact. And often faster due to less context switching. And more often correct because it's one read consistent query instead of several queries taking place at different times.

An example using the code from Calculating probabilities with N throws of a die, of how it could look like in production. Difference is that the results of that query are inserted into a table. I made both variants "production like" by documenting them well. First a package using SQL:

rwijk@ORA11GR1> create package probabilities_sql
  2  as
  3    --
  4    -- The procedure "calculate" calculates all probabilities with
  5    -- p_number_of_dies throws of a die. The results of the calculation
  6    -- are inserted into the table probabilities.
  7    --
  8    procedure calculate (p_number_of_throws in number)
  9    ;
 10  end probabilities_sql;
 11  /

Package is aangemaakt.

rwijk@ORA11GR1> create package body probabilities_sql
  2  as
  3    procedure calculate (p_number_of_throws in number)
  4    is
  5    begin
  6      insert into probabilities
  7      ( sum_of_dies
  8      , percentage
  9      )
 10      with number_of_die_faces as (select count(*) cnt from die)
 11      , all_probabilities as
 12      ( select sum_value
 13             , prob
 14             , i
 15          from --
 16               -- Generate as many rows as there are possible combinations of the
 17               -- dies. This equals: power(,p_number_of_throws).
 18               -- For example: with a traditional die (6 faces) and 3 throws, there
 19               -- are power(6,3) = 216 rows with a l-value running from 1 until 216.
 20               --
 21               ( select level l
 22                   from number_of_die_faces
 23                connect by level <= power(cnt,p_number_of_throws)
 24               )
 25             , number_of_die_faces
 26         model
 27               --
 28               -- A reference model to be able to quickly lookup the face_value
 29               -- and probability when provided a face_id
 30               --
 31               reference r on (select face_id, face_value, probability from die)
 32                 dimension by (face_id)
 33                 measures (face_value,probability)
 34               main m
 35                 --
 36                 -- Each combination is in a different partition.
 37                 -- Which means it is easy to parallellize if necessary.
 38                 --
 39                 partition by (l rn, cnt)
 40                 dimension by (0 i)
 41                 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
 42               --
 43               -- Iterate as many times as there are throws of the die.
 44               --
 45               rules iterate (1000) until (iteration_number+1=p_number_of_throws)
 46               --
 47               -- For each throw of the die, calculate the face_id, remainder, the
 48               -- sum and probability. For the sum and probability, the reference
 49               -- model is used as a lookup. Each iteration overwrites the previous
 50               -- one.
 51               --
 52               ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
 53               , remainder[0]   = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
 54               , sum_value[0]   = sum_value[0] + face_value[die_face_id[0]]
 55               , prob[0]        = prob[0] * probability[die_face_id[0]]
 56               )
 57      )
 58      --
 59      -- All probabilities of each possible combination are now calculated.
 60      -- Now, sum them all up per sum of all face_values.
 61      --
 62      select sum_value
 63           , sum(prob)
 64        from all_probabilities
 65       group by sum_value
 66      ;
 67    end calculate;
 68  end probabilities_sql;
 69  /

Package-body is aangemaakt.

And a package doing it the PL/SQL way, using the same idea:

rwijk@ORA11GR1> create package probabilities_plsql
  2  as
  3    --
  4    -- The procedure "calculate" calculates all probabilities with
  5    -- p_number_of_dies throws of a die. The results of the calculation
  6    -- are inserted into the table probabilities.
  7    --
  8    procedure calculate (p_number_of_throws in number)
  9    ;
 10  end probabilities_plsql;
 11  /

Package is aangemaakt.

rwijk@ORA11GR1> create package body probabilities_plsql
  2  as
  3    g_number_of_die_faces number(4)
  4    ;
  5    procedure initialization
  6    --
  7    -- Calculate the number of die faces (6 in case of a traditional die) only once.
  8    --
  9    is
 10    begin
 11      select count(*)
 12        into g_number_of_die_faces
 13        from die
 14      ;
 15    end initialization
 16    ;
 17    function face_value
 18    ( p_face_id in die.face_id%type
 19    ) return die.face_value%type result_cache relies_on (die)
 20    --
 21    -- A lookup function returning the face_value of a given face_id.
 22    -- This function is called multiple times for the same face_id's and
 23    -- is therefore optimized by the result_cache hint.
 24    --
 25    is
 26      l_face_value die.face_value%type;
 27    begin
 28      select face_value
 29        into l_face_value
 30        from die
 31       where face_id = p_face_id
 32      ;
 33      return l_face_value;
 34    end face_value
 35    ;
 36    function probability
 37    ( p_face_id in die.face_id%type
 38    ) return die.probability%type result_cache relies_on (die)
 39    --
 40    -- A lookup function returning the probability of a given face_id.
 41    -- This function is called multiple times for the same face_id's and
 42    -- is therefore optimized by the result_cache hint.
 43    --
 44    is
 45      l_probability die.probability%type;
 46    begin
 47      select probability
 48        into l_probability
 49        from die
 50       where face_id = p_face_id
 51      ;
 52      return l_probability;
 53    end probability
 54    ;
 55    procedure calculate (p_number_of_throws in number)
 56    is
 57      l_die_face_id die.face_id%type;
 58      l_remainder   number(10);
 59      l_sum         probabilities.sum_of_dies%type;
 60      l_probability probabilities.percentage%type
 61      ;
 62      type ta_probabilities is table of probabilities%rowtype index by pls_integer;
 63      a_probabilities ta_probabilities;
 64    begin
 65      --
 66      -- Loop as many times as there are possible combinations of the
 67      -- dies. This number equals: power(,p_number_of_throws).
 68      -- For example: with a traditional die (6 faces) and 3 throws, there
 69      -- are power(6,3) = 216 iterations.
 70      --
 71      for i in 1 .. power(g_number_of_die_faces,p_number_of_throws)
 72      loop
 73        l_remainder := i;
 74        l_sum := 0;
 75        l_probability := 1;
 76        --
 77        -- For each combination, iterate over all throws of each individual die,
 78        -- and calculate the face_id of that die (using l_die_face_id and
 79        -- l_remainder) and use that face_id to calculate the sum of the die
 80        -- face values and the probability.
 81        --
 82        for j in 1 .. p_number_of_throws
 83        loop
 84          l_die_face_id := 1 + mod(l_remainder-1, g_number_of_die_faces);
 85          l_remainder   := ceil((l_remainder-l_die_face_id+1)/g_number_of_die_faces);
 86          l_sum         := l_sum + face_value(l_die_face_id);
 87          l_probability := l_probability * probability(l_die_face_id);
 88        end loop;
 89        --
 90        -- Sum up all the probabilities with the same sum.
 91        --
 92        a_probabilities(l_sum).sum_of_dies := l_sum;
 93        a_probabilities(l_sum).percentage :=
 94          nvl(a_probabilities(l_sum).percentage,0) + l_probability
 95        ;
 96      end loop;
 97      --
 98      -- Bulk insert all calculated probabilities into the table PROBABILIIES.
 99      --
100      forall i in indices of a_probabilities
101        insert into probabilities
102        values a_probabilities(i)
103      ;
104    end calculate
105    ;
106  begin
107    initialization;
108  end probabilities_plsql;
109  /

Package-body is aangemaakt.

Note that this algorithm is not data intensive and the PL/SQL variant here is actually faster than the SQL variant due to 11g's result cache, but that's not the point here. The point is of course readability. Do you really think the SQL variant is much more complex than the PL/SQL variant? I'd love to hear your thoughts about this subject, whether you agree or not.

---

UPDATE

Three nice follow-up blog entries:

by Chen Shapira

by Laurent Schneider

by H.Tonguç Yılmaz