Monthly Archives: February 2010

Application Express 4.0 – Early Adopter 2 Now Available

apex40

If you didn’t already see the news, the EA2 release of APEX4.0 has just been opened up.

Head over to www.tryapexnow.com, sign up for a workspace and try out all the cool new features.

EA2 includes the following key features –

  • Websheets (this will be huge!)
  • New collection enhancements
  • Tree region
  • Debugging has been rewritten

and many more (see Joel’s blog post for a full description).

APEX 4.0 is going to be a huge release and will have (arguably) the biggest impact of any APEX release, it’s certainly interesting times being involved in the APEX community.

Views and Order By

Developers like views…they can simplify things and hide (protect us from) underlying complexity.

For example, would you rather write the query –

SELECT
  ename,
  department_name
FROM
  emp_vw

or

SELECT 
  e.ename, 
  d.dname 
FROM 
  emp e, 
  dept d 
WHERE 
  e.deptno = d.deptno;

It’s a rhetorical question so please don’t say “I prefer the second”…otherwise there’s no point me continuing this post. Anyway, that’s quite a simple example, imagine the view hides all sorts of complexity (analytics, subquerys etc).

The view gives us a single ‘point of truth’, we can tell developers “use that view, it contains all the information you need, you don’t need to understand all the logic yourself”, views are a very powerful tool for code re-usability (and simplification).

However, what if someone codes a view like this –

CREATE OR REPLACE VIEW empdept_vw AS
SELECT 
  e.ename, 
  d.dname,
  e.sal
FROM 
  emp e, 
  dept d 
WHERE 
  e.deptno = d.deptno
ORDER BY d.dname;

Notice the order by there? They probably/possibly created that view to use directly in a report, adding the ORDER BY into the view so they could just write –

SELECT * FROM empdept_vw

rather than

SELECT * FROM empdept_vw edv ORDER BY edv.dname

Well that’s fine, but what is someone else later on uses that view in this way –

SELECT * from empdept_vw edv ORDER BY edv.sal DESC

Well we’re now potentially making the database do more work because the query is sorting the data in a different way to the view sorts it (so therefore the sorting in the view is essentially redundant). The key point here is that the database cost based optimizer might be smart enough to figure out what you’re doing and eliminate the redundant sort, however there might be lots of cases where that doesn’t happen (consider the scenario where you have views built on top of views, with ORDER BY’s thrown in there at a deep level).

The cost-based optimizer gets smarter with every release of the Oracle datbase, however it will never (and I’m reasonably confident about this!) understand your data and requirements as much as you do (or rather as much as you should).

So, I would argue anytime you see a view with an ORDER BY in it you should really question why you’re doing that (rather than putting the ORDER BY in the query that uses the view). We can even make our job easier and look for any views that have an ORDER BY defined in them by querying DBA_VIEWS or USER_VIEWS. Unfortunately those views contain the text definition of the view in a LONG column rather than a CLOB, which means we can’t do a simple LIKE predicate –

JES@dbtest> select * from user_views where upper(text) like '%ORDER BY%'; 
                                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

We can get round this in a number of ways (such as creating a copy of the DBA_VIEWS or USER_VIEWS in a local table converting the LONG column to a CLOB etc), or we can use a different approach like this –

JES@dbtest> select 
  * 
from 
  user_views
where 
  dbms_xmlgen.getxml('select text from user_views where view_name = ''' || view_name || '''') like '%ORDER BY%' 

(note unfortunately I can’t remember who to credit with the DBMS_XMLGEN trick above, I saw it a long time ago and it’s been lost in the mists of time).

and yes…before you ask…in the schema I ran that query in I did find a view that had an unnecessary ORDER BY in 😉

I’d be interested to hear your thoughts on if you find ORDER BY’s in views a problem/issue/area-of-concern, or if it’s just me?

Increasing the speed of APEX Collections

I’d love to say “I’m quite often asked…”, but to be honest hardly anyone seems to use Collections in APEX (a very under utilised feature in my opinion).

However, a question does come up from time to time, often along the lines of “I am using a collection but it is slow…”.

Typically, you might create a collection in the following way –

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
  p_collection_name => 'COLL1',
  p_query           => 'select * from emp',
  p_generate_md5    => 'NO');

This would create a collection (called ‘COLL1’) and populate it with the results of the query (select * from emp).

So, let’s take a quick example. Firstly I want to use a sizable table

JES@dbtest> create table my_all_objects as select * from all_objects;

Table created.

JES@dbtest> select count(*) from my_all_objects;

  COUNT(*)
----------
     55853

So now I’m going to create an APEX application, which has the following code in a process:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
  p_collection_name => 'COLL1',
  p_query           => 'select * from my_all_objects',
  p_generate_md5    => 'NO');

It’s not really important to see the APEX application as such, what is important is to see how long it takes to run this process. There are lots of ways I can achieve that (putting in my own instrumentation etc), but APEX comes with a nice debug mode, which shows me timing information for everything that it does when you run a page. Running that page in debug mode I see something similar to –

0.03: ...Process "Create 1": PLSQL (AFTER_SUBMIT) APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY( p_collection_name => 'COLL1', p_query => 'SELECT * FROM my_all_objects', p_generate_md5 => 'NO');
18.57: ...Do not run process "Create_B", process point=AFTER_SUBMIT, condition type=, when button pressed=CREATE_B

The important bit here is the difference in timings (18.57 – 0.03) between the two lines, so that means my process took around 18.54 seconds to create the collection. 18 seconds is a long time for an user to wait for something to happen in a web application.

So, what can we do to improve things? Well if you look in the documentation for APEX_COLLECTION you’ll see there is another routine called CREATE_COLLECTION_FROM_QUERY_B. From the documentation –

The CREATE_COLLECTION_FROM_QUERY_B method offers significantly faster performance than the CREATE_COLLECTION_FROM_QUERY method by performing bulk SQL operations, but has the following limitations:

No column value in the select list of the query can be more than 2,000 bytes. If a row is encountered that has a column value of more than 2,000 bytes, an error will be raised during execution.

The MD5 checksum will not be computed for any members in the collection.

Ok, so if we can live with those limitations (the restriction about not computing the checksum is not as significant as it might at first seem, since we could also manually calculate it ourselves once the collection has been populated), then it’s worth trying it, so changing our code from –

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
  p_collection_name => 'COLL1',
  p_query           => 'SELECT * FROM my_all_objects',
  p_generate_md5    => 'NO');

to

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
  p_collection_name => 'COLL1',
  p_query           => 'SELECT * FROM my_all_objects');

as you can see, it’s a relatively minor change. So let’s run the page in debug mode again and see if there is an improvement:

0.03: ...Process "Create_B": PLSQL (AFTER_SUBMIT) APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B( p_collection_name => 'COLL1', p_query => 'SELECT * FROM my_all_objects');
5.25: Branch point: AFTER_PROCESSING

So this time it took around 5.2 seconds (5.25 – 0.03) to create the collection. (Note that I ran these tests multiple times to remove any caching issues).

Whilst I’d say 5.2 seconds is still a long time for a user to wait for a webpage, it’s a significant improvement over 18 seconds. Note your own results might vary with the difference in timings.

APEX Listener EA2 is out!

Just a quick note to say that the APEX Listener Early Adopter 2 is out.

Kris Rice blogged about the features and fixes available in this EA, I just quickly tested out the XLS upload (turns it into an APEX collection….very very cool!).

The APEX Listener is definitely introducing some interesting features (like the ability to record and replay sessions), so it will very soon outshine the Oracle HTTP Server in my opinion.

APEX@Your Oracle Usergroup

Just a quick note to say I often get asked if I can present at a Oracle usergroup meetings.

The answer is usually ‘yes I’d love to’, since –

1) I love to talk about and promote APEX
2) I love to travel

So, if you have an Oracle usergroup and you’re interested in promoting Oracle Application Express or looking for an APEX speaker, please feel free to drop me an email (john@sumneva.com) and hopefully we can arrange something.

Advert: Oracle APEX Training in Manchester, UK

As Dimitri has already blogged, this year we are focussing on doing a lot more Oracle Application Express training events.

With that in mind, the first public training I’m doing this year is 3 day Introduction to Oracle Application Express in Manchester on the 22nd March.

Here’s an except from the course description –

Introduction To Oracle APEX I

march-22

Introduction to Oracle Application Express is designed for those who have little to no interaction with APEX or for those looking to formalize their understanding of APEX. This course starts out with a review of the architecture of APEX, how it works, and some example projects that have been built with APEX. It then describes the Utilities and SQL Workshop facilities of APEX. The bulk of the class is spent on the application builder. Most of the core components are covered in Intro to APEX I, with hands-on exercises used to reinforce the concepts.

Numbers are strictly limited for this course, which you can book online now by clicking on the calendar.