APEX conditions and performance - part 2

This is a follow-up to my previous post APEX conditions and performance.

Roel Hartman has followed up my post by doing the due diligence and testing the performance of different APEX condition types.  His findings back up what I just asserted. (But I wasn't just guessing, my assertions were based on facts I learned long ago from someone in the APEX team via the Oracle APEX forum - so long ago that I cannot now find the conversation!)

As I said previously, for a more complex condition than "item=value" you will often have to use a PL/SQL expression condition, and that's fine.  However, it is not uncommon to see a page where there is a group of several items that all have the same complex condition.  For example, perhaps if some complex condition is true then we need to collect the user's bank details and contact details - maybe 12 items.  The same complex condition is applied (and has to be evaluated) for each of the 12 items.  In this sort of case it may be appropriate to add child regions to the form region, and apply the complex condition just once - to the child region that contains this group of conditional items.  A suitable region template (perhaps even "no template") can be applied to these new child regions so that the appearance of the page is not changed by adding them.

Example 1: original page

Region: My Form
- Item: Name
- Item: Date of Birth
- Item: Bank Account No (complex condition)
- Item: Bank Sort Code (complex condition)
...
- Item: Address Line 1 (complex condition)
- Item: Address Line 2 (complex condition)
...
- Item: Something else

Example 2: page using child regions

Region: My Form
- Subregion: Personal Details
-- Item: Name
-- Item: Date of Birth
- Subregion: Bank and Address details (complex condition)
-- Item: Bank Account No
-- Item: Bank Sort Code
...
-- Item: Address Line 1
-- Item: Address Line 2
...
- Subregion: More stuff
-- Item: Something else

Example 2 achieves the same result as Example 1 but only evaluates the complex condition once instead of many times.  As well as improving performance this also makes your page simpler: if the condition changes in the future you only have one instance to change.

APEX conditions and performance

Having recently seen some examples of non-optimal code in APEX conditions (e.g. item rendering conditions and read-only conditions)  I thought it worth writing a few words about them.  By putting them here on my blog I can refer to them in future rather than writing them again. Also I may receive useful feedback from readers to improve or correct my advice.

For a very simple condition such as “when the value of item P1_JOB is SALESMAN” there are many condition types to choose from including:

1) Value of item/column in Expression 1 = Expression 2

Expression 1: P1_JOB

Expression 2: SALESMAN

2) PL/SQL Expression

Expression 1: :P1_JOB = ‘SALESMAN’

3) Exists (SQL query returns at least one row)

SELECT NULL

FROM DUAL

WHERE :P1_JOB = ‘SALESMAN’

(Yes I have actually seen this one!)

4) PL/SQL Function returning Boolean

Begin

  If :P1_JOB = ‘SALESMAN’ then

    Return true;

  Else

    Return false;

  End if;

End;

… and so on.

The correct type to use in this case is (1) because it is the only declarative method; all the others require APEX to run dynamic PL/SQL to evaluate the condition.

To evaluate (1) APEX will do something like this:

IF v(expression1) = expression2 THEN…

To evaluate (2) APEX will do something like this:

-- Parse expression1 to look for item references e.g. :P1_JOB

…

EXECUTE IMMEDIATE expression1 INTO l_result USING v(item_name);

IF l_result THEN …

(In fact it will have to use DBMS_SQL not EXECUTE IMMEDIATE because the number of bind values can vary.  The pseudo-code above is merely an educated guess by the way.)

(3) and (4) will involve similar code to (2).

True, the cost of executing the dynamic PL/SQL won’t be huge, but bear in mind that a page may have many items with conditions like this, and the small penalty is then incurred many times per page load, which adds up particularly on a frequently used page.

Of course, if you need a more complex condition such as “Job is manager and location = London” then you cannot use method (1), you will need to use method (2) (not (3) or (4) please!).  Always look for the simplest and most appropriate condition type for your needs.

Also, if the condition involves a function call e.g. (emp_pkg.is_eligible(:P1_EMPNO) = ‘Y’) and is used in more than one place it is probably better to execute the function call once, assign the result to a hidden page item, and then use a type (1) condition “value of P1_IS_ELIGIBLE is equal to Y”.

The curse of the cursor

For some reason, many Oracle developers avoid SELECT INTO as if it were dangerous, preferring to "have more control" over their code by using cursors for everything. This morning I spent over an hour debugging some code written by one such developer, only to find that the issue I was trying to fix was one that would have been caught by the original developer immediately had he used SELECT INTO.

The code resembled this:

procedure do_something (p_empno in number)
is
   l_empno number;
   l_boss_ind varchar2(1) := 'N';
   cursor c_emp (cp_empno number) is
      select job
      from emp
      where empno = cp_empno;
begin
   for rec in c_emp (l_empno) loop
      if rec.job = 'MANAGER' then
         l_boss_ind = 'Y';
      end if;
   end loop;
   insert into some_table (empno, boss_ind) values (p_empno, l_boss_ind);
end;

Sometimes the BOSS_IND column wasn't being set correctly. The code was a lot more complex than this in reality. You can probably easily and quickly spot the error above, but in the real procedure there was a lot of other code that could also set the BOSS_IND under different circumstances, and it was, as I said, about an hour before I spotted the problem which is: variable l_empno was not set when the cursor was opened (it got set further down), so the cursor returned no rows and the code inside the loop was never executed.

If the developer had coded this using SELECT INTO, a very unexpected NO_DATA_FOUND exception would have been raised as soon as he ran this for the first time, and the bug would never have reached system testing (as it had). Assuming of course he didn't decide that SELECT INTO had "caused" the bug, and didn't "fix" it by changing to use a cursor...

Apex MP3 Player item plug-in

I have developed a plug-in item type that renders an MP3 player to play a specified audio file using the free Premiumbeat Single Track Flash MP3 Player. The file may be specified via a URL of as the ID of a file stored in APEX_APPLICATION_FILES. There is a demo of this available here on apex.oracle.com

The plug-in has the following settings available:

They are mostly fairly self-explanatory (and documented via Help). They can generally be left to their default values. The Media Title attribute specifies the title displayed in the player. This can be set to an item reference e.g. &P2_TRACK_NAME. If not specified the URL of the file is displayed.

The value of the item can be either of the following:

• a number, in which case it is assumed to be the ID of an APEX_APPLICATION_FILES row containing the MP3 data


• any URL pointing to an MP3 file

This should shortly be available to download from apex-plugin.com.

Dutch Oracle User Group's Apex Day 2011

This week I attended the Dutch Oracle User Group (OGh)'s Apex Day as a presenter, along with an ex-colleague from Northgate, Nigel Blair. We were talking about how we converted 1500+ Forms modules to Apex. There was quite a lot of interest from the 250 people present. We hope the interest wasn't due to a mix-up in the agenda that had us down as Nigel Andrews and Tony Blair!

Luckily we were on first so I was then able to relax and enjoy the rest of the day, with fascinating presentations on Apex 4.1 (Hilary Farrell), Plug-Ins (John Scott), Apex Translations (Peter Raganitsch) and Apex with Locator/Spatial (Dimitri Gielis).

The OGh certainly know how to run a great event: everyone was well looked after, and the atmosphere was very relaxed. My thanks to Rob van Wijk for inviting me.

UPDATE
The handouts for various presentations, including ours, are now available to download from the Ogh website.

Fame at last for my biggest Apex project to date

I'm very pleased to see that the Apex project I started and worked on for several years is now the subject of an entry under Customer Quotes on the OTN Apex page.

"At Northgate Revenues & Benefits, we have used APEX to replace our legacy Oracle Forms system comprising around 1500 Forms. Our user interface has 10,000 end users daily, across 172 clients, who this year sent out over 12 million Council Tax annual bills worth £15billion and will pay out benefits of over £13billion. Our clients now experience, on average, sub second response times across a working day. We are continuing to leverage our investment in Oracle Application Express by delivering citizen facing solutions as well as launching the conversion of our Social Housing application which will replace 3,500 Oracle Forms running at 100 clients worldwide, with a total of 15,000 end users managing circa 3,000,000 properties. Oracle Application Express has helped us to make the move away from Oracle Forms whilst delivering benefits to our clients and our business.."

-- Alan Powell, Director of Products and Services, Northgate Public Services

This started life as a demo I built (using HTMLDB 1.6) for an alternative, simplified interface for "power users" to work alongside the Forms application, but was so well received by customers that it quickly became the interface, with the Forms interface eventually retired.

Building a seriously large business application with Apex is a very different ball game from the typical Apex "use case", and required a different approach. We had to invent for ourselves some of the features that were built in to later versions of Apex (and some that still haven't been), such as:

• Nested regions
• A Forms to Apex migration tool
  
• Dynamic Actions - i.e. a declarative way for developers to implement Javascript and AJAX functionality, without all of them having to become Javascript experts.
• Declarative tabular forms based on Apex collections, with validation and dynamic actions
  

Why did we go for Apex and not ADF? I blogged about this a few years ago and stand by what I said then (though I haven't had the chance or need to go back and look at ADF 4 years later).

This must be one of the biggest Apex projects ever undertaken, and I'm proud to have played a major part in it.

SQL Overlap Test

Many times I come across SQL where the developer is trying to check for overlap between two ranges (usually date ranges, but sometimes numbers). For example, to meet the requirement "select all employees whose hired from and to dates overlap with the project start and end dates".

The developer sketches out all the possible overlap scenarios and finds four:

1) End of range A overlaps start of range B:
A----
    B---------

2) Start of range A overlaps end of range B:
    A--------
B----

3) Range A falls entirely within range B:
    A---
B--------------

4) Range B falls entirely within range A:
A--------------
    B---

This leads to SQL like this (assuming all values are not null):

where (a.start < b.start 
       and a.end between b.start and b.end)
or    (a.start between b.start
       and b.end and a.end > b.end)
or    (a.start between b.start and b.end
       and a.end between b.start and b.end)
or    (b.start between a.start and a.end
       and b.end between a.start and a.end)

If, as is often the case, the end dates are allowed to be null, meaning "forever", then the SQL becomes yet more complex. In some cases I have seen attempts at this where the developer has got it wrong and missed out one of the cases altogether.

In fact it is much easier to look at the cases where A and B do not overlap, because there are only two such cases:

1) Range A ends before range B starts:
A---
       B-----

2) Range A starts after range B ends:
       A-----
B---

This leads to the much simpler SQL:

where not (a.end < b.start or a.start > b.end)

which can be rearranged to the even simpler (though perhaps less intuitive):

where a.end >= b.start 
and   a.start <= b.end

Even if we have to allow for null end dates this is now very simple:

where nvl(a.end,b.start) >= b.start 
and   a.start <= nvl(b.end,a.start)

I don't claim that any of the above is original, I am sure this algorithm appears in many SQL and other books. But I see variants of the long-winded version (sometimes bug-ridden ones) so often I thought it worth documenting here so I can point to it in future.

Shameless boasting

I hate to boast but...

StackOverflow has become one of my favourite forums for reading and sometimes answering Oracle-related questions (though it covers all programming topics in fact).

Today I am the first person ever to be awarded the Oracle badge for having earned 1000 upvotes for my answers to questions with the Oracle tag:


Of course, this may just mean I have too much time on my hands...

Chris Date Seminar

Chris Date is giving a 2 day seminar called SQL and Relational Theory - How to Write Accurate SQL Code in Edinburgh, 13th-14th May 2010. I really wish I could go to it myself. I attended one of his seminars back in about 1997 and it was both riveting and highly educational.

Apex 4.0 - no more dummy branches required

How many times do you see this error page while building an Apex page?

ERR-1777: Page 1 provided no page to branch to.
Please report this error to your application administrator.

To avoid that, I have a habit of creating an unconditional branch back to the same page in every page I build, with a sequence number higher than all the conditional branches. If I then add new conditional branches I have to ensure that I change the default sequence to a lower value.

A long time ago (in 2007) in this OTN forum thread I asked:

"I don't understand why we have to create these unconditional branches at all - why can't the default behaviour be to branch back to the same page if no other branch is taken? When is the "no page to branch to" error ever useful?!"

In Apex 4.0, this annoyance finally disappears:

"When no branches have been defined then branch page back to itself rather than showing an error."

Sometimes the little things make all the difference!

Trying Out Apex 4.0 Dynamic Actions

In my spare moments I am currently familiarising myself with the Apex 4.0 Early Adopter edition. One of the many exciting new features is Dynamic Actions. These allow you to add functionality to your pages that would previously have required writing Javascript, AJAX calls and On Demand PL/SQL processes, but can now be done declaratively.

The following very simple example shows 3 uses:
1) Enabling/disabling one field according to the value of another
2) Calculating and displaying a value when items are updated
3) Retrieving information from the database when an item is changed.

There is little documentation about Dynamic Actions yet, so I'm not sure I have always taken the best approach.

My example is a "Create Employee" page that inserts a row into the familiar EMP table:

I have created a Dynamic Action that makes the Commission item enabled only when the selected job is one of 'MANAGER' or 'SALESMAN'.

MANAGER selected:

CLERK selected:

These are "simple" actions, I merely had to specify which item values trigger the action like this:

... and then specify the item to be enabled/disabled.

Another dynamic action computes the "Total Package" as SAL+COMM when either of these two items are updated:

I did this using a Javascript fragment rather than PL/SQL, to avoid an unnecessary AJAX trip to the database:

(OK so I did have to write some Javascript! But not much.)

A third dynamic action fires when a manager is selected; this looks up the selected employee and gets his/her job title and department name and dislays them in 2 page items:

This last action isn't as efficient as I'd like because it results in 2 AJAX calls: one to get the job and one to get the department name. This could be a case of me just not knowing the right way to do it in one hit. I used 2 "true actions" that use SQL to set the value of an item. The first has this SQL:

select job from emp where empno = :p2_mgr

and the second:

select dname
from dept join emp using (deptno)
where empno = :p2_mgr

Flashback saves the day (again)

I'm posting this as much for my future reference as anything. This morning I accidentally dropped a package body from the schema I was working in - that is to say I intentionally dropped it, but then realised I shouldn't have. And since this package body was the work-in-progress of another developer who likes to build their code using Toad, directly into the database (a hanging offence if it was down to me...) there was no file containing the source that they could re-create it from.

I felt sure flashback would save me, but my first attempt failed:

SQL> select text from user_source as of timestamp (sysdate-1/24)
2 where name = 'MY_PACKAGE'
3 order by line;

no rows selected

A quick Google took me to this forums.oracle.com post where BluShadow said:

select obj# from sys.obj$ where name like 'PKG_MY_OVERWRITTEN_PKG%'

/*
Then, using the obj# run the following (as of 3 hours ago):
*/

select source from sys.source$ as of timestamp(sysdate-((1/24)*3)) where obj# = 1234567
order by line

/*
You can't look at sys.obj# as of any time previously.
These tables form the basis of the ALL_SOURCE database view.
*/

So that explains why my first attempt failed. But I still have a problem as I don't know the obj# for the dropped object. However, I was able to find it out by searching sys.source$ like this:

select * from sys.source$
as of timestamp (sysdate-1/24)
where upper(source) like '%MY_PACKAGE%';

Inspection of the results revealed the required obj#, and I was finally able to obtain the source of the dropped package body. Phew!

Accessible Apex

I am often asked whether Apex can be used to build applications that comply with web content accessibility guidelines. It can, as this demo Apex application aims to prove.

It is a very simple application consisting of 3 pages:

• Login
• Report
• Form

The report page shows some records from a table, and has a link on each row to open the form page for editing, and a Create link in the report header to open the form page to create a new record.

The form page has a Submit button to save the changes and a Cancel link to return without saving changes.

I have tested all 3 pages with the Total Validator tool and they all reach Conformance Level "Triple-A", i.e. all Priority 1, 2 and 3 checkpoints are satisfied (according to the tool).  

Perhaps the most important point is that the application works even when Javascript is disabled in the browser.   It is often thought that Apex applications cannot work without Javascript enabled, but this isn't so; however, many Apex components such as tabs do require Javascript, so they can't be used.  That doesn't mean an accessible application can't have tabs, it just means you have to build your own tab functionality (which I haven't done in this demo application).

I don't have access to a screen reader like Jaws so I have not tested the application with one yet.

UKOUG - Apex SIG

I was lucky enough to be able to attend the UK Oracle User Group's Application Express SIG on Friday - my first experience of any UKOUG event. The event was a "sell out", with all available places taken up well in advance. This in itself is great news, because it shows that APEX really is taking off, which means I can be fairly confident I haven't backed the wrong horse career-wise.

David Peake, the APEX Product Manager, gave the first two presentations:

• The Latest and Greatest from Development


• APEX @ Oracle

The first of these was a tantalising glimpse at what will be in 3.2 (coming soon) and 4.0 (coming later this year hopefully). These are described in the APEX Statement of Direction, but we were able to see some of them in a live demo. I am particularly looking forward to 4.0 for its improved tabular forms, and for Dynamic Actions - i.e. a "rich client" experience without having to write any Javascript.

Dimitri Gielis and John Scott from Apex Evangelists both gave interesting presentations, on Charting in Apex and Dispelling Myths about APEX respectively. Dimitri's piqued my interest to go back and look at charts again, and John's provided a lot of useful ammo for fighting against the usual "Apex is just a power user toy" kind of myths.

There was also a presentation from Matt Nolan and Vincent Migue from e-DBA called "Using Apex to Expose your Business to the Web" - and their website is a fine example of that.

After a short Q&A forum, we all went to the local pub, which was a great opportunity to get to know some of these people a little better.

All in all, a great day out (good grief, how sad do I sound?!) and I'd definitely like to attend the next one, should there be one.

Apex Impact Analysis script (v2)

After receiving useful comments from Patrick Wolf on my previous post, here is an enhanced version of the script that also checks condition expressions. I'm not including condition_expression2 because I don't think that ever contains anything other than literal values (am I right?)

column obj_name format a50

undef search_text

accept search_text prompt "Enter search text: "

select application_id, page_id, 'Region' objtype, region_name obj_name, 'Source' usage_type
from   apex_application_page_regions
where lower(region_source) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name, 'Source' usage_type
from   apex_application_page_items
where  lower(item_source) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Process' obj_type, process_name obj_name, 'Source' usage_type
from   apex_application_page_proc
where  lower(process_source) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Branch' obj_type, TO_CHAR(process_sequence) obj_name, 'Source' usage_type
from   apex_application_page_branches
where  lower(branch_action) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Region' obj_type, region_name obj_name, 'Condition' usage_type
from   apex_application_page_regions
where lower(condition_expression1) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name, 'Condition' usage_type
from   apex_application_page_items
where  lower(condition_expression1) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name, 'Read Only' usage_type
from   apex_application_page_items
where  lower(read_only_condition_exp1) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Process' obj_type, process_name obj_name, 'Condition' usage_type
from   apex_application_page_proc
where  lower(condition_expression1) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Branch' obj_type, TO_CHAR(process_sequence) obj_name, 'Condition' usage_type
from   apex_application_page_branches
where  lower(condition_expression1) like  '%&search_text.%'
ORDER BY 1,2,3,4
/

Apex: impact analysis script

Ever wondered where a particular package is being used by your Apex application(s)? This simpleSQL Plus script may be of use:

column obj_name format a50

undef search_text

accept search_text prompt "Enter search text: "

select application_id, page_id, 'Region' objtype, region_name obj_name
from   apex_application_page_regions
where lower(region_source) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name
from   apex_application_page_items
where  lower(item_source) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Process' obj_type, process_name obj_name
from   apex_application_page_proc
where  lower(process_source) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Branch' obj_type, TO_CHAR(process_sequence) obj_name
from   apex_application_page_branches
where  lower(branch_action) like  '%&search_text.%'
ORDER BY 1,2,3,4
/

For example:

Enter search text: my_package.my_fun

APPLICATION_ID    PAGE_ID OBJTYPE OBJ_NAME
-------------- ---------- ------- ------------------------------------------
           101        123 Process Call my function
           101        127 Branch  30
           102        128 Region  My Report
           102        129 Region  My Query
           103         21 Item    P21_MY_ITEM

Feel free to tell me if (a) I have missed anything, or (b) I have re-invented the wheel and should be using some built-in Apex utility!

Apex 3.0

I have finally got access to the Apex 3.0 evaluation instance, after some problems reading the email containing my login credentials. It looks good. The "What's New in Apex 3.0" document details lots of interesting new features; however there a couple of things on my "wish list" that are not mentioned there, but have been added. These are not very exciting, but assist the developer:

• Region Static ID: now you can give your regions an identifier of your choice, rather than having to refer to Apex region IDs like R17346384974630405 in your code. Not only can your own identifiers be easier to read and remember, they won't change when you copy the page.
• #BUTTON_ID#: this new substitution variable means you can reference the button ID in the button template, and so enable manipulation of the button via Javascript.

Up to now I have devised my own work-arounds for these, but these will make life simpler in future.

No doubt I'll stumble across other goodies as I spend more time playing with, er I mean "evaluating", 3.0

Why I Love HTMLDB

Over the last 3 years or so I have got to know HTMLDB well, and have become a big fan of it - as a development tool, not as a "power user toy". I have successfully built and deployed a number of HTMLDB applications. I find it appeals to me in a way that .Net and J2EE never have. Why is this? Here are some reasons:

• all programming is in PL/SQL, my favourite programming language


• all the "boring bits" like pagination of record sets are built-in


• integration with the Oracle database is seamless


• you don't need a big application server to support it


• there is no question of reinventing the wheel by caching data, locking data, implementing business rules in the "middle tier" (there is NO "middle tier"!)

There are, I must admit, some shortcomings:

• it was not designed with large applications and large development teams in mind, so code and build management is a challenge (but one that we are learning to overcome)


• some of the functionality is not yet quite as robust as it could be (hopefully this will improve as the product matures)

However, HTMLDB doesn't seem to be to everyone's taste. Some hardened J2EE fanatics think it is a toy that is not good enough to be used by "programming professionals" - see for example this exchange I had on Oracle WTF.

I guess one of the key comments from the J2EE fan was this: "and as an ex Forms programmer NOTHING is a backward step from oracle forms!"

Now, I will admit that Oracle Forms is now getting rather old and tired - but in its heyday (around 1990-1995) it was a great product. What it did was integrate seamlessly with the database and do all the drudgery of paginating record sets etc. while you got on with implementing the business requirements. In fact, all the reasons I gave above for liking HTMLDB apply equally to Oracle Forms.

But for some years now, Forms has been looking like a "legacy system" - shoe-horned into the ubiquitous browser interface as WebForms but never looking quite at home there. And the alternatives I saw seemed to be a step backwards in terms of data management:

• ASP/.Net: write lots of VB code and hand-crank things like record pagination


• J2EE: write lots of Java code (which looks a LOT like C!), build your own classes to wrap around database objects etc. And cope with the "standards" that came and went like pop idols (JSF is it currently?)

Then I came across HTMLDB, promoted by Tom Kyte on his AskTom website. You could try it out for free, without even downloading anything, on Oracle's hosted HTMLDB development website. Of course, as with anything, there was a learning curve to get over, which probably took me a few months (elapsed: I was only playing with HTMLDB in my spare time.) But what was very soon apparent to me was that this could be the successor to Oracle Forms that I had been waiting for.

However, in their wisdom Oracle have decided to position HTMLDB as a "power user toy" to replace Excel and Access for very small databases - which it certainly can do. But this marketing undermines HTMLDB as a tool for "serious" application developers. It has been suggested to me that this may be because HTMLDB didn't come out of the Oracle Tools group, which of course has JDeveloper to promote.

Once I had built some small-scale applications with HTMLDB to replace aging Forms applications used only internally, the people I work for were sufficiently impressed to consider using it for something a lot bigger. I built a prototype which was received favourably, but we knew we ought to at least look at JDeveloper with ADF, since that is the approach Oracle recommend for moving away from Forms. So we got Oracle to send us someone to demonstrate JDeveloper to us. It all looked very slick, knocking up an "emp and dept" application in a few minutes - but nothing that couldn't have been done just as easily with HTMLDB. And when he tried to customise the application at our request, he quickly came unstuck. Soon afterwards, a colleague went to an Oracle "Developer Day" (or some such title) where JDeveloper was demoed agan. This time, the compilation process failed with a fatal error, and the demonstrator gave up and moved on to something else. What with these unimpressive demonstrations and the fact that our business logic already exists in the form of PL/SQL packages, it was very clear that HTMLDB was the only realistic option to replace the system within the timescale and budget allowed. (My impression is that JDeveloper might be really good in about 3 years time!)

But even if we were building a brand new system, my philosophy would be to build the business logic as PL/SQL packages and then build a light UI application on top of that: I really don't buy the whole J2EE way of doing things. It implies that the application is king, rather than the data. As Tom Kyte often says: applications come and go, but the data lasts "for ever".

So, for now at least, I am staking my future on HTMLDB rather than J2EE. Maybe I'll regret that one day, but I doubt it.

PL/SQL So Doku Solver

So Doku has taken over the UK, if not the world, in the last year. I am now addicted, but at first I couldn't see the point in these puzzle and prefered to write a program to solve them for me. What was the point in that? Absolutely none! But in case you ever feel the need to get a Su Doku puzzle solved without actualy doing it yourself, here is the code - think of it as a late Christmas present!

First, we need to create a table:

create table cells
  ( x number(1,0) not null
  , y number(1,0) not null
  , z varchar2(9)
  , done varchar2(1) not null
  , constraint cells_pk primary key (x, y)
  )
/

Then a package:

create or replace package cell_pkg as
  procedure solve(p_state in varchar2);
  procedure print;
end;
/

create or replace package body cell_pkg as
  procedure solve(p_state in varchar2)
  is
    v varchar2(1);
    cnt integer;
    processed integer;
    it_failed exception;
  begin
    -- Set up a clean board
    delete cells;
    for r in 1..9 loop
      for c in 1..9 loop
        insert into cells (x,y,z,done) values (r,c,'123456789','N');
      end loop;
    end loop;
    -- Apply initial state
    for r in 1..9 loop
      for c in 1..9 loop
        v := substr(p_state,(r-1)*9+c,1);
        if v between '1' and '9' then
          update cells
             set z = v
           where x = r
             and y = c;
        end if;
      end loop;
    end loop;
    -- Start processing
    loop
      processed := 0;
      -- Process cells that are solved but not yet marked as "done":
      for rec in (select * from cells where length(z)=1 and done='N')
      loop
        -- Remove that cell's value from the possible values for other cells
        -- in same row, column or square
        update cells
           set z = replace(z,rec.z)
         where (  x = rec.x
               or y = rec.y
               or (   floor((x-1)/3) = floor((rec.x-1)/3)
                  and floor((y-1)/3) = floor((rec.y-1)/3)
                  )
               )
           and (x <> rec.x or y <> rec.y); -- Exclude self!
        -- Now mark this cell as done
        update cells
           set done = 'Y'
         where x = rec.x and y = rec.y;
        -- Note how many cells processed on this pass
        processed := processed+1;
      end loop;
      -- Look for cells that are not solved, but where they are the only cell
      -- in their row, column or square containing a given value
      for i in 1..9 loop
        for rec in (select * from cells c1
                    where  length(z) > 1
                    and    z like '%'||i||'%'
                    and    (  not exists
                               (select null
                                from   cells c2
                                where  c2.x = c1.x -- Same row
                                and    (c1.x <> c2.x or c1.y <> c2.y) -- Exclude self!
                                and    c2.z like '%'||i||'%'
                               )
                           or not exists
                              (select null
                               from   cells c2
                               where  c2.y = c1.y -- Same column
                               and    (c1.x <> c2.x or c1.y <> c2.y) -- Exclude self!
                               and    c2.z like '%'||i||'%'
                               )
                           or not exists
                              (select null
                               from   cells c2
                               where  floor((c2.x-1)/3) = floor((c1.x-1)/3) -- Same
                               and    floor((c2.y-1)/3) = floor((c1.y-1)/3) -- square
                               and (c1.x <> c2.x or c1.y <> c2.y) -- Exclude self!
                               and c2.z like '%'||i||'%'
                              )
                           )
                   )
        loop
          update cells
             set z = i
           where x = rec.x
             and y = rec.y;
          -- Note how many cells processed on this pass
          processed := processed+1;
        end loop;
      end loop;
      -- Have we solved it yet?
      select count(*) into cnt from cells where length(z) > 1 and rownum = 1;
      exit when cnt = 0;
      -- No.  If we didn't achieve anything on this pass then give up
      if processed = 0 then
        raise it_failed;
      end if;
    end loop;
    print;
    dbms_output.put_line('SUCCESS!!!');
  exception
    when it_failed then
      print;
      dbms_output.put_line('FAILED!!!');
  end;

  procedure print
  is
  begin
    for rec in (select * from cells order by x,y)
    loop
      dbms_output.put(rec.z||' ');
      if rec.y = 9 then
        dbms_output.put_line('');
      end if;
    end loop;
  end;
end;
/

Now, how to use it. Let's take the following Su Doku puzzle as an example:

.6.1.4.5.
..83.56..
2.......1
8..4.7..6
..6...3..
7..9.1..4
5.......2
..72.69..
.4.5.8.7.

We can solve this as follows:

begin
cell_pkg.solve(' 6 1 4 5   83 56  2       18  4 7  6  6   3  7  9 1  45       2  72 69   4 5 8 7 ');
end;
/

(Enter all the grid values reading from left to right, top to bottom, including ALL spaces).
The output will look like this:

9 6 3 1 7 4 2 5 8
1 7 8 3 2 5 6 4 9
2 5 4 6 8 9 7 3 1
8 2 1 4 3 7 5 9 6
4 9 6 8 5 2 3 1 7
7 3 5 9 6 1 8 2 4
5 8 9 7 1 3 4 6 2
3 1 7 2 4 6 9 8 5
6 4 2 5 9 8 1 7 3
SUCCESS!!!

But perhaps you can do better? If you can write a better version, please let me know!

UPDATE 20 April 2006: Bill Magee has picked up the challenge and run with it, and his improved version is here.

What does GROUP BY CUBE do?

This is one of those "new" features that has actually been around for a long time, but I had never used it. Then recently I was asked to tune a long-running report, and it occured to me that it might just be what I needed.

The report consisted of 4 summary queries, each of which presented the same data but summarised by a different "dimension". Imagine it was based on the EMP table, then the report would show:

Employee Summary by Job
-----------------------

Job                                          # Emps  Total Sal
---------------------------------------- ---------- ----------
CLERK                                             4       4150
ANALYST                                           2       6000
MANAGER                                           3       8275
SALESMAN                                          4       5600
PRESIDENT                                         1       5000
                                         ---------- ----------
                                                 14      29025
                                                 

Employee Summary by Manager
---------------------------

Mgr                                          # Emps  Total Sal
---------------------------------------- ---------- ----------
7566                                              2       6000
7698                                              5       6550
7782                                              1       1300
7788                                              1       1100
7839                                              3       8275
7902                                              1        800
                                         ---------- ----------
                                                 14      29025

Employee Summary by Department
------------------------------

Dept                                         # Emps  Total Sal
---------------------------------------- ---------- ----------
10                                                3       8750
20                                                5      10875
30                                                6       9400
                                         ---------- ----------
                                                 14      29025

That is only 3 queries, but you get the idea.

Each query took 10 minutes to run (reasonable given the complexity of the query and the amount of data), so the whole report took 40 minutes.

My goal was to reduce the time to more like 10 minutes - perhaps by pre-computing the results grouped by all 4 dimensions into a temporary table and then reporting on that. However, it occured to me that GROUP BY CUBE might be what I needed, so I tried it out. Here is a simple query:

SQL> select job, mgr, deptno, count(*) numemps, sum(sal) tots
  2  from emp
  3  group by cube(job, mgr, deptno);

JOB              MGR     DEPTNO    NUMEMPS     TOTSAL
--------- ---------- ---------- ---------- ----------
                                         1       5000
                                        14      29025
                             10          1       5000
                             10          3       8750
                             20          5      10875
                             30          6       9400
                7566                     2       6000
                7566         20          2       6000
                7698                     5       6550
                7698         30          5       6550
                7782                     1       1300
                7782         10          1       1300
                7788                     1       1100
                7788         20          1       1100
                7839                     3       8275
                7839         10          1       2450
                7839         20          1       2975
                7839         30          1       2850
                7902                     1        800
                7902         20          1        800
CLERK                                    4       4150
CLERK                        10          1       1300
CLERK                        20          2       1900
CLERK                        30          1        950
CLERK           7698                     1        950
CLERK           7698         30          1        950
CLERK           7782                     1       1300
CLERK           7782         10          1       1300
CLERK           7788                     1       1100
CLERK           7788         20          1       1100
CLERK           7902                     1        800
CLERK           7902         20          1        800
ANALYST                                  2       6000
ANALYST                      20          2       6000
ANALYST         7566                     2       6000
ANALYST         7566         20          2       6000
MANAGER                                  3       8275
MANAGER                      10          1       2450
MANAGER                      20          1       2975
MANAGER                      30          1       2850
MANAGER         7839                     3       8275
MANAGER         7839         10          1       2450
MANAGER         7839         20          1       2975
MANAGER         7839         30          1       2850
SALESMAN                                 4       5600
SALESMAN                     30          4       5600
SALESMAN        7698                     4       5600
SALESMAN        7698         30          4       5600
PRESIDENT                                1       5000
PRESIDENT                    10          1       5000
PRESIDENT                    10          1       5000

52 rows selected.

Buried in amongst all those rows is all the data I need. There is a GROUPING function that can be used to see which group a row belongs to:

SQL> select job, mgr, deptno, count(*) numemps, sum(sal) totsal
  2  ,      grouping(job) gj, grouping(mgr) gm, grouping(deptno) gd
  3  from emp
  4  group by cube(job, mgr, deptno);

JOB              MGR     DEPTNO    NUMEMPS     TOTSAL         GJ         GM         GD
--------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                                         1       5000          1          0          1
                                        14      29025          1          1          1
                             10          1       5000          1          0          0
                             10          3       8750          1          1          0
                             20          5      10875          1          1          0
                             30          6       9400          1          1          0
                7566                     2       6000          1          0          1
                7566         20          2       6000          1          0          0
                7698                     5       6550          1          0          1
                7698         30          5       6550          1          0          0
                7782                     1       1300          1          0          1
                7782         10          1       1300          1          0          0
                7788                     1       1100          1          0          1
                7788         20          1       1100          1          0          0
                7839                     3       8275          1          0          1
                7839         10          1       2450          1          0          0
                7839         20          1       2975          1          0          0
                7839         30          1       2850          1          0          0
                7902                     1        800          1          0          1
                7902         20          1        800          1          0          0
CLERK                                    4       4150          0          1          1
CLERK                        10          1       1300          0          1          0
CLERK                        20          2       1900          0          1          0
CLERK                        30          1        950          0          1          0
CLERK           7698                     1        950          0          0          1
CLERK           7698         30          1        950          0          0          0
CLERK           7782                     1       1300          0          0          1
CLERK           7782         10          1       1300          0          0          0
CLERK           7788                     1       1100          0          0          1
CLERK           7788         20          1       1100          0          0          0
CLERK           7902                     1        800          0          0          1
CLERK           7902         20          1        800          0          0          0
ANALYST                                  2       6000          0          1          1
ANALYST                      20          2       6000          0          1          0
ANALYST         7566                     2       6000          0          0          1
ANALYST         7566         20          2       6000          0          0          0
MANAGER                                  3       8275          0          1          1
MANAGER                      10          1       2450          0          1          0
MANAGER                      20          1       2975          0          1          0
MANAGER                      30          1       2850          0          1          0
MANAGER         7839                     3       8275          0          0          1
MANAGER         7839         10          1       2450          0          0          0
MANAGER         7839         20          1       2975          0          0          0
MANAGER         7839         30          1       2850          0          0          0
SALESMAN                                 4       5600          0          1          1
SALESMAN                     30          4       5600          0          1          0
SALESMAN        7698                     4       5600          0          0          1
SALESMAN        7698         30          4       5600          0          0          0
PRESIDENT                                1       5000          0          0          1
PRESIDENT                                1       5000          0          1          1
PRESIDENT                    10          1       5000          0          0          0
PRESIDENT                    10          1       5000          0          1          0

Based on that, I can filter out the rows I'm not interested in and format the results like this:

SQL> select case when gj=0 and gm=1 and gd=1 then 'Job'
  2              when gj=1 and gm=0 and gd=1 then 'Manager'
  3              when gj=1 and gm=1 and gd=0 then 'Dept'
  4              end as keytype
  5  ,      case when gj=0 and gm=1 and gd=1 then job
  6              when gj=1 and gm=0 and gd=1 then to_char(mgr)
  7              when gj=1 and gm=1 and gd=0 then to_char(deptno)
  8              end as keyval
  9  ,      numemps, totsal
 10  from
 11  (
 12  select job, mgr, deptno, count(*) numemps, sum(sal) totsal
 13  ,      grouping(job) gj, grouping(mgr) gm, grouping(deptno) gd
 14  from emp
 15  group by cube(job, mgr, deptno)
 16  )
 17  where (gj=0 and gm=1 and gd=1)
 18  or    (gj=1 and gm=0 and gd=1)
 19  or    (gj=1 and gm=1 and gd=0);

KEYTYPE KEYVAL                                      NUMEMPS     TOTSAL
------- ---------------------------------------- ---------- ----------
Manager 7566                                              2       6000
        7698                                              5       6550
        7782                                              1       1300
        7788                                              1       1100
        7839                                              3       8275
        7902                                              1        800
                                                          1       5000
*******                                          ---------- ----------
sum                                                      14      29025
Job     ANALYST                                           2       6000
        CLERK                                             4       4150
        MANAGER                                           3       8275
        PRESIDENT                                         1       5000
        SALESMAN                                          4       5600
*******                                          ---------- ----------
sum                                                      14      29025
Dept    10                                                3       8750
        20                                                5      10875
        30                                                6       9400
*******                                          ---------- ----------
sum                                                      14      29025

... which gives me the breakdown I want in a single query, and provides the performance boost I was hoping for!