Tuesday, March 26, 2024

Setting the CARDINALITY (undocumented) hint on two tables

I was trying to solve a performance problem in a query containing a left join on two global temporary tables. 

Clearly if you run an Explain Plan on such tables, the cardinality is totally wrong, but theoretically you should be able to "simulate" the right cardinality with the undocumented hint CARDINALITY.

The second problem is that apparently such hint accepts only one table or I couldn't find any example of usage when you need to set the value for two tables.

So I came up with the following workaround using the WITH clause.
If the resulting plan is correct, then it's easily understood why a query with a cost of 7513724 takes ages to return the results.

WITH 
zoo_01 as (
select /*+ CARDINALITY(gtt_rpt_zoo_01 38974) */ * from reports.gtt_rpt_zoo_01),
zoo_03 as (
select /*+ CARDINALITY(gtt_rpt_zoo_03 38967) */ * from reports.gtt_rpt_zoo_03)
SELECT ...
FROM zoo_01, zoo_03
WHERE ...

If there is an alternate method, I'd be glad to know...

Friday, March 15, 2024

Order of appearance of functions and subqueries in a WITH ... SELECT

I don't know if it is the same for you but I tend to forget certain details, that's why often I need to write down some examples in this place to save time for the future.

For instance one thing that I tend to forget is the order in which you can specify functions and subqueries inside the same WITH. 

Functions must come first, then subqueries, if any.

Here is a working example, two functions, two subqueries and the final SELECT.

with 
   -- first function
function msg ( msgnum in integer) return varchar2
as
 s varchar2(200);
 i pls_integer;
begin
 i := utl_lms.get_message(msgnum, 'rdbms', 'ora', 'italian', s);
 return 'ORA-'||to_char(msgnum,'TM9')||': '||s;
end;
  -- second function
function msg2 ( msgnum in integer) return varchar2
as
 s varchar2(200); i pls_integer; begin i := utl_lms.get_message(msgnum, 'rdbms', 'ora', 'italian', s); return 'ORA-'||to_char(msgnum,'TM9')||': '||s; end; -- some subqueries x as (select 13367 as a from dual ), y as (select 13368 as a from dual) -- final query select msg(a) from x union all select msg2(a) from y;
According to a performance comparison made by Tim Hall, inline functions seems to be faster than equivalent ordinary functions unless PRAGMA UDF has been specified in the latter, in which case ordinary functions seem to outperform the inline ones.

Adding PRAGMA UDF to inline functions doesn't seem to make any difference.

Tuesday, March 05, 2024

When #OWNER# is not the OWNER you expected

Oracle provides developers with a ton of dictionary views, many of which containing a column called OWNER, basically most of the DBA* and ALL* views contain such column, with a few exceptions, for instance compare view ALL_INDEXES with ALL_IND_COLUMNS and try to figure out why the latter comes with a column called INDEX_OWNER while the former comes with a simple OWNER.

If you are developing an APEX report based on some of these views and for some reason the report comes with a link to another page where you pass the value of the OWNER column as a parameter using the #OWNER# substitution string, as APEX itself suggests if you click on the helper icon in the link builder, you won't get the OWNER you expect, because #OWNER# is a undocumented APEX substitution string that gets replaced with the name of the primary schema attached to the workspace.

I created a simple application on APEX.ORACLE.COM to show this behavior (open door credentials).

I believe that someone should update the section dedicated to the Substitution strings in the APEX Builder manual and add #OWNER# to the list and I am almost 100% sure that #OWNER# is a legitimate substitution string that gets replaced everywhere, not just accidentally in report links.

So, at the end of the day, if you need to pass the owner of something inside a link, you must rename the column, and I'd recommend avoiding the word OWNER altogether, use SCHEMA instead.


 

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles