Category Archives: oracle

Friday prank: select from join join join

Posted on by Posted in curious, oracle 2,845 Page views 1 Comment

Valid funny queries 🙂

select the join from join join join using(the,some) 
/
select some join from left join join right using(some,the) 
/
select 1 join from join join join join join using(the) on 1=1
/
select the some from join 
where the=some( the(select some from join) 
               ,the(select the  from join)
               ) 
/

tables

[sourcecode language=”sql”]
create table join as select 1 some,1 the from dual;
create table left as select 1 some,1 the from dual;
create table right as select 1 some,1 the from dual;
[/sourcecode]

[collapse]
🙂

Little addition 🙂

update two tables set join=2;
select join from two tables;
select first, second, random from two tables join three tables on 1=1;

Another one 🙂

select first
     , case when the=some(some,some) then join end true
from two tables join three tables using(random);

….
and…

select random columns from two tables join four tables on the=some(some,some);

Oracle 12c: scalar subqueries

Posted on by Posted in 12c, CBO, oracle, undocumented 2,826 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 by Posted in 12c, documentation, oracle, undocumented 4,404 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 by Posted in 12c, oracle, undocumented 2,176 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 by Posted in oracle, SQL*Plus, SQL*PLus tips 3,301 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

or

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

Links:

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

Posted on by Posted in 12c, bug, CBO, oracle, query optimizing 2,597 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:
inlist_concat_diff_10053

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

Posted on by Posted in 12c, curious, undocumented 2,941 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 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.
[/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]

Oracle 12c: Extended varchars

Posted on by Posted in 12c 3,165 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!