Oracle SQL | Author Archives

Oracle 12c: scalar subqueries

Posted on February 11, 2014 by Sayan Malakshinov Posted in 12c, CBO, oracle, undocumented Leave a comment

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):

Before 12.1

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.

[collapse]

12.1

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.

[collapse]

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

Before 12.1

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

[collapse]

12.1

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

[collapse]

SYS_OP_MAP_NONNULL is in the documentation now

Posted on February 10, 2014 by Sayan Malakshinov Posted in 12c, documentation, oracle, undocumented Leave a comment

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.

Little example of index creation on extended varchars

Posted on November 15, 2013 by Sayan Malakshinov Posted in 12c, oracle, undocumented Leave a comment

-- it's just for fun:
SQL> alter system set "_scalar_type_lob_storage_threshold"=32000;

System altered.
SQL> create table t_varchar32000(v varchar2(32000 byte));

Table created.

SQL> insert into t_varchar32000
  2  select rpad(rownum,31999) || `x' str from dual connect by level<=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace users;
create index ix_t_varchar32000 on t_varchar32000(v) tablespace users
                                  *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k;
create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k
                                  *
ERROR at line 1:
ORA-01450: maximum key length (26510) exceeded

-- tablespace for big varchars:
SQL> alter system set DB_32K_CACHE_SIZE=100M;

System altered.

SQL> CREATE TABLESPACE TS_32K DATAFILE '/u01/app/oracle/oradata/xtsql/pdb1/ts_32k_1.dbf' SIZE 150M
  2   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  3   BLOCKSIZE 32K;

Tablespace created.

SQL> create table t_varchar16000(v varchar2(16000 byte)) tablespace ts_32k;

Table created.

SQL> insert into t_varchar16000
  2  select rpad(rownum,15999,'x' ) || 'y' from dual connect by level<=1000;

1000 rows created.

SQL> create index ix_t_varchar16000 on t_varchar16000(v) tablespace ts_32k;

Index created.

Statistics

SQL> begin
  2     dbms_stats.gather_table_stats(
  3       ownname    => user
  4      ,tabname    => 'T_VARCHAR16000'
  5      ,method_opt => 'for all columns size auto'
  6      ,cascade => true
  7     );
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> @stats/tab t_varchar16000

OWNER           TABLE_NAME                     PARTITION_NAME          # ST_LOCK STALE_STA GLOBAL_ST USER_STAT   NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  AVG_SPACE LAST_ANALYZED
--------------- ------------------------------ -------------------- ---- ------- --------- --------- --------- ---------- ---------- ------------ ----------- ---------- ---------------
XTENDER         T_VARCHAR16000                                                   NO        YES       NO              1000       3016            0       16001          0 14-NOV-13


OWNER           INDEX_NAME                       NUM_ROWS DISTINCT_KEYS     BLEVEL LEAF_BLOCKS  CL_FACTOR LAST_ANALYZED   GLOBAL_ST USER_STAT
--------------- ------------------------------ ---------- ------------- ---------- ----------- ---------- --------------- --------- ---------
XTENDER         IX_T_VARCHAR16000                    1000          1000          1        1000       1000 14-NOV-13       YES       NO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| column_name                   | num_distinct| low_value         | high_value        | num_nulls | num_bucket| last_analyzed       | sample_size| globa| user_| avg_c| histogram           |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| V                             | 1000        | 1000xxxxxxxxxxxxxx| 9xxxxxxxxxxxxxxxxx| 0         | 1         | 2013-11-14 21:11    | 1000       | YES  | NO   | 16001| NONE                |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[collapse]

Just another SQL beautifier

Posted on October 30, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 6 Comments

Previously i wrote beautifier in perl, but it was not so good, so i decided to write it in java using popular BlancoSQLFormatter library.
So you can download it now: https://orasql.org/scripts/SQLBeautifier.jar
Usage:

java -jar SQLBeautifier.jar your_file.sql

echo select * from dual | java -jar SQLBeautifier.jar

You certainly can conveniently use it within sql*plus with script like that:

set timing off head off termout off
col qtext format a150
prompt ################################  Original query text:  ################################################;
#spool &_SPOOLS/to_format.sql
spool to_format.sql
select
    coalesce(
        (select sql_fulltext from v$sqlarea a where a.sql_id='&1')
    ,   (select sql_text from dba_hist_sqltext a where a.sql_id='&1' and dbid=(select dbid from v$database))
    ) qtext
from dual
;
spool off

prompt ################################  Formatted query text #################################################;
#host perl inc/sql_format_standalone.pl &_SPOOLS/to_format.sql
host java -jar SQLBeautifier.jar to_format.sql
prompt ################################  Formatted query text End #############################################;
set termout on head on

Example:

Links:

Patch for “Bug 16516751 : Suboptimal execution plan for query with join and in-list using composite index” is available now

Posted on October 7, 2013 by Sayan Malakshinov Posted in 12c, bug, CBO, oracle, query optimizing 2 Comments

Bug about which i wrote previously is fixed now in 12.2, and patch 16516751 is available now for 11.2.0.3 Solaris64.
Changes:
1. CBO can consider filters in such cases now
2. Hint NUM_INDEX_KEYS fixed and works fine

UPD: Very interesting solution by Igor Usoltsev(in russian):
Ignored hint USE_CONCAT(OR_PREDICATES(N)) allows to avoid inlist iterator.
Example:

select--+ USE_CONCAT(OR_PREDICATES(32767))
 * from xt1,xt2
where
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2)
/

Plan hash value: 2884586137          -- good plan:
 
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2))

From 10053 trace on nonpatched 11.2.0.3:

Just link to my old package for os commands execution

Posted on September 19, 2013 by Sayan Malakshinov Posted in oracle, PL/SQL Leave a comment

I just noticed that os_command.zip from old oracle white paper was lost, so i decided to post link to my old package, which is like os_command but with timeout parameter: http://github.com/xtender/xt_shell

Oracle 12c: behavior tests of the Inline functions, “Identities” and “defaults”

Posted on July 13, 2013 by Sayan Malakshinov Posted in 12c, curious, undocumented 4 Comments

I have done several minitests:
1. SQL and PL/SQL engines: which functions will be executed if there are two functions with same name as in SQL, as in PL/SQL (like “USER”, LPAD/RPAD, etc..)
– PL/SQL.

PL/SQL

SQL> @trace_on
Enter value for trace_identifier: inline
Enter value for level: 12
Tracing was enabled:

TRACEFILE_NAME
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xtsql/xtsql/trace/xtsql_ora_21599_inline.trc

SQL> with
  2  function inline_user return varchar2 is
  3  begin
  4   return user;
  5  end;
  6  select
  7    inline_user
  8  from dual
  9  /

INLINE_USER
------------------------------
XTENDER

1 row selected.

SQL> @trace_off
-- unlike SQL's "USER", PL/SQL function SYS.STANDARD.USER recursively executes "select user from sys.dual":
SQL> !grep USER /u01/app/oracle/diag/rdbms/xtsql/xtsql/trace/xtsql_ora_21599_inline.trc
SELECT USER FROM SYS.DUAL

SQL>

[collapse]

2. Will there be any context switches if we call the inline functions which contain another pl/sql functions/procedures?
– Yes

Test 1

SQL> sho parameter max_string

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
max_string_size                      string       STANDARD

SQL> @trace_pl_on

Session altered.

SQL> with
  2  function blabla(p_str varchar2) return varchar2 is
  3  begin
  4   return lpad(p_str, 5000, '*');
  5  end;
  6  select
  7    length(blabla(dummy)) lpad_plsql
  8  from dual;
  9  /
from dual
     *
ERROR at line 8:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

SQL> @trace_pl_last.sql

     RUNID  EVENT_SEQ EVENT_COMMENT                    EVENT_UNIT_OWNER   EVENT_UNIT
---------- ---------- -------------------------------- ------------------ -----------
         1          1 PL/SQL Trace Tool started
         1          2 Trace flags changed
         1          3 PL/SQL Virtual Machine started                      <anonymous>
         1          4 PL/SQL Internal Call                                <anonymous>
         1          5 PL/SQL Virtual Machine stopped

[collapse]

Test 2

SQL> @trace_pl_on

Session altered.

SQL> create or replace function f_standalone(p varchar2) return varchar2 is
  2  begin
  3    return lpad('x',3)||p;
  4  end;
  5  /

Function created.

SQL> with
  2  function blabla(p_str varchar2) return varchar2 is
  3     s varchar2(32767);
  4  begin
  5     s:=    lpad(p_str, 100, '1');
  6     s:= s||s;
  7     s:= s||lpad(p_str, 100, '3');
  8     s:= s||s;
  9     s:= s||(1+10);
 10     s:= f_standalone(s);
 11     s:= f_standalone(s);
 12     s:= f_standalone(s);
 13     return s;
 14  end;
 15  select
 16    length(blabla(dummy)) lpad_plsql
 17  from dual
 18  /

LPAD_PLSQL
----------
       611

SQL> @trace_pl_last.sql

     RUNID  EVENT_SEQ EVENT_COMMENT                    EVENT_UNIT_OWNER  EVENT_UNIT
---------- ---------- -------------------------------- ----------------- ------------
         2          1 PL/SQL Trace Tool started
         2          2 Trace flags changed
         2          3 PL/SQL Virtual Machine started                     <anonymous>
         2          4 PL/SQL Internal Call                               <anonymous>
         2          5 PL/SQL Virtual Machine stopped
         2          6 PL/SQL Virtual Machine started                     <anonymous>
         2          7 PL/SQL Virtual Machine started                     <anonymous>
         2          8 PL/SQL Internal Call                               <anonymous>
         2          9 PL/SQL Virtual Machine stopped
         2         10 PL/SQL Virtual Machine stopped
         2         11 PL/SQL Virtual Machine started                     <anonymous>
         2         12 PL/SQL Virtual Machine started                     <anonymous>
         2         13 PL/SQL Internal Call                               <anonymous>
         2         14 PL/SQL Virtual Machine stopped
         2         15 PL/SQL Virtual Machine stopped
         2         16 PL/SQL Virtual Machine started                     <anonymous>
         2         17 PL/SQL Internal Call                               <anonymous>
         2         18 PL/SQL Internal Call                               <anonymous>
         2         19 Procedure Call                                     <anonymous>
         2         20 PL/SQL Internal Call             XTENDER           F_STANDALONE
         2         21 Return from procedure call       XTENDER           F_STANDALONE
         2         22 Procedure Call                                     <anonymous>
         2         23 PL/SQL Internal Call             XTENDER           F_STANDALONE
         2         24 Return from procedure call       XTENDER           F_STANDALONE
         2         25 Procedure Call                                     <anonymous>
         2         26 PL/SQL Internal Call             XTENDER           F_STANDALONE
         2         27 Return from procedure call       XTENDER           F_STANDALONE
         2         28 PL/SQL Virtual Machine stopped

28 rows selected.

[collapse]

Test 3

SQL> @trace_pl_on

Session altered.

SQL> with
  2  function blabla(p_str varchar2) return varchar2 is
  3     s varchar2(32767);
  4  begin
  5     s:=    lpad(p_str, 100, '1');
  6     s:= s||s;
  7     s:= s||lpad(p_str, 100, '3');
  8     s:= s||s;
  9     s:= s||(1+10);
 10     return s;
 11  end;
 12  select
 13    length(blabla(dummy)) lpad_plsql
 14  from dual
 15  /

LPAD_PLSQL
----------
       602

1 row selected.

SQL> @trace_pl_last.sql

     RUNID  EVENT_SEQ EVENT_COMMENT                    EVENT_UNIT_OWNER   EVENT_UNIT
---------- ---------- -------------------------------- ------------------ ------------
         3          1 PL/SQL Trace Tool started
         3          2 Trace flags changed
         3          3 PL/SQL Virtual Machine started                      <anonymous>
         3          4 PL/SQL Internal Call                                <anonymous>
         3          5 PL/SQL Internal Call                                <anonymous>
         3          6 PL/SQL Virtual Machine stopped

6 rows selected.

[collapse]

3. How IDENTITY works?
For all identity columns Oracle creates a sequence with name like “ISEQ$$_XXX”, where “XXX” is the object_id of the table. All identities we can get through DBA_TAB_IDENTITY_COLS.
All Identity sequences:

select i.*
      ,tab.owner       tab_owner
      ,tab.object_name tab_name
      ,sq.object_name  sequence_name
from sys.idnseq$ i
    ,dba_objects tab
    ,dba_objects sq
where tab.object_id=i.obj#
  and sq.object_id = i.seqobj#

And we can see usage of this sequence in plans:

SQL_ID  fn5tjw6hu0dtn, child number 0
-------------------------------------
insert into xt_identity (description) values('1')

Plan hash value: 3838626111

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | Cost  | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |      1 |     1 |      0 |00:00:00.01 |      35 |
|   1 |  LOAD TABLE CONVENTIONAL |              |      1 |       |      0 |00:00:00.01 |      35 |
|   2 |   SEQUENCE               | ISEQ$$_91720 |      1 |       |      1 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------

[collapse]

4. When executes “default seq.nextval”?

Test

SQL> create sequence xt_sq1; SQL> create sequence xt_sq2; SQL> create table xt_default( 2 id1 int default xt_sq1.nextval 3 , pad varchar2(30) 4 , id2 int default xt_sq2.nextval 5 ); Table created. SQL> insert into xt_default(pad) values('1'); 1 row created. SQL> select xt_sq1.currval, xt_sq2.currval from dual; CURRVAL CURRVAL ---------- ---------- 1 1 SQL> insert into xt_default(pad) values(1/0); insert into xt_default(pad) values(1/0) * ERROR at line 1: ORA-01476: divisor is equal to zero SQL> select xt_sq1.currval, xt_sq2.currval from dual; CURRVAL CURRVAL ---------- ---------- 2 2

[collapse]

12c undocumented oracle

Oracle 12c: Extended varchars

Posted on July 13, 2013 by Sayan Malakshinov Posted in 12c 9 Comments

Tim Hall perfectly describes in his excellent post how new extended datatypes are stored on Oracle 12c.
I just found interesting parameter “_scalar_type_lob_storage_threshold“ – “threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB” – This parameter is the max size in bytes, at which these data types will be stored “inline” as simple datatypes, without creation of the lob segments.
See little example:

Controlling store extended varchars as lob

SQL> @param_ _scalar_type_lob_storage_threshold; NAME VALUE DEFLT TYPE DESCRIPTION ---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------ _scalar_type_lob_storage_threshold 4000 TRUE number threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB SQL> select * from user_lobs; no rows selected SQL> create table T_4000( 2 i int generated always as identity 3 ,v1000 varchar2(1000) 4 ,v4000 varchar2(4000) 5 ,v4500 varchar2(4500) 6 ); Table created. SQL> alter system set "_scalar_type_lob_storage_threshold"=5000; System altered. SQL> create table T_5000( 2 i int generated always as identity 3 ,v1000 varchar2(1000) 4 ,v4000 varchar2(4000) 5 ,v4500 varchar2(4500) 6 ); Table created. SQL> select table_name,column_name ,data_type,data_type_mod,data_length,char_col_decl_length,char_length,char_used 2 from user_tab_columns; TABLE_NAME COLUMN_NAM DATA_TYPE DAT DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C ---------- ---------- ---------- --- ----------- -------------------- ----------- - T_4000 V4500 VARCHAR2 4500 4500 4500 B T_4000 V4000 VARCHAR2 4000 4000 4000 B T_4000 V1000 VARCHAR2 1000 1000 1000 B T_4000 I NUMBER 22 0 T_5000 V4500 VARCHAR2 4500 4500 4500 B T_5000 V4000 VARCHAR2 4000 4000 4000 B T_5000 V1000 VARCHAR2 1000 1000 1000 B T_5000 I NUMBER 22 0 8 rows selected. SQL> select table_name,column_name,chunk,retention,cache,logging,encrypt,compression,deduplication,in_row,securefile 2 from user_lobs; TABLE_NAME COLUMN_NAM CHUNK RETENTION CACHE LOGGING ENCR COMPRE DEDUPLICATION IN_ SEC ---------- ---------- ---------- ---------- ---------- ------- ---- ------ --------------- --- --- T_4000 V4500 8192 YES YES NO NO NO YES YES

[collapse]

Note that there are no lobs for table t_5000!

Oracle 12c: Lateral, row_limiting_clause

Posted on July 5, 2013 by Sayan Malakshinov Posted in 12c, CBO, query optimizing 3 Comments

Previously i showed how we can optimize getting TopN rows sorted by field “B” for each distinct value “A” with undocumented “lateral” in previous versions of Oracle RDBMS.
But now it is documented!
Very simple example:

with t as (select level a from dual connect by level&lt;=10) select * from t ,lateral( select * from dba_objects o where object_id=t.a ) ;

Moreover, we can make now this optimization more stable and simple with row_limiting_clause:

With row_limiting_clause and multiset:

with t_unique( a ) as ( select min(t1.a) from xt_test t1 union all select (select min(t1.a) from xt_test t1 where t1.a&gt;t.a) from t_unique t where a is not null ) select/*+ use_nl(rids tt) */ * from t_unique v ,table( cast( multiset( select/*+ index_desc(tt ix_xt_test_ab) */ tt.rowid rid from xt_test tt where tt.a=v.a order by tt.b desc fetch first 5 rows only ) as sys.odcivarchar2list ) ) rids ,xt_test tt where tt.rowid=rids.column_value order by tt.a,tt.b desc

[collapse]

With row_limiting_clause and lateral:

with t_unique( a ) as ( select min(t1.a) from xt_test t1 union all select next_a from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a&gt;t.a) r where t.a is not null ) select/*+ use_nl(v r t) leading(v r t) */ t.* from t_unique v ,lateral( select/*+ index_desc(tt ix_xt_test_ab) */ rowid rid from xt_test tt where tt.a=v.a order by b desc fetch first 5 rows only ) r ,xt_test t where r.rid=t.rowid

[collapse]

Unfortunately, the recursive_subquery_clause with scalar subqueries sometimes doesn’t work:

Spoiler

SQL> with t_unique( a ) as ( 2 select min(t1.a) 3 from xt_test t1 4 union all 5 select (select min(t1.a) from xt_test t1 where t1.a&gt;t.a) 6 from t_unique t 7 where a is not null 8 ) 9 select/*+ use_nl(v r) */ * 10 from t_unique v 11 ,lateral( 12 select/*+ index_desc(tt ix_xt_test_ab) */ tt.* 13 from xt_test tt 14 where tt.a=v.a 15 order by tt.a, b desc 16 fetch first 5 rows only 17 ) r 18 order by r.a,r.b desc; from xt_test t1 * ERROR at line 3: ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []

[collapse]

But I think oracle will fix it soon, because this ORA-600 can be solved easily with hint “materialize”, but it’s not so good:

Spoiler

SQL> with t_unique( a ) as ( 2 select min(t1.a) 3 from xt_test t1 4 union all 5 select (select min(t1.a) from xt_test t1 where t1.a&gt;t.a) 6 from t_unique t 7 where a is not null 8 ), v as ( 9 select--+ materialize 10 * 11 from t_unique 12 ) 13 select/*+ use_nl(v r) */ * 14 from v 15 ,lateral( 16 select/*+ index_desc(tt ix_xt_test_ab) */ tt.* 17 from xt_test tt 18 where tt.a=v.a 19 order by tt.a, b desc 20 fetch first 5 rows only 21 ) r 22 order by r.a,r.b desc; 150 rows selected. Elapsed: 00:00:01.01 Statistics ---------------------------------------------------------- 10 recursive calls 8 db block gets 11824 consistent gets 1 physical reads 624 redo size 4608 bytes sent via SQL*Net to client 462 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 64 sorts (memory) 0 sorts (disk) 150 rows processed

[collapse]

UPDATE: There is a better solution:

Spoiler

SQL> with t_unique( a ) as ( 2 select min(t1.a) 3 from xt_test t1 4 union all 5 select (select min(t1.a) from xt_test t1 where t1.a&gt;t.a) 6 from t_unique t 7 where a is not null 8 ), v as ( 9 select * from t_unique 10 union all 11 select null from dual where 1=0 -- &lt;&lt;-- workaround 12 ) 13 select/*+ use_nl(v r) */ * 14 from v 15 ,lateral( 16 select/*+ index_desc(tt ix_xt_test_ab) */ tt.* 17 from xt_test tt 18 where tt.a=v.a 19 order by tt.a, b desc 20 fetch first 5 rows only 21 ) r 22 order by r.a,r.b desc;

[collapse]

And note that we can’t use now row_limiting_clause in cursor’s:

cursor(...row_limiting_clause)

SQL> with 2 t_unique( a ) as ( 3 select min(t1.a) 4 from xt_test t1 5 union all 6 select next_a 7 from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a&gt;t.a) r 8 where t.a is not null 9 ) 10 select 11 cursor( 12 select * 13 from xt_test t 14 where t.a=v.a 15 order by a,b desc 16 fetch first 5 rows only 17 ) c 18 from t_unique v 19 ; with * ERROR at line 1: ORA-03001: unimplemented feature ORA-00600: internal error code, arguments: [kokbcvb1], [], [], [], [], [], [], [], [], [], [], []

[collapse]

And, just for fun, with inline pl/sql function(inconsistent):

SQL> with 2 function f(v_a int) 3 return sys.ku$_vcnt 4 as 5 res sys.ku$_vcnt; 6 begin 7 select tt.rowid as rid 8 bulk collect into res 9 from xt_test tt 10 where tt.a = v_a 11 order by a,b desc 12 fetch first 5 rows only; 13 return res; 14 end; 15 16 t_unique( a ) as ( 17 select min(t1.a) 18 from xt_test t1 19 union all 20 select next_a 21 from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a&gt;t.a) r 22 where t.a is not null 23 ) 24 select/*+ use_nl(v r t) leading(v r t) */ t.* 25 from t_unique v 26 ,table(f(v.a)) r 27 ,xt_test t 28 where r.column_value=t.rowid; 29 / 150 rows selected. Elapsed: 00:00:00.06 Statistics ---------------------------------------------------------- 31 recursive calls 0 db block gets 173 consistent gets 0 physical reads 0 redo size 5657 bytes sent via SQL*Net to client 642 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 32 sorts (memory) 0 sorts (disk) 150 rows processed

[collapse]

lateral oracle undocumented behaviour recursive_subquery_clause row_limiting_clause undocumented oracle

Oracle 12c: Inconsistency of Inline “with” functions

Posted on July 3, 2013 by Sayan Malakshinov Posted in 12c, oracle, PL/SQL 2 Comments

I was hoping that if inline “with” functions are in the query, so their results will be consistent with it (as operators), but unfortunately such functions returns also inconsistent results as standalone pl/sql functions.

SQL> create table t as select 1 a from dual; Table created. SQL> declare 2 j binary_integer; 3 begin 4 dbms_job.submit( j 5 ,'begin 6 for i in 1..10 loop 7 dbms_lock.sleep(1); 8 update t set a=a+1; 9 commit; 10 end loop; 11 end;' 12 ); 13 commit; 14 end; 15 / PL/SQL procedure successfully completed. SQL> with 2 function f return int is 3 res int; 4 begin 5 dbms_lock.sleep(1); 6 select a into res from t; 7 return res; 8 end; 9 select 10 f 11 from dual 12 connect by level<=10; 13 / F ---------- 1 1 1 2 3 4 5 6 7 8 10 rows selected.

Interesting: Jonathan Lewis wrote that inline “deterministic” functions doesn’t use caching mechanism as standalone deterministic functions.

12c consistency deterministic functions pl/sql functions