Skip navigation.

XTended Oracle SQL

Syndicate content
XTended Oracle SQL
Updated: 18 hours 2 min ago

To_char, Infinity and NaN

Mon, 2014-03-31 15:23

Funny that oracle can easily cast ‘nan’,'inf’,'infinity’,'-inf’,'-infinity’ to corresponding binary_float_infinity,binary_double_nan, but there is no any format models for to_char(binary_float_infinity,format) or to_binary_***(text_expr,format) that can output the same as to_char(binary_float_infinity)/to_binary_float(‘inf’) without format parameter:

If a BINARY_FLOAT or BINARY_DOUBLE value is converted to CHAR or NCHAR, and the input is either infinity or NaN (not a number), then Oracle always returns the pound signs to replace the value.

Little example:

SQL> select to_binary_float('inf') from dual;

TO_BINARY_FLOAT('INF')
----------------------
                   Inf

SQL> select to_binary_float('inf','9999') from dual;
select to_binary_float('inf','9999') from dual
                       *
ERROR at line 1:
ORA-01722: invalid number

SQL> select
  2     to_char(binary_float_infinity)         without_format
  3    ,to_char(binary_float_infinity,'99999') with_format
  4    ,to_char(1e6d,'99999')                  too_large
  5  from dual;

WITHOUT_F WITH_FORMAT        TOO_LARGE
--------- ------------------ ------------------
Inf       ######             ######

SQL> select to_char(0/0f) without_format, to_char(0/0f,'tme') with_format from dual;

WITHOUT_F WITH_FORMAT
--------- --------------------------------------------------------------------------
Nan       ################################################################

ps. it’s just crossposting of my old blog.

Categories: Development

Deterministic functions, result_cache and operators

Sun, 2014-03-30 16:51

In previous posts about caching mechanism of determinstic functions I wrote that cached results are kept only between fetch calls, but there is one exception from this rule: if all function parameters are literals, cached result will not be flushed every fetch call.
Little example with difference:

SQL> create or replace function f_deterministic(p varchar2)
  2     return varchar2
  3     deterministic
  4  as
  5  begin
  6     dbms_output.put_line(p);
  7     return p;
  8  end;
  9  /
SQL> set arrays 2 feed on;
SQL> set serverout on;
SQL> select
  2     f_deterministic(x) a
  3    ,f_deterministic('literal') b
  4  from (select 'not literal' x
  5        from dual
  6        connect by level<=10
  7       );

A                              B
------------------------------ ------------------------------
not literal                    literal
not literal                    literal
not literal                    literal
not literal                    literal
not literal                    literal
not literal                    literal
not literal                    literal
not literal                    literal
not literal                    literal
not literal                    literal

10 rows selected.

not literal
literal
not literal
not literal
not literal
not literal
not literal

As you can see, ‘literal’ was printed once, but ‘not literal’ was printed 6 times, so it was returned from cache 4 times.

Also i want to show the differences in consistency between:
1. Calling a function with determinstic and result_cache;
2. Calling an operator for function with result_cache;
3. Calling an operator for function with deterministic and result_cache;

In this example I will do updates in autonomouse transactions to emulate updates in another session during query execution:
Spoiler:: Tables and procedures with updates SelectShow

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1 as select 1 id from dual;
create table t2 as select 1 id from dual;
create table t3 as select 1 id from dual;

create or replace procedure p1_update as
  pragma autonomous_transaction;
begin
   update t1 set id=id+1;
   commit;
end;
/
create or replace procedure p2_update as
  pragma autonomous_transaction;
begin
   update t2 set id=id+1;
   commit;
end;
/
create or replace procedure p3_update as
  pragma autonomous_transaction;
begin
   update t3 set id=id+1;
   commit;
end;
/


Spoiler:: Variant 1
SelectShow

create or replace function f1(x varchar2) return number result_cache deterministic
as
  r number;
begin
   select id into r from t1;
   p1_update;
   return r;
end;
/


Spoiler:: Variant 2
SelectShow

create or replace function f2(x varchar2) return number result_cache
as
  r number;
begin
   select id into r from t2;
   p2_update;
   return r;
end;
/
create or replace operator o2
binding(varchar2)
return number
using f2
/


Spoiler:: Variant 3
SelectShow

create or replace function f3(x varchar2) return number result_cache deterministic
as
  r number;
begin
   select id into r from t3;
   p3_update;
   return r;
end;
/
create or replace operator o3
binding(varchar2)
return number
using f3
/


Test:

SQL> set arrays 2;
SQL> select
  2     f1(dummy) variant1
  3    ,o2(dummy) variant2
  4    ,o3(dummy) variant3
  5  from dual
  6  connect by level<=10;

  VARIANT1   VARIANT2   VARIANT3
---------- ---------- ----------
         1          1          1
         2          1          1
         2          1          1
         3          1          1
         3          1          1
         4          1          1
         4          1          1
         5          1          1
         5          1          1
         6          1          1

10 rows selected.

SQL> /

  VARIANT1   VARIANT2   VARIANT3
---------- ---------- ----------
         7         11         11
         8         11         11
         8         11         11
         9         11         11
         9         11         11
        10         11         11
        10         11         11
        11         11         11
        11         11         11
        12         11         11

10 rows selected.

We can see that function F1 returns same results every 2 execution – it is equal to fetch size(“set arraysize 2″),
operator O2 and O3 return same results for all rows in first query execution, but in the second query executions we can see that they are incremented by 10 – it’s equal to number of rows.
What we can learn from that:
1. A calling a function F1 with result_cache and deterministic reduces function executions, but all function results inconsistent with query;
2. Operator O2 returns consistent results, but function is always executed because we invalidating result_cache every execution;
3. Operator O3 works as well as operator O2, without considering that function is deterministic.

All tests scripts: tests.zip

Categories: Development

When v$sesstat statistics are updated

Thu, 2014-03-20 18:41

Craig Shallahamer wrote excellent article “When is v$sesstat really updated?”.
And my today post just a little addition and correction about the difference of updating ‘Db time’ and ‘CPU used by this session’ statistics.

Test #1

In this test I want to show that the statistics will be updated after every fetch call.
I have set arraysize=2, so sql*plus will fetch by 2 rows:
(full script)

-- Result will be fetched by 2 rows:
set arraysize 2;
-- this query generates CPU consumption 
-- in the scalar subquery on fetch phase,
-- so CPU consumption will be separated 
-- into several periods between fetch calls:
with gen as (
            select/*+ materialize */
               level n, lpad(level,400) padding
            from dual
            connect by level<=200
            )
    ,stat as (
            select/*+ inline */
               sid,name,value 
            from v$mystat st, v$statname sn
            where st.statistic#=sn.statistic#
              and sn.name in ('DB time'
                             ,'CPU used by this session'
                             ,'user calls'
                             ,'recursive calls')
            )
--first rows just for avoiding SQL*Plus effect with fetching 1 row at start,
-- others will be fetched by "arraysize" rows:
select null rn,null cnt,null dbtime,null cpu,null user_calls, null recursive_calls from dual
union all -- main query:
select
   rownum rn
  ,(select count(*) from gen g1, gen g2, gen g3 where g1.n>g2.n and g1.n*0=main.n*0) cnt
  ,(select value from stat where sid*0=n*0 and name = 'DB time'                    ) dbtime
  ,(select value from stat where sid*0=n*0 and name = 'CPU used by this session'   ) cpu
  ,(select value from stat where sid*0=n*0 and name = 'user calls'                 ) user_calls
  ,(select value from stat where sid*0=n*0 and name = 'recursive calls'            ) recursive_calls
from gen main
where rownum<=10;
set arraysize 15;

Test results:

SQL> @tests/dbtime

        RN        CNT     DBTIME        CPU USER_CALLS RECURSIVE_CALLS
---------- ---------- ---------- ---------- ---------- ---------------

         1    3980000      12021      11989        200             472
         2    3980000      12021      11989        200             472
         3    3980000      12121      12089        201             472
         4    3980000      12121      12089        201             472
         5    3980000      12220      12186        202             472
         6    3980000      12220      12186        202             472
         7    3980000      12317      12283        203             472
         8    3980000      12317      12283        203             472
         9    3980000      12417      12383        204             472
        10    3980000      12417      12383        204             472

As you can see the statistics are updated after every fetch call.

Test #2

Now since we already tested simple sql query, I want to do a little bit more complicated test with PL/SQL:
I’m going to write single PL/SQL block with next algorithm:
1. Saving stats
2. Executing some pl/sql code with CPU consumption
3. Getting statistics difference
4. Starting query from first test
5. Fetch 10 rows
6. Getting statistics difference
7. Fetch next 10 rows
8. Getting statistics difference
9. Fetch next 10 rows
10. Getting statistics difference
And after executing this block, i want to check statistics.

Full script:

set feed off;

-- saving previous values
column st_dbtime      new_value prev_dbtime      noprint;
column st_cpu_time    new_value prev_cputime     noprint;
column st_user_calls  new_value prev_user_calls  noprint;
column st_recur_calls new_value prev_recur_calls noprint;

select   max(decode(sn.NAME,'DB time'                  ,st.value))*10 st_dbtime
        ,max(decode(sn.NAME,'CPU used by this session' ,st.value))*10 st_cpu_time
        ,max(decode(sn.NAME,'user calls'               ,st.value))    st_user_calls
        ,max(decode(sn.NAME,'recursive calls'          ,st.value))    st_recur_calls
from v$mystat st, v$statname sn
where st.statistic#=sn.statistic# 
  and sn.name in ('DB time','CPU used by this session'
                 ,'user calls','recursive calls'
                 )
/
-- variable for output from pl/sql block: 
var output varchar2(4000);

prompt Executing test...;
----- main test:
declare
   cnt int;
   st_dbtime      number; 
   st_cpu_time    number; 
   st_user_calls  number; 
   st_recur_calls number; 
   cursor c is 
      with gen as (select/*+ materialize */
                     level n, lpad(level,400) padding
                   from dual
                   connect by level<=200)
      select
          rownum rn
        , (select count(*) from gen g1, gen g2, gen g3 where g1.n>g2.n and g1.n*0=main.n*0) cnt
      from gen main
      where rownum<=60;
   
   type ctype is table of c%rowtype;
   c_array ctype;
   
   procedure SnapStats(descr varchar2:=null)
   is
      st_new_dbtime      number;
      st_new_cpu_time    number;
      st_new_user_calls  number;
      st_new_recur_calls number;
   begin
      select   max(decode(sn.NAME,'DB time'                 ,st.value))*10 st_dbtime
              ,max(decode(sn.NAME,'CPU used by this session',st.value))*10 st_cpu_time
              ,max(decode(sn.NAME,'user calls'              ,st.value))    st_user_calls
              ,max(decode(sn.NAME,'recursive calls'         ,st.value))    st_recur_calls
          into st_new_dbtime,st_new_cpu_time,st_new_user_calls,st_new_recur_calls
      from v$mystat st, v$statname sn
      where st.statistic#=sn.statistic#
        and sn.name in ('DB time','CPU used by this session'
                       ,'user calls','recursive calls'
                       );
      if descr is not null then
         :output:= :output || descr ||':'||chr(10)
                || 'sesstat dbtime:     ' || (st_new_dbtime      - st_dbtime      )||chr(10)
                || 'sesstat cputime:    ' || (st_new_cpu_time    - st_cpu_time    )||chr(10)
                || 'sesstat user calls: ' || (st_new_user_calls  - st_user_calls  )||chr(10)
                || 'sesstat recur calls:' || (st_new_recur_calls - st_recur_calls )||chr(10)
                || '======================================'||chr(10);
      end if;
      st_dbtime      := st_new_dbtime     ;
      st_cpu_time    := st_new_cpu_time   ;
      st_user_calls  := st_new_user_calls ;
      st_recur_calls := st_new_recur_calls;
   end;
   
begin
   -- saving previous stats:
   SnapStats;

   -- generating cpu load:
   for i in 1..1e7 loop
      cnt:=cnt**2+cnt**1.3-cnt**1.2;
   end loop;
   -- getting new stats:
   SnapStats('After pl/sql loop');
   
   open c;
   SnapStats('After "open c"');
   fetch c bulk collect into c_array limit 10;
   SnapStats('After fetch 10 rows');
   fetch c bulk collect into c_array limit 10;
   SnapStats('After fetch 20 rows');
   fetch c bulk collect into c_array limit 10;
   SnapStats('After fetch 30 rows');
   close c;
   SnapStats('After close c');
end;
/ 

prompt 'Delta stats after statement(ms):';
select   max(decode(sn.NAME,'DB time'                 ,st.value))*10
          - &&prev_dbtime      as delta_dbtime
        ,max(decode(sn.NAME,'CPU used by this session',st.value))*10
          - &&prev_cputime     as delta_cpu_time
        ,max(decode(sn.NAME,'user calls'              ,st.value))  
          - &&prev_user_calls  as delta_user_calls
        ,max(decode(sn.NAME,'recursive calls'         ,st.value))  
          - &&prev_recur_calls as delta_recur_calls
from v$mystat st, v$statname sn
where st.statistic#=sn.statistic# 
  and sn.name in ('DB time','CPU used by this session'
                 ,'user calls','recursive calls'
                 )
/
prompt 'Test results:';
col output format a40;
print output;
set feed off;

Output:

SQL> @tests/dbtime2

Executing test...
'Delta stats after statement(ms):'

DELTA_DBTIME DELTA_CPU_TIME DELTA_USER_CALLS DELTA_RECUR_CALLS
------------ -------------- ---------------- -----------------
       18530          18460                5                33

Test results:
OUTPUT
----------------------------------------
After pl/sql loop:
sesstat dbtime:     0
sesstat cputime:    4350
sesstat user calls: 0
sesstat recur calls:2
======================================
After "open c":
sesstat dbtime:     0
sesstat cputime:    20
sesstat user calls: 0
sesstat recur calls:4
======================================
After fetch 10 rows:
sesstat dbtime:     0
sesstat cputime:    4680
sesstat user calls: 0
sesstat recur calls:2
======================================
After fetch 20 rows:
sesstat dbtime:     0
sesstat cputime:    4680
sesstat user calls: 0
sesstat recur calls:2
======================================
After fetch 30 rows:
sesstat dbtime:     0
sesstat cputime:    4690
sesstat user calls: 0
sesstat recur calls:2
======================================
After close c:
sesstat dbtime:     0
sesstat cputime:    0
sesstat user calls: 0
sesstat recur calls:3
======================================

We can notice that “CPU time” is updated at the same time as recursive calls, but “DB time” is updated only with “User calls”. Although this difference is not so important(because in most cases we can use other statistics in sum), but i think, if you want to instrument some code, it gives reason to check out desirable statistics for update time.

Categories: Development

Single SQL vs SQL+PL/SQL

Fri, 2014-02-28 14:58

Everyone knows Tom Kyte’s mantra:

You should do it in a single SQL statement if at all possible.

But we all know that “Every rule has an exception
There are many different cases when pl/sql with sql can be more efficient than only sql, and i dont want to catalog them. I just want to show a couple examples of such exceptions:

1. Running totals by several dimensions

Simple example from forum:

select dt,
       dim1,
       dim2,
       val,
       sum(val) over(partition by dim1 order by dt) dim1_cumulative_sum,
       sum(val) over(partition by dim2 order by dt) dim2_cumulative_sum,
       sum(val) over(partition by dim1, dim2 order by dt) dim1_dim2_cumulative_sum
  from mg_t
 order by dt;

This query will be very hard for big data sets, so we can do it efficiently with pl/sql:

create or replace function xf_to_drop return xt2_to_drop pipelined
is
   type tt  is table of number index by pls_integer;
   type tt2 is table of tt index by pls_integer;
   dim1_c tt;
   dim2_c tt;
   dim12_c tt2;
begin
   for r in (
            select dt,
                   dim1,
                   dim2,
                   val
              from mg_t
             order by dt
   )
   loop
      dim1_c(r.dim1):=case when dim1_c.exists(r.dim1) then dim1_c(r.dim1) else 0 end + r.val;
      dim2_c(r.dim1):=case when dim2_c.exists(r.dim1) then dim2_c(r.dim1) else 0 end + r.val;
      dim12_c(r.dim1)(r.dim2):=case
                                  when dim12_c.exists(r.dim1)
                                   and dim12_c(r.dim1).exists(r.dim2)
                                  then dim12_c(r.dim1)(r.dim2)
                                  else 0
                               end + r.val;
      pipe row (xt1_to_drop( r.dt
                            ,r.dim1
                            ,r.dim2
                            ,r.val
                            ,dim1_c(r.dim1)
                            ,dim1_c(r.dim1)
                            ,dim12_c(r.dim1)(r.dim2)
                           ));
   end loop;
end;
/
Spoiler:: Full example SelectShow
create table mg_t as
select trunc(sysdate) + level/1440 dt,
       trunc(3 * dbms_random.value()) dim1,
       trunc(3 * dbms_random.value()) dim2,
       trunc(100 * dbms_random.value()) val
  from dual
connect by level <= 3e6;

create type xt1_to_drop is object(
       dt                  date
      ,dim1                number
      ,dim2                number
      ,val                 number
      ,dim1_cumulative_sum number
      ,dim2_cumulative_sum number
      ,dim1_dim2_cumulative_sum number
);
create type xt2_to_drop as table of xt1_to_drop;

create or replace function xf_to_drop return xt2_to_drop pipelined
is
   type tt is table of number index by pls_integer;
   type tt2 is table of tt index by pls_integer;
   dim1_c tt;
   dim2_c tt;
   dim12_c tt2;
begin
   for r in (
            select dt,
                   dim1,
                   dim2,
                   val
              from mg_t
             order by dt
   )
   loop
      dim1_c(r.dim1):=case when dim1_c.exists(r.dim1) then dim1_c(r.dim1) else 0 end + r.val;
      dim2_c(r.dim1):=case when dim2_c.exists(r.dim1) then dim2_c(r.dim1) else 0 end + r.val;
      dim12_c(r.dim1)(r.dim2):=case
                                  when dim12_c.exists(r.dim1)
                                   and dim12_c(r.dim1).exists(r.dim2)
                                  then dim12_c(r.dim1)(r.dim2)
                                  else 0
                               end + r.val;
      pipe row (xt1_to_drop( r.dt,r.dim1,r.dim2,r.val,dim1_c(r.dim1),dim1_c(r.dim1),dim12_c(r.dim1)(r.dim2)));
   end loop;
end;
/
exec for r in (select * from table(xf_to_drop)) loop null; end loop;

2. Finding connected components

Assume that we have big table with many-to-many relationship:

create table test (clientid NUMBER(10), accountid NUMBER(10));

How we can find all connected groups?

This example also taken from our russian forum and there was very good and simple sql-only solution, but it’s not efficient on big data sets:

select min(group_member_id) as group_max_id, accountid, clientid
  from  (select clientid as group_member_id
         , connect_by_root accountid as accountid
         , connect_by_root clientid  as clientid
      from test
      connect by nocycle decode(accountid, prior accountid, 1, 0)
                       + decode(clientid,  prior clientid,  1, 0)
                       = 1
  ) a
  group by accountid, clientid
  order by group_max_id, accountid
/

We can try to remember algorithms courses and adopt one of the several algorithms for connected components:
Spoiler:: Based on weighted quick-union algorithm SelectShow

declare
   type int_array    is table of pls_integer index by pls_integer;
   type arr_elems    is table of sys.ku$_objnumset index by pls_integer;
   root              int_array;
   root_elems        arr_elems;

   n        int;
   clients  int_array;
   accounts int_array;

    l integer:=dbms_utility.get_time();

    procedure print(v in varchar2) is
    begin
      dbms_output.put_line(to_char((dbms_utility.get_time-l)/100,'0999.99')||' '||v);
      l:=dbms_utility.get_time();
    end;

   
   function get_root(n int) return pls_integer is
   begin
      if root.exists(n) then 
         return root(n);
      else 
         return null;
      end if;
   end;
   
   procedure update_root(old_root pls_integer,new_root pls_integer) is
      i       pls_integer;
      elem    pls_integer;
      cnt_old pls_integer;
      cnt_new pls_integer;
   begin
      if old_root!=new_root then 
         --root_elems(new_root):=root_elems(new_root) multiset union all root_elems(old_root);
         cnt_old:=root_elems(old_root).count;
         cnt_new:=root_elems(new_root).count;
         root_elems(new_root).extend(cnt_old);
         for i in 1..cnt_old
         loop
            elem := root_elems(old_root)(i);
            root(elem):=new_root;
            root_elems(new_root)(cnt_new+i):=elem;
         end loop;
         root_elems(old_root).delete;
      end if;
   end;
   
   procedure add_elem(p_root pls_integer, p_elem pls_integer) is
   begin
      if not root_elems.exists(p_root) then
         root_elems(p_root):=sys.ku$_objnumset(p_elem);
      else
         root_elems(p_root).extend();
         root_elems(p_root)(root_elems(p_root).count):=p_elem;
      end if;
   end;
   
   procedure add_link(clientid pls_integer,accountid pls_integer) is
      r1       pls_integer;
      r2       pls_integer;
      new_root pls_integer;
   begin
      r1:=get_root(clientid);
      r2:=get_root(accountid);
      
      if r1 is null or r2 is null then
         new_root := coalesce(r1,r2,clientid);
         if r1 is null then add_elem(new_root,clientid ); root(clientid) :=new_root; end if;
         if r2 is null then add_elem(new_root,accountid); root(accountid):=new_root; end if;
      else
         new_root := least(r1,r2);
         root(clientid) :=new_root;
         root(accountid):=new_root;
         update_root(greatest(r1,r2),new_root);
      end if;
   end;
   
   function str_format(p int) return varchar2 is
   begin
      return utl_lms.format_message('(%d, %d) = group #%d'
                                   ,clients(p)
                                   ,accounts(p)
                                   ,get_root(clients(p))
                                   );
   end;
begin
   print('start');
   select clientid,accountid 
          bulk collect into clients,accounts
   from test 
--   where rownum<=1000
   ;
   print('fetched');
   n:=clients.count;
   dbms_output.put_line('count='||n);
   for i in 1..n loop
      add_link(clients(i),accounts(i));
   end loop;
   print('processed');
---
/*
   for i in 1..n loop
      dbms_output.put_line(str_format(i));
   end loop;
--   */
end;


We can also try even more interesting special algorithms for parallel processing:
CONNECTED COMPONENTS ALGORITHMS
FOR MESH-CONNECTED PARALLEL COMPUTERS

Categories: Development

Inlist iterator again

Thu, 2014-02-20 17:30

Several months ago I wrote about avoiding inlist iterator, but this post about how to force inlist iterator in queries like that:

select *
from xt_test1 t1
    ,xt_test2 t2
where 
     t1.a = :a 
 and t2.id in (a,b)

i.e. when we need to get rows from big table using index by list of values from another table.

Test tables:
Spoiler:: ddl SelectShow

drop table xt_test1 purge;
drop table xt_test2 purge;

create table xt_test1(id, a not null, b not null, constraint xt_test1_pk primary key (id))
as
 select level, level*2-1, level*2
 from dual
 connect by level <=10000
/

create table xt_test2(id, pad,constraint xt_test2_pk primary key(id))
as
 select level,lpad(level,20)
 from dual
 connect by level <=10000
/
begin
   dbms_stats.gather_table_stats(user,'XT_TEST1');
   dbms_stats.gather_table_stats(user,'XT_TEST2');
end;
/

By default we can get only bad plan with concatenation and 2 full table scans for this query:

Plan hash value: 667870028

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    74 |    22   (0)| 00:00:01 |
|   1 |  CONCATENATION                |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     1 |    37 |    11   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |             |     1 |    37 |    11   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     1   (0)| 00:00:01 |
|   7 |   NESTED LOOPS                |             |     1 |    37 |    11   (0)| 00:00:01 |
|   8 |    NESTED LOOPS               |             |     1 |    37 |    11   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     0   (0)| 00:00:01 |
|  11 |    TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T1"."A"=TO_NUMBER(:A))
   5 - access("T2"."ID"="B")
   9 - filter("T1"."A"=TO_NUMBER(:A))
  10 - access("T2"."ID"="A")
       filter(LNNVL("T2"."ID"="B"))

27 rows selected.

I have tried several different options and found one interesting thing:

SQL> explain plan for
  2  select *
  3  from xt_test1 t1
  4      ,xt_test2 t2
  5  where
  6       t1.a = :a
  7   and t2.rowid = (select x.rowid rid from xt_test2 x where x.id in (t1.a,t1.b))
  8  /

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 951366071

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    37 | 14061   (1)| 00:02:49 |
|   1 |  NESTED LOOPS               |             | 10000 |   361K|    11   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY USER ROWID| XT_TEST2    | 10000 |   244K|     1   (0)| 00:00:01 |
|   4 |    INLIST ITERATOR          |             |       |       |            |          |
|*  5 |     INDEX UNIQUE SCAN       | XT_TEST2_PK |     2 |    32 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."A"=TO_NUMBER(:A))
   5 - access("X"."ID"=:B1 OR "X"."ID"=:B2)

As you can see it’s very good plan, but only for cases when you have one row only for any set of (t1.a, t2.b).
Look what will be if we replace “=” to “in”:
(strictly speaking we have to use “in (select distinct rowid“, but it’s not so important now)

SQL> explain plan for
  2  select *
  3  from xt_test1 t1
  4      ,xt_test2 t2
  5  where
  6       t1.a = :a
  7   and t2.rowid in (select x.rowid rid from xt_test2 x where x.id in (t1.a,t1.b))
  8  /

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    74 |    22   (0)| 00:00:01 |
|   1 |  CONCATENATION                |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     1 |    37 |    11   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |             |     1 |    37 |    11   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     1   (0)| 00:00:01 |
|   7 |   NESTED LOOPS                |             |     1 |    37 |    11   (0)| 00:00:01 |
|   8 |    NESTED LOOPS               |             |     1 |    37 |    11   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     0   (0)| 00:00:01 |
|  11 |    TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$62C663B8
   4 - SEL$62C663B8_1 / T1@SEL$1
   5 - SEL$62C663B8_1 / X@SEL$2
   6 - SEL$62C663B8_1 / X@SEL$2
   9 - SEL$62C663B8_2 / T1@SEL$62C663B8_2
  10 - SEL$62C663B8_2 / X@SEL$62C663B8_2
  11 - SEL$62C663B8_2 / X@SEL$62C663B8_2

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T1"."A"=TO_NUMBER(:A))
   5 - access("X"."ID"="T1"."B")
   9 - filter("T1"."A"=TO_NUMBER(:A))
  10 - access("X"."ID"="T1"."A")
       filter(LNNVL("X"."ID"="T1"."B"))

Notice that join with “t2″ was eliminated(also we can get join elimination of X).
So let’s try with outlines from plan with “=”

SQL> explain plan for
  2  select
  3    /*+
  4        BEGIN_OUTLINE_DATA
  5        INDEX(@"SEL$2" "X"@"SEL$2" ("XT_TEST2"."ID"))
  6        PUSH_SUBQ(@"SEL$2")
  7        USE_NL(@"SEL$1" "T2"@"SEL$1")
  8        LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
  9        ROWID(@"SEL$1" "T2"@"SEL$1")
 10        FULL(@"SEL$1" "T1"@"SEL$1")
 11        OUTLINE_LEAF(@"SEL$1")
 12        OUTLINE_LEAF(@"SEL$2")
 13        ALL_ROWS
 14        DB_VERSION('11.2.0.4')
 15        OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
 16        IGNORE_OPTIM_EMBEDDED_HINTS
 17        END_OUTLINE_DATA
 18    */
 19     *
 20  from xt_test1 t1
 21      ,xt_test2 t2
 22  where
 23       t1.a = :a
 24   and t2.rowid in (select x.rowid rid from xt_test2 x where x.id in (t1.a,t1.b))
 25  /

Explained.

SQL> @xplan +alias

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3405631984

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   500 | 18500 |    27   (4)| 00:00:01 |
|   1 |  NESTED LOOPS        |             |   500 | 18500 |    24   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL  | XT_TEST2    |   500 | 12500 |    14   (0)| 00:00:01 |
|   4 |    INLIST ITERATOR   |             |       |       |            |          |
|*  5 |     INDEX UNIQUE SCAN| XT_TEST2_PK |     1 |    16 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
   3 - SEL$1 / T2@SEL$1
   4 - SEL$2
   5 - SEL$2 / X@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."A"=TO_NUMBER(:A))
   3 - filter( EXISTS (SELECT /*+ PUSH_SUBQ INDEX ("X" "XT_TEST2_PK") */ 0
              FROM "XT_TEST2" "X" WHERE ("X"."ID"=:B1 OR "X"."ID"=:B2) AND "X".ROWID=:B3))
   5 - access("X"."ID"=:B1 OR "X"."ID"=:B2)
       filter("X".ROWID=:B1)

Unfortunately “TABLE ACCESS BY USER ROWID” was replaced by “TABLE ACCESS FULL”.
I will try to register SR later about this strange behavior, but I want to show now a couple workarounds for that.
First of all we can use collection iterators:

SQL> explain plan for
  2  select/*+ use_nl(t2) index(t2 (id)) cardinality(x 2)*/ *
  3  from xt_test1 t1
  4      ,table(sys.odcinumberlist(a,b)) x
  5      ,xt_test2 t2
  6  where
  7       t1.a = :a
  8   and t2.id=x.column_value
  9  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 651710375

---------------------------------------------------------------------------------------------
| Id  | Operation                                | Name        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |             |     2 |    78 |    41   (0)|
|   1 |  NESTED LOOPS                            |             |     2 |    78 |    41   (0)|
|   2 |   NESTED LOOPS                           |             |     2 |    78 |    41   (0)|
|   3 |    NESTED LOOPS                          |             |     2 |    28 |    39   (0)|
|   4 |     TABLE ACCESS FULL                    | XT_TEST1    |     1 |    12 |    10   (0)|
|   5 |     COLLECTION ITERATOR CONSTRUCTOR FETCH|             |     2 |     4 |    29   (0)|
|   6 |    INDEX UNIQUE SCAN                     | XT_TEST2_PK |     1 |       |     0   (0)|
|   7 |   TABLE ACCESS BY INDEX ROWID            | XT_TEST2    |     1 |    25 |     1   (0)|
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T1"."A"=TO_NUMBER(:A))
   6 - access("T2"."ID"=VALUE(KOKBF$))

It’s quite good solution, but there is a big overhead because of many objects creations.
So let’s try another options:
Spoiler:: With generated tables
SelectShow

SQL> explain plan for
  2  with gen as (select level rn from dual connect by level<=2)
  3  select/*+ leading(t1 gen t2) use_nl(gen) */ *
  4  from xt_test1 t1
  5      ,gen
  6      ,xt_test2 t2
  7  where
  8       t1.a = :a
  9   and t2.id=decode(gen.rn,1,t1.a,t1.b)
 10  /

Explained.

-------------------------------------------------------------------------------------
Plan hash value: 2353516195

-------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |     1 |    50 |    13   (0)|
|   1 |  NESTED LOOPS                    |             |     1 |    50 |    13   (0)|
|   2 |   NESTED LOOPS                   |             |     1 |    50 |    13   (0)|
|   3 |    NESTED LOOPS                  |             |     1 |    25 |    12   (0)|
|*  4 |     TABLE ACCESS FULL            | XT_TEST1    |     1 |    12 |    10   (0)|
|   5 |     VIEW                         |             |     1 |    13 |     2   (0)|
|*  6 |      CONNECT BY WITHOUT FILTERING|             |       |       |            |
|   7 |       FAST DUAL                  |             |     1 |       |     2   (0)|
|*  8 |    INDEX UNIQUE SCAN             | XT_TEST2_PK |     1 |       |     0   (0)|
|   9 |   TABLE ACCESS BY INDEX ROWID    | XT_TEST2    |     1 |    25 |     1   (0)|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T1"."A"=TO_NUMBER(:A))
   6 - filter(LEVEL<=2)
   8 - access("T2"."ID"=DECODE("GEN"."RN",1,"T1"."A","T1"."B"))

Note that you have to avoid duplicates if t1.a = t2.b.
Also it’s quite good to get plans with concatenation only on second table, without rereading others tables:
Spoiler:: 12c Lateral with concatenation
SelectShow

SQL> explain plan for
  2  select/*+ leading(t1 t2) use_nl(t2)  */ *
  3  from xt_test1 t1
  4      ,lateral(
  5           select/*+ no_merge */ *
  6           from xt_test2
  7           where xt_test2.id in (t1.a,t1.b)
  8      ) t2
  9  where
 10       t1.a = :a
 11  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 53936152

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     2 |   134 |    14   (0)|
|   1 |  NESTED LOOPS                  |                 |     2 |   134 |    14   (0)|
|*  2 |   TABLE ACCESS FULL            | XT_TEST1        |     1 |    12 |    10   (0)|
|   3 |   VIEW                         | VW_LAT_A18161FF |     2 |   110 |     4   (0)|
|   4 |    CONCATENATION               |                 |       |       |            |
|   5 |     TABLE ACCESS BY INDEX ROWID| XT_TEST2        |     1 |    25 |     2   (0)|
|*  6 |      INDEX UNIQUE SCAN         | XT_TEST2_PK     |     1 |       |     1   (0)|
|   7 |     TABLE ACCESS BY INDEX ROWID| XT_TEST2        |     1 |    25 |     2   (0)|
|*  8 |      INDEX UNIQUE SCAN         | XT_TEST2_PK     |     1 |       |     1   (0)|
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."A"=TO_NUMBER(:A))
   6 - access("XT_TEST2"."ID"="T1"."B")
   8 - access("XT_TEST2"."ID"="T1"."A")
       filter(LNNVL("XT_TEST2"."ID"="T1"."B"))


But it’s really funny, that do the same with simple ANSI LEFT OUTER join!
Spoiler:: Ansi left join with concatenation
SelectShow

SQL> explain plan for
  2  select/*+ leading(t1 t2) use_nl(t2) */ *
  3  from xt_test1 t1
  4       left join xt_test2 t2
  5            on t2.id in (t1.a,t1.b)
  6  where t1.a = :a
  7  and t2.id*0 is not null
  8  /
-----------------------------------------------------------------------------------------------
Plan hash value: 4273891864

-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     2 |   134 |    14   (0)| 00:00:01 |
|*  1 |  FILTER                         |             |       |       |            |          |
|   2 |   NESTED LOOPS OUTER            |             |     2 |   134 |    14   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL            | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|   4 |    VIEW                         |             |     2 |   110 |     4   (0)| 00:00:01 |
|   5 |     CONCATENATION               |             |       |       |            |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     2   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     1   (0)| 00:00:01 |
|   8 |      TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     2   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T2"."ID"*0 IS NOT NULL)
   3 - filter("T1"."A"=TO_NUMBER(:A))
   7 - access("T2"."ID"="T1"."B")
   9 - access("T2"."ID"="T1"."A")
       filter(LNNVL("T2"."ID"="T1"."B"))

Oracle 12c: scalar subqueries

Mon, 2014-02-10 15:10

We already know that the CBO transformation engine in 12c can unnest scalar subqueries from select-list.
So it’s not very surprising, that CBO is now able to add scalar subqueries costs to total query cost (even if “_optimizer_unnest_scalar_sq” = false):
Spoiler:: Before 12.1 SelectShow

SQL> explain plan for
  2  select
  3  (select count(*) from XT_TEST) cnt
  4  from dual;

Explained.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2843533371

---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IX_TEST_A | 90792 |    50   (0)| 00:00:01 |
|   3 |  FAST DUAL            |           |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

10 rows selected.


Spoiler:: 12.1
SelectShow

SQL> alter session set "_optimizer_unnest_scalar_sq"=false;

Session altered.

SQL> explain plan for
  2  select
  3  (select count(*) from XT_TEST) cnt
  4  from dual;

Explained.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2843533371

---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    52   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IX_TEST_A | 90792 |    50   (0)| 00:00:01 |
|   3 |  FAST DUAL            |           |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

10 rows selected.


But it’s interesting that correlated subquery can reference now to a column from parent tables more
than one level above:
Spoiler:: Before 12.1
SelectShow

SQL> with t1 as (select/*+ materialize */ 1 a from dual)
  2      ,t2 as (select/*+ materialize */ 2 b from dual)
  3      ,t3 as (select/*+ materialize */ 3 c from dual)
  4  select
  5    (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
  6  from t1;
  (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
                                                       *
ERROR at line 5:
ORA-00904: "T1"."A": invalid identifier


Spoiler:: 12.1
SelectShow

SQL> with t1 as (select/*+ materialize */ 1 a from dual)
  2      ,t2 as (select/*+ materialize */ 2 b from dual)
  3      ,t3 as (select/*+ materialize */ 3 c from dual)
  4  select
  5    (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
  6  from t1;

         S
----------
         6

SYS_OP_MAP_NONNULL is in the documentation now

Mon, 2014-02-10 14:24

Interesting, that SYS_OP_MAP_NONNULL appeared in the Oracle 12c documentation: Choosing Indexes for Materialized Views

Lazy tip: By the way, with length limitations, we can also use documented dump function:

SQL> with
  2    t(a,b) as (
  3               select *
  4               from table(ku$_vcnt(null,'FF','A'))
  5                   ,table(ku$_vcnt(null,'FF','B'))
  6              )
  7  select
  8      a,b
  9     ,case when sys_op_map_nonnull(a) = sys_op_map_nonnull(b) then '=' else '!=' end comp1
 10     ,case when dump(a,1017)          = dump(b,1017)          then '=' else '!=' end comp2
 11     ,sys_op_map_nonnull(a) s_o_m_n_a
 12     ,sys_op_map_nonnull(b) s_o_m_n_b
 13     ,dump(a,  17) dump_a
 14     ,dump(b,  17) dump_b -- it is preferably sometimes to use 1017 - for charset showing
 15  from t;

A     B     COMP1 COMP2 S_O_M_N_A  S_O_M_N_B  DUMP_A                DUMP_B
----- ----- ----- ----- ---------- ---------- --------------------- ---------------------
            =     =     FF         FF         NULL                  NULL
      FF    !=    !=    FF         464600     NULL                  Typ=1 Len=2: F,F
      B     !=    !=    FF         4200       NULL                  Typ=1 Len=1: B
FF          !=    !=    464600     FF         Typ=1 Len=2: F,F      NULL
FF    FF    =     =     464600     464600     Typ=1 Len=2: F,F      Typ=1 Len=2: F,F
FF    B     !=    !=    464600     4200       Typ=1 Len=2: F,F      Typ=1 Len=1: B
A           !=    !=    4100       FF         Typ=1 Len=1: A        NULL
A     FF    !=    !=    4100       464600     Typ=1 Len=1: A        Typ=1 Len=2: F,F
A     B     !=    !=    4100       4200       Typ=1 Len=1: A        Typ=1 Len=1: B

9 rows selected.
Categories: Development

A little trick with redefining any object in a view from another schema

Mon, 2014-01-13 15:49

A couple months ago i used this trick with one of the dba_ views in own query for convenience.
Simple example:

SQL> conn scott/zzzxxx@orasql
Connected.
SQL> select count(*) from all_catalog;

  COUNT(*)
----------
     30088

SQL> with v$enabledprivs as( select -level as priv_number from dual connect by level<=1000)
  2  select count(*) from all_catalog;

  COUNT(*)
----------
     36453

As you see, i just redefined v$enabledprivs with subquery factoring clause, and look at the code of “all_catalog” view now:

CREATE OR REPLACE VIEW SYS.ALL_TABLES AS
select u.name, o.name,
       decode(bitand(t.property,2151678048), 0, ts.name,
              decode(t.ts#, 0, null, ts.name)),
       ...<skipped>...
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
     sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi,
     sys.deferred_stg$ ds
where ...
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
  and t.dataobj# = cx.obj# (+)
  and cx.owner# = cu.user# (+)
  and ksppi.indx = ksppcv.indx
  and ksppi.ksppinm = '_dml_monitoring_enabled'
;
Categories: Development