Answer with SQL: How many weekdays a year?

I'm a big fan of generating data with dual, using a perk of the connect by syntax.

I think Tom Kyte was the originator of this technique. It's not necessarily the fastest method, but it's super convenient - no table required.

Today I wanted to know how many weekdays a year, so I defined 365 rows on the fly using sysdate to turn these into each day of the year. Then I ran a simple select over this to aggregate my result

with years as (
   select to_char(trunc(sysdate,'yy')+rownum-1,'dy') dy
   from dual
   connect by level <= 365
)
select count(*)
      ,sum(case when dy in ('sat','sun') then 1 end) weekends
from years

You can see this demonstrated at livesql.oracle.com
https://livesql.oracle.com/apex/livesql/file/content_D6ZWKMK4O2IVMWGOAXIDEP61M.html
Never heard of it? I recommend you have a play. I've only dabbled myself, but if you don't have an environment to experiment with, this is free!

Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions within a SQL statement was relatively easy.

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(event_no)
FROM   events
/

However a slight adjustment is required for DML. The documentation suggests that

• If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint.

but does not give any examples, which I think is unfortunate - but thanks Tim for getting us started ;-)

UPDATE /*+ WITH_PLSQL */ events e
SET e.org_id =
  (WITH
     FUNCTION inline_fn(p_id IN NUMBER) RETURN NUMBER IS
     BEGIN
       RETURN p_id;
     END;
   SELECT inline_fn(e.org_id)
   FROM   dual);
/

Without the hint Oracle returns

ORA-32034: unsupported use of WITH clause

but I was getting

ORA-00933: SQL command not properly ended

What clued me in was the brief highlight SQL Developer makes over the statement before it executes. For me this paused at the return statement within the function.

I happened to be using one of the pre-built Oracle Developer VMs to play around, and it turns out the one I'm using has SQL Developer 4.0.0.13 supplied.

That particular version doesn't seem to be aware of this bleeding edge feature. I vaguely recall seeing this mentioned somewhere probably in the vicinity of thatJeffSmith fellow. I tried it in the command line SQL*Plus and it worked fine against the 12.1.0.1 instance.

It does ring a clear bell for once upon a time circa 2006 working on Oracle 9i or 10g when I sent an email to a colleague containing a SQL statement including a WITH clause.

He didn't have success in his Oracle 8i SQL*Plus windows client either... oh how I miss thee.

Playing with dates, again

When creating LOVs for APEX I sometimes debate to myself whether to make a static or dynamic LOV.

I had one scenario where having some SQL was handy, so I started with this

SELECT TO_CHAR(NEXT_DAY(sysdate, 'MON')+ROWNUM-1,'DY')
FROM dual
CONNECT BY LEVEL <= 7;

It's possible to then place this as an inline view within a subquery factoring clause, to use fancy terminology. This makes it easy to share column data.

WITH data AS 
 (SELECT NEXT_DAY(sysdate, 'MON')+ROWNUM-1 dt
  FROM dual
  CONNECT BY LEVEL <= 7)
SELECT TO_CHAR(dt,'DY'), TO_CHAR(dt,'Day')
FROM data;

TO_CHAR(DT,'DY') TO_CHAR(DT,'DAY')
---------------- -----------------
MON              Monday            
TUE              Tuesday           
WED              Wednesday         
THU              Thursday          
FRI              Friday            
SAT              Saturday          
SUN              Sunday            

 7 rows selected 

Simple, but effective.

Anyone have thoughts on benefits of using dynamic vs static LOVs in APEX?

SQL tip: List days of the week

This one's real quick - something handy for later.

I've done some similar before for all days/weekends of the year, or the ubiquitous months of the year.

Here I wanted to dynamically define days of the week:

WITH data AS 
 (SELECT NEXT_DAY(sysdate, 'MON') dt
  FROM dual
  CONNECT BY LEVEL <= 7)
SELECT TO_CHAR(dt+ROWNUM-1,'DY'), TO_CHAR(dt+ROWNUM-1,'Day')
FROM data;

Scott

Fun with SQL analytical functions

I had an interesting SQL problem at work recently, and I came up with a solution that I'm not sure is completely ideal - so I thought I'd attempt to replicate it here.

Some of the complexity is lost while I obscure and simplify the problem (it involved a hierarchical query), but I think the key elements remain.

Consider a table of codes with an order sequence.

create table my_values (code varchar2(10), order_seq number(5));
insert into my_values values ('A', 10);
insert into my_values values ('A', 20);
insert into my_values values ('A', 30);
insert into my_values values ('B', 40);
insert into my_values values ('B', 50);
insert into my_values values ('C', 60);
insert into my_values values ('C', 70);
insert into my_values values ('C', 80);
insert into my_values values ('D', 90);

My requirement was to see the next and previous code. For instance, when listing B records, I wanted to see A and C in the same row - the solution screamed analytical functions so I started with my favourite:

select code, order_seq
      ,row_number() over (partition by code order by order_seq) rn
from my_values
order by order_seq;

CODE       ORDER_SEQ RN
---------- --------- --
A                 10  1 
A                 20  2 
A                 30  3 
B                 40  1 
B                 50  2 
C                 60  1 
C                 70  2 
C                 80  3 
D                 90  1

I struck the results down to just the first row for each code, incorporating lag/lead to get the info I needed.

select s.code
      ,lag(s.code) over (order by s.order_seq) my_lag
      ,lead(s.code) over (order by s.order_seq) my_lead
from 
(
  select code, order_seq
        ,row_number() over (partition by code 
                            order by order_seq) rn
  from my_values
  order by order_seq
) s
where s.rn = 1
order by order_seq;

CODE       MY_LAG     MY_LEAD  
---------- ---------- ----------
A                     B          
B          A          C          
C          B          D          
D          C                    

Finally, to combine my results I created an in-line view with a subquery factoring statement.

with sub as (
  select s.code
        ,lag(s.code) over (order by s.order_seq) my_lag
        ,lead(s.code) over (order by s.order_seq) my_lead
  from 
  (
    select code, order_seq
          ,row_number() over (partition by code 
                              order by order_seq) rn
    from my_values
    order by order_seq
  ) s
  where s.rn = 1)
select m.*, sub.my_lag, sub.my_lead
from my_values m, sub
where m.code = sub.code;

CODE       ORDER_SEQ MY_LAG     MY_LEAD  
---------- --------- ---------- ----------
A                 10            B          
A                 20            B          
A                 30            B          
B                 40 A          C          
B                 50 A          C          
C                 60 B          D          
C                 70 B          D          
C                 80 B          D          
D                 90 C                     

So my question is - any ideas for a simpler solution?

Recursive Subquery Factoring

OK, this post is partially for my benefit because I'm sure in future I'll need to re-think how this works - and I'll want the basic syntax on hand.

From 11g Release 2, the SQL WITH clause has been extended to allow recursive queries. This new syntax complies with the ANSI standards, as opposed to Oracle's CONNECT BY, START WITH keywords.

It's officially called "recursive subquery factoring", but it's also known as tree-walking or a hierarchical query.

Unfortunately in this simple example, the ANSI syntax is somewhat more verbose. I wonder if this evens out as the complexity of the query increases, or if the readability of the code is "scalable"?

-- 10g method
SELECT o.org_id, o.name, o.parent_org_id, level
FROM organisations o
CONNECT BY PRIOR org_id = parent_org_id
START WITH org_id = 1000;

-- 11 method
WITH org_sage (org_id, name, parent_org_id, reportlevel) AS
  (SELECT org_id, name, parent_org_id, 1 reportlevel
   FROM   organisations
   WHERE  org_id = 1000
   UNION ALL
   SELECT o.org_id, o.name, o.parent_org_id, reportlevel+1
   FROM  org_sage p, organisations o
   WHERE p.org_id = o.parent_org_id
)
SELECT org_id, name, parent_org_id, reportlevel
FROM org_sage;

Another unfortunate outcome is a quick test of throughput - 10000 iterations on my laptop gave the following respective timings.

.81 secs
.000081 secs per iteration
3.56 secs
.000356 secs per iteration

So it might be best to compare the two in your scenario/data, and consider the value of using the ANSI format in your case.

Further documentation can on hierarchical queries can found here, and in the SQL Language Reference, under the SELECT statement, looking at subquery factoring.

Remember, the key to understanding recursion is understanding recursion :-)

Data Densification - filling dates example

Many years ago when I was first learning Oracle (in the deep end) I came by the problem of data densification. I can't remember how I solved the problem back then, but I've encountered it a few times since. When writing my presentation for the model clause, I explored the difference between two options. Recently whilst investigating syntax definitions for the ANSI standard outer joins, I encountered another solution called a partitioned outer join - so I thought I'd compare all three for performance and readability.

The scenario is thus - I have an amount in a table for various dates throughout the year, for a particular person.

NAME          AMT DT
------ ---------- -----------
Scott         117 11-jan-2009
Scott         250 17-feb-2009
Scott         300 14-apr-2009
Scott          50 06-jun-2009
Wade         1231 17-mar-2009
Wade         2321 22-apr-2009
Wade         3122 30-sep-2009
Wade           59 31-oct-2009

However in my report I wish all months of the year displayed for the person, regardless of whether amounts exist. Let's add a cumulative figure in there for good measure. Included below this is the SQL for my test case.

NAME   MTH              AMT    CUM_AMT
------ --------- ---------- ----------
Scott  January          117        117
Scott  February         250        367
Scott  March              0        367
Scott  April            300        667
Scott  May                0        667
Scott  June              50        717
Scott  July               0        717
Scott  August             0        717
Scott  September          0        717
Scott  October            0        717
Scott  November           0        717
Scott  December           0        717
Wade   January            0          0
Wade   February           0          0
Wade   March           1231       1231
Wade   April           2321       3552
Wade   May                0       3552
Wade   June               0       3552
Wade   July               0       3552
Wade   August             0       3552
Wade   September       3122       6674
Wade   October           59       6733
Wade   November           0       6733
Wade   December           0       6733

DROP TABLE customer;
CREATE TABLE customer (
   name  VARCHAR2(10)
  ,amt   NUMBER
  ,dt    DATE
);

insert into customer values('Scott',117,  to_date('11-01-2009','DD-MM-YYYY'));
insert into customer values('Scott',250,  to_date('17-02-2009','DD-MM-YYYY'));
insert into customer values('Scott',300,  to_date('14-04-2009','DD-MM-YYYY'));
insert into customer values('Scott',50,   to_date('06-06-2009','DD-MM-YYYY'));
insert into customer values('Wade',1231,  to_date('17-03-2009','DD-MM-YYYY'));
insert into customer values('Wade',2321,  to_date('22-04-2009','DD-MM-YYYY'));
insert into customer values('Wade',3122,  to_date('30-09-2009','DD-MM-YYYY'));
insert into customer values('Wade',59,    to_date('31-10-2009','DD-MM-YYYY'));

This is a pre-10g solution, using an efficient method on the DUAL table to conjure records. I also use analytics to determine the cumulative amount. There is however a cartesion join and two full table scans on the customer table.

SELECT date_fill.name
      ,TO_CHAR(real_dt,'Month') mth
      ,NVL(amt,0) amt
      ,NVL(SUM(amt) OVER (PARTITION BY date_fill.name
                          ORDER BY real_dt ),0) cum_amt
FROM
  (SELECT name, TRUNC(dt,'mm') dt, SUM(amt) amt
   FROM   customer
   GROUP BY name, TRUNC(dt,'mm')
   ) actual_data -- Actual data
  ,(SELECT name, real_dt
    FROM  (SELECT DISTINCT name
           FROM   customer)
         ,(WITH mths AS (SELECT TRUNC(SYSDATE,'YYYY') real_dt
                         FROM DUAL CONNECT BY LEVEL <= 12)
           SELECT ADD_MONTHS(real_dt,ROWNUM-1) real_dt FROM mths)
   ) date_fill -- Distinct list with conjured dates
-- Outer join actual data with full date list
WHERE date_fill.real_dt = actual_data.dt(+)
AND   date_fill.name    = actual_data.name(+)
ORDER BY date_fill.name, date_fill.real_dt;

-------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     2 |    74 |    11  (28)|
|   1 |  WINDOW SORT                        |          |     2 |    74 |    11  (28)|
|*  2 |   HASH JOIN OUTER                   |          |     2 |    74 |    11  (28)|
|   3 |    VIEW                             |          |     2 |    24 |     6  (17)|
|   4 |     MERGE JOIN CARTESIAN            |          |     2 |    24 |     6  (17)|
|   5 |      VIEW                           |          |     1 |     6 |     2   (0)|
|   6 |       COUNT                         |          |       |       |            |
|   7 |        VIEW                         |          |     1 |     6 |     2   (0)|
|   8 |         CONNECT BY WITHOUT FILTERING|          |       |       |            |
|   9 |          FAST DUAL                  |          |     1 |       |     2   (0)|
|  10 |      BUFFER SORT                    |          |     2 |    12 |     6  (17)|
|  11 |       VIEW                          |          |     2 |    12 |     4  (25)|
|  12 |        HASH UNIQUE                  |          |     2 |    12 |     4  (25)|
|  13 |         TABLE ACCESS FULL           | CUSTOMER |     8 |    48 |     3   (0)|
|  14 |    VIEW                             |          |     8 |   200 |     4  (25)|
|  15 |     HASH GROUP BY                   |          |     8 |   136 |     4  (25)|
|  16 |      TABLE ACCESS FULL              | CUSTOMER |     8 |   136 |     3   (0)|
-------------------------------------------------------------------------------------

Here is what some would consider a more elegant solution using the model clause. The explain plan is far neater, and the performance was also enhanced - and there was no joins & half as many logical reads. The cumulative column was calculated using a nifty rule computation.

SELECT name, TO_CHAR(dt,'DD-MM-YYYY') dt, amt, cum_amt -- Model results
FROM (
   SELECT name, TRUNC(dt, 'MM') dt, SUM(amt) amt
   FROM   customer
   GROUP BY name, TRUNC(dt, 'MM')
)
MODEL
PARTITION BY (name)
DIMENSION BY (dt)
MEASURES (amt, cast(NULL AS NUMBER) cum_amt) -- Define calculated col
IGNORE NAV
RULES SEQUENTIAL ORDER(
   -- Conjure dates
   amt[FOR dt FROM TO_DATE('01-01-2009', 'DD-MM-YYYY')
              TO   TO_DATE('01-12-2009', 'DD-MM-YYYY')
              INCREMENT NUMTOYMINTERVAL(1, 'MONTH')
       ] = amt[CV(dt)] -- Apply amt for given date, if found
  ,cum_amt[ANY] = SUM(amt)[dt <= CV(dt)] -- Calculate cumulative
)
ORDER BY name, dt;
------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     8 |   136 |     5  (40)|
|   1 |  SORT ORDER BY               |          |     8 |   136 |     5  (40)|
|   2 |   SQL MODEL ORDERED          |          |     8 |   136 |     5  (40)|
|   3 |    HASH GROUP BY             |          |     8 |   136 |     5  (40)|
|   4 |     TABLE ACCESS FULL        | CUSTOMER |     8 |   136 |     3   (0)|
|   5 |    WINDOW (IN SQL MODEL) SORT|          |       |       |            |
------------------------------------------------------------------------------

The following solution is the one I stumbled upon while playing with ANSI joins. I wanted to see how it compared with the others. We still have a join, but that's not necessarily an issue. It's certainly tidier looking than the first solution, and we don't need to fry our brain learn how to use model. I have heard some resistance to ANSI syntax however - not for the sake of going against the grain, but apparently there have been some security issues. I'm not qualified to comment on this, let's just compare the performance in this particular test case.

SELECT c.name, TO_CHAR(real_dt,'Month') mth, NVL(amt,0) amt
      ,NVL(SUM(amt) OVER (PARTITION BY c.name
                          ORDER BY real_dt ),0) cum_amt
FROM customer c
PARTITION BY (name)
RIGHT OUTER JOIN
  (WITH mths AS (SELECT TRUNC(SYSDATE,'YYYY') real_dt
                 FROM DUAL CONNECT BY LEVEL <= 12)
   SELECT ADD_MONTHS(real_dt,ROWNUM-1) real_dt FROM mths) mths
ON (real_dt = TRUNC(c.dt,'mm'))
ORDER BY c.name, real_dt;
-------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     2 |   130 |     8  (13)|
|   1 |  WINDOW SORT                        |          |     2 |   130 |     8  (13)|
|   2 |   VIEW                              |          |     2 |   130 |     8  (13)|
|   3 |    NESTED LOOPS PARTITION OUTER     |          |     2 |    46 |     8  (13)|
|   4 |     BUFFER SORT                     |          |       |       |            |
|   5 |      VIEW                           |          |     1 |     6 |     2   (0)|
|   6 |       COUNT                         |          |       |       |            |
|   7 |        VIEW                         |          |     1 |     6 |     2   (0)|
|   8 |         CONNECT BY WITHOUT FILTERING|          |       |       |            |
|   9 |          FAST DUAL                  |          |     1 |       |     2   (0)|
|* 10 |     FILTER                          |          |       |       |            |
|  11 |      SORT PARTITION JOIN            |          |     1 |    17 |     4  (25)|
|  12 |       TABLE ACCESS FULL             | CUSTOMER |     1 |    17 |     3   (0)|
-------------------------------------------------------------------------------------

I executed these 3 statements 1000 times on my laptop on 10gR2 (similar results in 11gR1) to see how throughput compares. I consistently found the following performance:

6 secs -- Original
.006 secs per iteration
4.6 secs -- Model clause
.0046 secs per iteration
.35 secs -- Partitioned outer join
.00035 secs per iteration

Personally I was a little surprised at the dramatic timing difference with the partitioned outer join over the original. I thought perhaps that would have been more comparable to the model clause solution. Something to bear in mind in future development.

If I needed to write this for a future project I'd probably take a closer look at the relevant data set using Tom Kyte's runstats package. This used to be located here - htpp://asktom.oracle.com/tkyte/runstats.html, but the link is currently broken. Possibly related to his recent move. However here is one of many of his forum entries addressing runstats. This package is better than just measuring throughput, it will let you know how your application may scale by paying attention to latches. A DBA's perspective can be found here.

Subquery Factoring Clause - WITH

Through general experience I've found the WITH statement either underutilised or people haven't been aware of it's existence, although that seems to be changing - it has been around since 9i.

For me, two typical examples come to mind.
1) I was demonstrating recently an example of using SUBSTR & INSTR together to extract certain parts of a string. I built up the expressions step by step, then to test the example with a slightly different string, I would normally be forced to do a search and replace. Depending on the example, this can be annoying.

select 'halls head 6065'
      ,substr('halls head 6065',4,5) guess
      ,instr('halls head 6065',' ',-1,1) pos_of_space
      ,substr('halls head 6065',1,instr('halls head 6065',' ',-1,1)-1 )||'*' suburb
      ,'*'||substr('halls head 6065',instr('halls head 6065',' ',-1,1)+1 )||'*' postcode
from dual;

Instead I can put the string I want to play with in a simple WITH statement, and change the "column name" at will.

with test as (select 'halls head 6065' suburb from dual)
select suburb
      ,substr(suburb,4,5) guess
      ,instr(suburb,' ',-1,1) pos_of_space
      ,substr(suburb,1,instr(suburb,' ',-1,1)-1 )||'*' suburb
      ,'*'||substr(suburb,instr(suburb,' ',-1,1)+1 )||'*' postcode
from test;

Other times recently I have used the WITH statement to essentially encapsulate a unit of work. I had extracted a SQL statement from some PL/SQL and wanted to run it with my own restrictions, but leave the original untouched - this allowed me to test without worrying if I've disrupted the original join behaviour.

WITH original as ({copy of embedded sql})
select *
from original
where {my own restrictions}

These are ad hoc reasons, there is also a potential performance benefit from using the WITH clause.

I'm sure I've seen an example where using the WITH clause provided a performance benefit, but memory is fallible so I thought I'd see what the documentation had to say.
It says it will give the optimiser more choice about what to do, and that we can improve the query by using the WITH syntax.
After comparing explain plans from the example straight out of the documentation, I wasn't convinced. So I ran timings over different versions of the database - 1000 iterations each. I was surprised at the results (sounds like tabloid journalism, doesn't it?):

Without the clause

SELECT dname, SUM(sal) AS dept_total
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname HAVING
SUM(sal) >
  (
    SELECT SUM(sal) * 1/3
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
  )
ORDER BY SUM(sal) DESC;

With the clause

WITH summary AS
(
 SELECT dname, SUM(sal) AS dept_total
 FROM emp, dept
 WHERE emp.deptno = dept.deptno
 GROUP BY dname
)
SELECT dname, dept_total
FROM summary
WHERE dept_total >
  (
    SELECT SUM(dept_total) * 1/3
    FROM summary
  )
ORDER BY dept_total DESC;

--9i
.3 secs
.0003 secs per iteration
2.9 secs
.0029 secs per iteration
--10g
1.1 secs
.0011 secs per iteration
4.06 secs
.00406 secs per iteration
--11g
1.17 secs
.00117 secs per iteration
7.46 secs
.00746 secs per iteration

Not only has it not performed better, its comparative performance has deteriorated as versions go up. So while we have removed duplicate code and enhanced the readability of our code, this example shows it's not necessarily more efficient.

It may just be a poor example to illustrate the performance benefit of this feature. It shows while a documented effect may be true in some circumstances, you must always test your particular scenario on your framework to get accurate comparisons. For further comment on this paradigm, I highly recommend reading this. Twice.