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:
ddl
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; /
[collapse]
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:
With generated tables
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"))
[collapse]
Also it’s quite good to get plans with concatenation only on second table, without rereading others tables:
12c Lateral with concatenation
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"))
[collapse]
But it’s really funny, that do the same with simple ANSI LEFT OUTER join!
Ansi left join with concatenation
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"))
[collapse]