Unpivoting Oracle APEX meta-data

There are APEX dictionary views for most of the data that represents the 'source' that is your APEX application meta-data.

Note: This post has beeen updated to reflect me not looking very hard, but I added a performance comparison... just because.

Today I found one place where I really wanted to run a query to find references to potential data - application substitution strings.

APEX Application Substitution Strings

And yes, I have a CSS rule to right-align those names, to make it easier to read.

I couldn't find any references in the APEX dictionary (correction, see below), so I looked in the Oracle data dictionary to find where it may live.

select * from all_tab_columns 
where column_name like 'SUB%03';

So not only are these stored in a place inaccessible to us mere-mortal developers, they are also stored in 20 sets of name/value columns - not rows.

This means if you have a dozen applications, with references in slightly different locations for each application, then string searches might be a pain.

Unpivot to the rescue!

select * from (
  select id app_id, alias, name -- key facts
      -- all the substitution stringz!
      ,substitution_string_01, substitution_value_01
      ,substitution_string_02, substitution_value_02
      ,substitution_string_03, substitution_value_03
      ,substitution_string_04, substitution_value_04
      ,substitution_string_05, substitution_value_05
      ,substitution_string_06, substitution_value_06
      ,substitution_string_07, substitution_value_07
      ,substitution_string_08, substitution_value_08
      ,substitution_string_09, substitution_value_09
      ,substitution_string_10, substitution_value_10
      ,substitution_string_11, substitution_value_11
      ,substitution_string_12, substitution_value_12
      ,substitution_string_13, substitution_value_13
      ,substitution_string_14, substitution_value_14
      ,substitution_string_15, substitution_value_15
      ,substitution_string_16, substitution_value_16
      ,substitution_string_17, substitution_value_17
      ,substitution_string_18, substitution_value_18
      ,substitution_string_19, substitution_value_19
      ,substitution_string_20, substitution_value_20
  from apex_190200.WWV_FLOWS -- direct from underlying view
)
unpivot (
(str, val) -- new columns
for rec in -- denoted by 
  ((substitution_string_01, substitution_value_01) as '01'
  ,(substitution_string_02, substitution_value_02) as '02'
  ,(substitution_string_03, substitution_value_03) as '03'
  ,(substitution_string_04, substitution_value_04) as '04'
  ,(substitution_string_05, substitution_value_05) as '05'
  ,(substitution_string_06, substitution_value_06) as '06'
  ,(substitution_string_07, substitution_value_07) as '07'
  ,(substitution_string_08, substitution_value_08) as '08'
  ,(substitution_string_09, substitution_value_09) as '09'
  ,(substitution_string_10, substitution_value_10) as '10'
  ,(substitution_string_11, substitution_value_11) as '11'
  ,(substitution_string_12, substitution_value_12) as '12'
  ,(substitution_string_13, substitution_value_13) as '13'
  ,(substitution_string_14, substitution_value_14) as '14'
  ,(substitution_string_15, substitution_value_15) as '15'
  ,(substitution_string_16, substitution_value_16) as '16'
  ,(substitution_string_17, substitution_value_17) as '17'
  ,(substitution_string_18, substitution_value_18) as '18'
  ,(substitution_string_19, substitution_value_19) as '19'
  ,(substitution_string_20, substitution_value_20) as '20'
  )
)
order by app_id, str 

This query transposes all the string/value columns into rows, each denonimated by the new "REC" column.

Note, this query can only be executed by those with the APEX Administrator Role, and if you want to have it within a view that could be executed by other schemas, then select access on wwv_flows is needed with grant option.

Columns unpivoted into rows

By placing the query in a view, I can now make queries like this to find any substitution strings in any applications that have date references.

select * from apx_app_sub_strings
where val like 'APP_DATE%';

Pretty neat, well at least as far as what I was trying to do.

This query may break in future versions, as it's based on an underlying, undocumented view.
I also think that once upon a time, there were fewer pairs.

Update: As the community quickly pointed out, I missed & forgot about a view already dedicated for such a task. I was too busy looking for a column number, when really I should have used APEX_APPLICATION_SUBSTITUTIONS.

I thought I'd see how 'they' solved the problem, and I was a little surprised to see a bunch of UNION statements.

SELECT ...
           f.substitution_string_18,
           f.substitution_value_18,
           f.substitution_string_19,
           f.substitution_value_19,
           f.substitution_string_20,
           f.substitution_value_20
      from wwv_flow_authorized auth,
           wwv_flows f
     where f.id = auth.application_id )
select workspace,
       workspace_display_name,
       application_id,
       application_name,
       substitution_string_01 as substitution_string,
       substitution_value_01  as substitution_value
  from substitution
where substitution_string_01 is not null
union all
select workspace,
       workspace_display_name,
       application_id,
       application_name,
       substitution_string_02 as substitution_string,
       substitution_value_02  as substitution_value
  from substitution
where substitution_string_02 is not null
union all...

After seeing this I couldn't help but run a brute for comparison, running both solutions x times.

iterations:5000
16.55 secs (.003310 secs per iteration) -- union all
 6.54 secs (.001308 secs per iteration) -- unpivot

I made sure the underlying join was the same, and I'm not all that surprised the unpivot did the job quicker.

Update 2: It appears 20.1 has gone with unpivot.

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.

UNION ALL - Performance & Accuracy

Time for my first on topic Oracle titbit - UNION ALL

Take the following example. I had two mutually exclusive tables, fairly flat and uncomplicated. Both queries returned 17 rows.

I wanted all rows returned, regardless of uniqueness.

The original solution used UNION.

SELECT col_a, col_b, col_c
FROM   table_1
UNION
SELECT col_a, col_b, col_c
FROM   table_2

I knew in this case not only was this not optimal, it was also inaccurate. This case should have used UNION ALL

SELECT col_a, col_b, col_c
FROM   table_1
UNION ALL
SELECT col_a, col_b, col_c
FROM   table_2

Such a minor difference in syntax, but in this case ~30% faster and 34 rows returned.
Had two of these records had the same column values, less than 34 rows would have been returned. Sure, in other scenarios this may be accurate, but in others this may cause data integrity issues, throw out reports or lose data.

In terms of speed, my basic test harness iterated these two queries 50000 times and got the following results:

7.27 secs -- union
.0001454 secs per iteration

5.54 secs -- union all
.0001108 secs per iteration

Should this query only be executed once per day, probably not an issue in terms of speed. However if it were required thousands of times per minute then this would probably make an impact.

I think every little bit helps. Take the anology of Aussie Rules - Australia's favourite sport. Commentators say all those one percenters count, they can make the difference to the end result. Imagine taking 100 separate Oracle efficiency tips and bundling them within your software. Collectively, how much wait time will it ultimately save the user?