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.
A couple of workarounds
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):
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.
-- 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.
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: http://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:
Links:
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:
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
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.
2. Will there be any context switches if we call the inline functions which contain another pl/sql functions/procedures?
- Yes
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:
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:
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<=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:
Unfortunately, the recursive_subquery_clause with scalar subqueries sometimes doesn’t work: