Oracle SQL | Author Archives

Oracle 12c: scalar subqueries

Posted on February 11, 2014 by Sayan Malakshinov Posted in 12c, CBO, oracle, undocumented 2,875 Page views 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

[sourcecode language=”sql” highlight=”15,17″]
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.
[/sourcecode]

[collapse]

12.1

[sourcecode language=”sql” highlight=”19,21″]
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.
[/sourcecode]

[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

[sourcecode language=”sql” highlight=”10,11″]
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
[/sourcecode]

[collapse]

12.1

[sourcecode language=”sql”]
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
[/sourcecode]

[collapse]

SYS_OP_MAP_NONNULL is in the documentation now

Posted on February 10, 2014 by Sayan Malakshinov Posted in 12c, documentation, oracle, undocumented 4,556 Page views 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 2,197 Page views 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

[sourcecode language=”sql”]
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 |
———————————————————————————————————————————————————————————————

[/sourcecode]

[collapse]

Just another SQL beautifier

Posted on October 30, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 3,367 Page views 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,617 Page views 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 2,154 Page views 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 2,997 Page views 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

[sourcecode language=”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>
[/sourcecode]

[collapse]

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

Test 1

[sourcecode language=”sql”]
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

[/sourcecode]

[collapse]

Test 2

[sourcecode language=”sql”]
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 ———- ———- 2 1 PL/SQL Trace 2 2 Trace flags 2 3 PL/SQL Virtual Machine started 2 4 PL/SQL Internal Call 2 5 PL/SQL Virtual 2 6 PL/SQL Virtual Machine started 2 7 PL/SQL Virtual Machine started 2 8 PL/SQL Internal Call 2 9 PL/SQL Virtual 2 10 PL/SQL Virtual 2 11 PL/SQL Virtual Machine started 2 12 PL/SQL Virtual Machine started 2 13 PL/SQL Internal Call 2 14 PL/SQL Virtual 2 15 PL/SQL Virtual 2 16 PL/SQL Virtual Machine started 2 17 PL/SQL Internal Call 2 18 PL/SQL Internal Call 2 19 Procedure Call 2 20 PL/SQL Internal Call 2 21 Return from procedure call 2 22 Procedure Call 2 23 PL/SQL Internal Call 2 24 Return from procedure call 2 25 Procedure Call 2 26 PL/SQL Internal Call 2 27 Return from procedure call 2 28 PL/SQL Virtual EVENT_UNIT_OWNER EVENT_UNIT
——————————– —————– ————
Tool started
changed
<anonymous>
<anonymous>
Machine stopped
<anonymous>
<anonymous>
<anonymous>
Machine stopped
Machine stopped
<anonymous>
<anonymous>
<anonymous>
Machine stopped
Machine stopped
<anonymous>
<anonymous>
<anonymous>
<anonymous>
XTENDER F_STANDALONE
XTENDER F_STANDALONE
<anonymous>
XTENDER F_STANDALONE
XTENDER F_STANDALONE
<anonymous>
XTENDER F_STANDALONE
XTENDER F_STANDALONE
Machine stopped

28 rows selected.
[/sourcecode]

[collapse]

Test 3

[sourcecode language=”sql”]
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.

[/sourcecode]

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

[sourcecode language=”sql”]
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 |
————————————————————————————————–
[/sourcecode]

[collapse]

4. When executes “default seq.nextval”?

Test

[sourcecode language=”sql”]
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
[/sourcecode]

[collapse]

12c undocumented oracle

Oracle 12c: Extended varchars

Posted on July 13, 2013 by Sayan Malakshinov Posted in 12c 3,263 Page views 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

[sourcecode language=”sql” highlight=”5,15,20,28,54″]
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

[/sourcecode]

[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,491 Page views 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:

[sourcecode language=”sql”]
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
[/sourcecode]

[collapse]

With row_limiting_clause and lateral:

[sourcecode language=”sql”]
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
[/sourcecode]

[collapse]

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

Spoiler

[sourcecode language=”sql”]
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], [], [], [], [], [], [], [], [], [], []
[/sourcecode]

[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

[sourcecode language=”sql”]
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
[/sourcecode]

[collapse]

UPDATE: There is a better solution:

Spoiler

[sourcecode language=”sql” highlight=”11″]
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;
[/sourcecode]

[collapse]

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

cursor(...row_limiting_clause)

[sourcecode language=”sql”]
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], [], [], [], [], [], [], [], [], [], [], []
[/sourcecode]

[collapse]

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

[sourcecode language=”sql”]
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

[/sourcecode]

[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,731 Page views 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