Oracle SQL
  • LICENSE

Inlist iterator again

Posted on February 21, 2014 by Sayan Malakshinov Posted in curious, oracle, query optimizing

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]
Note that you have to avoid duplicates if t1.a = t2.b.
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]

inlist iterator
« Oracle 12c: scalar subqueries
Friday prank: select from join join join »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL