Cheesy Code

Using WITH allows Oracle to get the result set once, store it and reuse it. The SQL is also shorter. Let’s generate a random sequence as an inline view using the WITH clause and then use it twice. If the query were being recalculated we’d see that the random numbers would be different. However there is no recalculation, even without the use of the undocumented MATERIALIZE hint:

EXEC dbms_random.initialize(5);   

WITH rnd AS
     (SELECT     LEVEL lvl, CHR (LEVEL + 64) letter,
                 TRUNC (DBMS_RANDOM.VALUE (1, 5)) rnd
            FROM DUAL
      CONNECT BY LEVEL <= 5)
SELECT a.*, b.rnd
  FROM rnd a, rnd b
WHERE a.lvl = b.lvl;

The random numbers match which shows there was no recalculation. Compare that the alternate style of writing the query with two embedded subqueries which yields two different random sequences. That tells us that Oracle has had to calculate the values twice:

EXEC dbms_random.initialize(5);
SELECT a.*, b.rnd
  FROM (SELECT     LEVEL lvl, CHR (LEVEL + 64) letter,
                   TRUNC (DBMS_RANDOM.VALUE (1, 5)) rnd
              FROM DUAL
        CONNECT BY LEVEL <= 5) a,
       (SELECT     LEVEL lvl, CHR (LEVEL + 64) letter,
                   TRUNC (DBMS_RANDOM.VALUE (1, 5)) rnd
              FROM DUAL
        CONNECT BY LEVEL <= 5) b
WHERE a.lvl = b.lvl;

Using WITH is both neater and the database needs to do less work. Samples run on 9i.