Friday, 8 December 2017

Friday Fun SQL Lesson - union all

Our office kitchen is unavailable this Friday, so the call was put out for pub lunch.

After a couple of replies I decided to enter my reply, in full nerd mode.
select * from people_coming_to_lunch;

People
--------
Kate
Scott
Karolina

3 rows selected.
And of course one of the other SQL geeks (name redacted) replied to extend the data set.
select * from people_coming_to_lunch
union 
select 'Shanequa'
from dual;
And I couldn't help myself. I had to play the performance card and suggest that UNION ALL would be the appropriate usage, and should be the default you type out. Always. Until you decide otherwise.

That's because sorts are expensive. And a UNION will need sorting to check for duplicates.

That sort of all the rows isn't necessary
And it will sort the data set even if there is a unique key on the data.
create table people_coming_to_lunch (people varchar2(30));
insert into people_coming_to_lunch values ('Scott');
insert into people_coming_to_lunch values ('Kate');
insert into people_coming_to_lunch values ('Karolina');

create unique index lunch_people on people_coming_to_lunch(people);

select * from people_coming_to_lunch
union all
select 'Shanequa' from dual
By using UNION ALL instead of UNION, you're telling the database not to even bother sorting the set to eliminate any potential duplicates, since your advanced human brain knows there will be no duplicates.

With only a few rows, the timing of sheer throughput is barely noticable.
iterations:1000
     0.30 secs (.0003 secs per iteration)  -- UNION
     0.25 secs (.00025 secs per iteration) -- UNION ALL
 
iterations:10000
     1.72 secs (.000172 secs per iteration)
     1.09 secs (.000109 secs per iteration)
 
iterations:50000
    10.94 secs (.0002188 secs per iteration)
     8.48 secs (.0001696 secs per iteration)
So I turned it up a notch and added about 5000 rows to the table.
insert into people_coming_to_lunch
select table_name from all_tables;
 
5000 rows inserted
Here's the explain plan without the sort.

That's one less chunk of 5000 rows to process

Now the differences in performance stand out.
iterations:1000
     6.79 secs (.00679 secs per iteration) -- UNION
     2.85 secs (.00285 secs per iteration) -- UNION ALL
 
iterations:5000
    42.91 secs (.008582 secs per iteration)
    19.89 secs (.003978 secs per iteration)
 
iterations:5000
    31.70 secs (.00634 secs per iteration)
    22.83 secs (.004566 secs per iteration)
 
iterations:5000
    30.75 secs (.00615 secs per iteration)
    16.76 secs (.003352 secs per iteration)
Upto twice as long for the same statement?
No thanks, not when I could just type 4 extra characters to get an easy performance win.

Turns out this topic formed my first technical post. Back in 2009, after almost 10 years of using SQL, that was the first thing I blogged about. How about that.

Thursday, 7 December 2017

Exporting an APEX page plus Shared Components

Imagine, if you will, a really large application. Perhaps it's a reporting application, and you've just created a new page (report) and an associated breadcrumb. 

Now for migration. You have a choice between exporting the entire application, or you could just export the page. But what about the associated breadcrumb? What about the LOV?

This is where the Component Export can help.

You'll find it in the Tasks menu when you start to export your application.

Tip: Keep an eye out for these Task menus, they can 'hide' interesting features
From there you can find, then check the most recently modified components to your application, then press "Add to Export" to add them to what I would imagine would be an apex_collection.

Creating this type of page would be a good learning exercise

The final confirmation, with the additional option to export as of x minutes ago.



Upon export you'll receive a file that looks just like a normal export, just with your selected components.
f102_components.sql

There are advantages to exporting only selected components. For instance, during a full application install, existing users will remove existing user sessions for the application unless you use apex_application_install.set_keep_sessions on the command line.

Though are a few reasons to take care.

For example, when I exported/imported the List for my left sidebar menu, the menu disappeared from my runtime view.
I had to re-assign the list as the navigation menu.
Shared Components - User Interface - Nagivation Menu - Navigation Menu List.

Exporting without the list was ok, so for all good for data driven (dynamic) menus. In that case the page should only appear in the menu when the relevant security tables say so.

And the export may not be as granular as you need. Or perhaps you'll forget components. Or perhaps your changes overlap with others.

Component export is fit for some purposes, and has been around forever. Give it a go.

And wouldn't you know, turns out it was a year ago today that I also felt compelled to blog about this very topic.



Wednesday, 6 December 2017

Exploring AJAX presentation

Wow, what a busy year. My blog archive is pretty thin this year, isn't it?

I'm moving house next week, but I'm itching to get back into blogging about my tech journey.
I need to get back into learning mode, I have some Kscope ideas to foster!

Perth had a humble but successful conference last week. It was impressive to see Oracle APEX dominating the developer track, with thanks to ACE Director Dimitri stepping up and delivering four engaging sessions in one day.

A few months ago I drafted a session exploring the concept of AJAX, and how it fits within the world of web application development. I was aiming for a more widespread crowd, hoping to fill some gaps by doing something as non-technical as possible.

Next minute, among what seems like many other things, I'm preparing to move house, so I came in a little underprepared.
Here's my slide-deck, but it's missing the passionate space-nerd adding enthusiasm to the content ;p
https://prezi.com/dwibb_dismt5/exploring-ajax-from-afar/



I'll probably post about certain frames that are worth a mention.

I'll have my regular hands on a 5.1 environment soon, I've got some catching up to do before 5.2 arrives. Bring on OracleJET.