Thanks for the question.

Asked: January 20, 2025 - 12:30 am UTC

Last updated: January 22, 2025 - 4:51 pm UTC

Version: 19c EE

Viewed 100+ times

You Asked

The following is a simplified version that exhibits the behavior in question.
There are two tables T and F.
1% of the rows of table T are indexed by T_IX.
Though F has the same number of rows as T_IX, F has 1% of rows "in common" with T_IX.
The cardinality are as follows.

T    ~  1M
T_IX ~ 10K
F    ~ 10K

The query is a semi-join that selects the rows from T that are both indexed in T_IX and "in common" with F.

select id
  from "T"
 where exists ( select null
                  from "F"
                 where T.num = F.num
              )
/

This query correctly returns 100 (0.01%) of the 1M rows in T.

The optimizer's plan is either
  i) Nested Loops join with F and T_IX, then perform the Table Access on T.
     PRO: Table Access on T is after the join.
     CON: Nested Loops starts.
 ii) Full Scanning T_IX then performing the Table Access on T.  This is then hash joined with F.
     PRO: Full Scan of T_IX and the Table Access on T is batched.
     CON: Accessed 9,900 more rows from T then we needed (99% inefficient).

How can I encourage the optimizer to get the best of both worlds?
That is, how can I encourage the optimizer to perform a hash index join between F and the index T_IX first, and then, after many (99%) of T's rowids have been eliminated via that join, perform a Table Access (preferably batched) on T?
Does such an access path even exist?

Note: The example below adds the hints in order to reliably generate the execution plans for the purpose of this example and is slightly modified to fit the format of this forum (mainly columns of the explain plans are removed).

This example shows the two explain plans and the difference in Table Access on T. The Nested Loops join does perform the Table Access of T after the join and only has to get 100 rows, but the Hash join does not. The Hash join performs the Table Access on T before the join and has to get 10,000 rows.

SQL>create table T ( id  number
  2                 , num number
  3                 , pad char(2e3) default 'x'
  4                 )
  5  /

Table created.

SQL>insert into T ( id, num )
  2              with "D" as
  3                   ( select level id
  4                       from dual connect by level <= 1e3
  5                   )
  6                   select rownum
  7                        , decode( mod( rownum, 1e2 ), 0, rownum )
  8                     from "D", "D"
  9  /

1000000 rows created.

SQL>create index T_IX on T ( num )
  2  /

Index created.

SQL>create table F ( num number
  2                 )
  3  /

Table created.

SQL>insert into F ( num )
  2              with "D" as
  3                   ( select level id
  4                       from dual connect by level <= 1e2
  5                   )
  6                   select rownum
  7                     from "D", "D"
  8  /

10000 rows created.

SQL>exec dbms_stats.gather_table_stats( OwnName => user, TabName => 'T' )

PL/SQL procedure successfully completed.

SQL>exec dbms_stats.gather_table_stats( OwnName => user, TabName => 'F' )

PL/SQL procedure successfully completed.

SQL>column name format A10
SQL>          select 'table' type, table_name name, num_rows, sample_size, blocks      from user_tables  where table_name in( 'T', 'F' )
  2  union all select 'index' type, index_name name, num_rows, sample_size, leaf_blocks from user_indexes where table_name in( 'T' )
  3   order by 1, 2
  4  /
TYPE  NAME          NUM_ROWS SAMPLE_SIZE      BLOCKS
----- ---------- ----------- ----------- -----------
index T_IX             10000       10000          21
table F                10000       10000          20
table T              1000000     1000000      338234

3 rows selected.

SQL>set linesize  237
SQL>set feedback  only      sql_id
SQL>set autotrace traceonly statistics
SQL>set timing    on
SQL>select /*+ gather_plan_statistics use_nl_with_index ( T T_IX ) leading ( F ) */
  2         id
  3    from "T"
  4   where exists ( select /*+  */
  5                         null
  6                    from "F"
  7                   where T.num = F.num
  8                )
  9  /

100 rows selected.

SQL_ID: 0y2ja7qn098m2
Elapsed: 00:00:00.55

Statistics
----------------------------------------------------------
         11  recursive calls
         12  db block gets
        136  consistent gets
        105  physical reads
       2024  redo size
       1179  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL>set timing    off
SQL>define my_sql_id = &_sql_id
SQL>set autotrace off
SQL>set feedback  on
SQL>select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id', cursor_child_no => null, format => 'ALL ALLSTATS LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0y2ja7qn098m2, child number 0
-------------------------------------
Plan hash value: 18874137

-------------------------------------------------------------------------
|Id | Operation                    |Name|Starts|A-Rows|Buffers|Used-Mem |
-------------------------------------------------------------------------
|  0| SELECT STATEMENT             |    |     1|   100|    132|         |
|  1|  NESTED LOOPS                |    |     1|   100|    132|         |
|  2|   NESTED LOOPS               |    |     1|   100|     32|         |
|  3|    SORT UNIQUE               |    |     1| 10000|     22| 487K (0)|
|  4|     TABLE ACCESS FULL        |F   |     1| 10000|     22|         |
|* 5|    INDEX RANGE SCAN          |T_IX| 10000|   100|     10|         |
|  6|   TABLE ACCESS BY INDEX ROWID|T   |   100|   100|    100|         |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T"."NUM"="F"."NUM")
       filter("T"."NUM" IS NOT NULL)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   1 -  SEL$5DA710D3
           -  leading ( F )

   5 -  SEL$5DA710D3 / T@SEL$1
           -  use_nl_with_index ( T T_IX )


37 rows selected.

SQL>undefine my_sql_id
SQL>set linesize  237
SQL>set feedback  only      sql_id
SQL>set autotrace traceonly statistics
SQL>set timing    on
SQL>select /*+ gather_plan_statistics use_hash ( T ) */
  2         id
  3    from "T"
  4   where exists ( select /*+  */
  5                         null
  6                    from "F"
  7                   where T.num = F.num
  8                )
  9  /

100 rows selected.

SQL_ID: 6u5xg2f1ra0a4
Elapsed: 00:00:00.78

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10045  consistent gets
       6316  physical reads
          0  redo size
       1179  bytes sent via SQL*Net to client
        537  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL>set timing    off
SQL>define my_sql_id = &_sql_id
SQL>set autotrace off
SQL>set feedback  on
SQL>select * from table(dbms_xplan.display_cursor(sql_id => '&my_sql_id', cursor_child_no => null, format => 'ALL ALLSTATS LAST +PEEKED_BINDS -PROJECTION -ALIAS'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6u5xg2f1ra0a4, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_hash ( T ) */        id   from
"T"  where exists ( select /*+  */                        null
         from "F"                  where T.num = F.num               )

Plan hash value: 2630336776

-------------------------------------------------------------------------------
|Id|Operation                            |Name|Starts|A-Rows|Buffers|Used-Mem |
-------------------------------------------------------------------------------
| 0|SELECT STATEMENT                     |    |     1|   100|  10045|         |
|*1| HASH JOIN RIGHT SEMI                |    |     1|   100|  10045|1942K (0)|
| 2|  TABLE ACCESS FULL                  |F   |     1| 10000|     22|         |
| 3|  TABLE ACCESS BY INDEX ROWID BATCHED|T   |     1| 10000|  10023|         |
|*4|   INDEX FULL SCAN                   |T_IX|     1| 10000|     23|         |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."NUM"="F"."NUM")
   4 - filter("T"."NUM" IS NOT NULL)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   3 -  SEL$5DA710D3 / T@SEL$1
         U -  use_hash ( T )


31 rows selected.

SQL>undefine my_sql_id
SQL>drop table F
  2  /

Table dropped.

SQL>drop table T
  2  /

Table dropped.

SQL>spool off

and Chris said...

I don't think the access path you want is possible - at least not out of the box.

You can get close to it by:

- Joining T to F and returning the T rowids
- Finding the T rows with rowids IN the result of the previous query

e.g.

select 
        id
   from "T"
  where rowid in ( select 
                        t2.rowid
                   from "F", t t2
                  where t2.num = F.num
               );

select * from dbms_xplan.display_cursor ( format => 'IOSTATS LAST');

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |    100 |00:00:00.02 |     148 |
|   1 |  NESTED LOOPS               |          |      1 |      1 |    100 |00:00:00.02 |     148 |
|   2 |   VIEW                      | VW_NSO_1 |      1 |  10000 |    100 |00:00:00.02 |      48 |
|   3 |    HASH UNIQUE              |          |      1 |      1 |    100 |00:00:00.02 |      48 |
|*  4 |     HASH JOIN               |          |      1 |  10000 |    100 |00:00:00.02 |      48 |
|   5 |      TABLE ACCESS FULL      | F        |      1 |  10000 |  10000 |00:00:00.01 |      22 |
|*  6 |      INDEX FAST FULL SCAN   | T_IX     |      1 |  10000 |  10000 |00:00:00.01 |      26 |
|   7 |   TABLE ACCESS BY USER ROWID| T        |    100 |      1 |    100 |00:00:00.01 |     100 |
--------------------------------------------------------------------------------------------------

In this example this does more work than the nested loops plan you get with a regular exists though - 148 buffers vs 131 buffers:

select 
        id
   from "T"
  where exists ( select 
                        null
                   from "F"
                  where T.num = F.num
                );

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |    100 |00:00:00.01 |     131 |
|   1 |  NESTED LOOPS                |      |      1 |  10000 |    100 |00:00:00.01 |     131 |
|   2 |   NESTED LOOPS               |      |      1 |  10000 |    100 |00:00:00.01 |      31 |
|   3 |    SORT UNIQUE               |      |      1 |  10000 |  10000 |00:00:00.01 |      22 |
|   4 |     TABLE ACCESS FULL        | F    |      1 |  10000 |  10000 |00:00:00.01 |      22 |
|*  5 |    INDEX RANGE SCAN          | T_IX |  10000 |      1 |    100 |00:00:00.01 |       9 |
|   6 |   TABLE ACCESS BY INDEX ROWID| T    |    100 |      1 |    100 |00:00:00.01 |     100 |
-----------------------------------------------------------------------------------------------

(results from 23.5).

So I'm unsure why you want this plan. What exactly is the problem the current plans give?

Rating

(1 rating)

Comments

A reader, January 22, 2025 - 2:22 am UTC

Thank you for taking the time to review this question and provide your insight and technique using the rowid.

The three plans in question are as follows.

i) Nested Loops Join & Nested Loops Table Access

-----------------------------------------
|Id | Operation                    |Name|
-----------------------------------------
|  0| SELECT STATEMENT             |    |
|  1|  NESTED LOOPS                |    |
|  2|   NESTED LOOPS               |    |
|  3|    SORT UNIQUE               |    |
|  4|     TABLE ACCESS FULL        |F   |
|* 5|    INDEX RANGE SCAN          |T_IX|
|  6|   TABLE ACCESS BY INDEX ROWID|T   |
-----------------------------------------

ii) Hash Join Right Semi

-----------------------------------------------
|Id|Operation                            |Name|
-----------------------------------------------
| 0|SELECT STATEMENT                     |    |
|*1| HASH JOIN RIGHT SEMI                |    |
| 2|  TABLE ACCESS FULL                  |F   |
| 3|  TABLE ACCESS BY INDEX ROWID BATCHED|T   |
|*4|   INDEX FULL SCAN                   |T_IX|
-----------------------------------------------

iii) Hash "Index" Join & Nested Loops Table Access

------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  NESTED LOOPS               |          |
|   2 |   VIEW                      | VW_NSO_1 |
|   3 |    HASH UNIQUE              |          |
|*  4 |     HASH JOIN               |          |
|   5 |      TABLE ACCESS FULL      | F        |
|*  6 |      INDEX FAST FULL SCAN   | T_IX     |
|   7 |   TABLE ACCESS BY USER ROWID| T        |
------------------------------------------------

To spare us from working with a 1B row example table, I was trying to scale the problem down while keep the relative proportion.
The original example provided is a 1/1000th scale, so the cardinality is actually

T    ~ 1B 
T_IX ~ 10M 
F    ~ 10M

In so doing, I ended up inadvertently making T_IX and F, small enough that the optimizer prefers i) (Nested Loops) over ii) (Hash). In the non-scaled down version, the optimizer prefers ii) (Hash) over i) (Nested Loops).
I am trying to highlight and avoid the 99% inefficiency of plan ii) as the the problem with the current plan - performing a TABLE ACCESS BY INDEX ROWID BATCHED for 10,000 rows to only keep 100.

If we increase the cardinality of T_IX and F by a factor of 10 to 100K (instead of 10K) and, to keep T at 1M instead of 1B, we change the proportion to 10% instead of 1%, then we see that the optimizer chooses ii) over i), and iii) has the fewest buffers of them all. Though i) is a close second, and the total memory used of iii) is more than i).

Buffer Summary per Plan with Modified Cardinality and Proportion:

  i)  10,798 buffers
 ii) 100,000 buffers
iii)  10,420 buffers

If all of the blocks are in memory (no physical reads), then the timing of all plans is less than a second. However, that's not an assumption I tend to make, and my inclination is to assume (the worst case) that all of the blocks are not in memory.

To avoid the excess block gets on T of plan ii), it seems I either ought to use your rowid technique or use an NL hint. I generally try to avoid using hints (using an NL hint on a billion row table does seem a bit odd), so your rowid technique generating plan iii) seems an attractive solution.

I appreciate and welcome any thoughts or critique you may have to this line of thinking.

Modified setup:

create table T ( id  number
               , num number
               , pad char(2e3) default 'x'
               )
/

insert into T ( id, num ) 
            with "D" as 
                 ( select level id 
                     from dual connect by level <= 1e3 
                 )
                 select rownum
                      , decode( mod( rownum, 1e1 ), 0, rownum )
                   from "D", "D"
/

create index T_IX on T ( num )
/

create table F ( num number
               )
/

insert into F ( num ) 
            with "D" as 
                 ( select level id 
                     from dual connect by level <= 1e2 
                 )
                 select rownum
                   from "D", "D"
/

insert into F ( num ) 
            with "D" as 
                 ( select level id 
                     from dual connect by level <= 3e2 
                 )
                 select rownum + 1e4
                   from "D", "D"
/

exec dbms_stats.gather_table_stats( OwnName => user, TabName => 'T' )
exec dbms_stats.gather_table_stats( OwnName => user, TabName => 'F' )

column name format A10
          select 'table' type, table_name name, num_rows, sample_size, blocks      from user_tables  where table_name in( 'T', 'F' )
union all select 'index' type, index_name name, num_rows, sample_size, leaf_blocks from user_indexes where table_name in( 'T' ) 
 order by 1, 2
/

Results for each of the three plans:

Plan i) Nested Loops Join & Nested Loops Table Access - 10,798 buffers.

select /*+ gather_plan_statistics use_nl_with_index ( T T_IX ) leading ( F ) */
       id
  from "T"
 where exists ( select /*+  */
                       null
                  from "F"
                 where T.num = F.num
              )
/
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |       |   100K(100)|          |  10000 |00:00:00.12 |   10798 |       |       |          |
|   1 |  NESTED LOOPS                |      |      1 |    100K|  1171K|   100K  (1)| 00:00:04 |  10000 |00:00:00.12 |   10798 |       |       |          |
|   2 |   NESTED LOOPS               |      |      1 |    100K|  1171K|   100K  (1)| 00:00:04 |  10000 |00:00:00.09 |     798 |       |       |          |
|   3 |    SORT UNIQUE               |      |      1 |    100K|   488K|   178   (2)| 00:00:01 |    100K|00:00:00.04 |     190 |  5439K|   956K| 4834K (0)|
|   4 |     TABLE ACCESS FULL        | F    |      1 |    100K|   488K|   178   (2)| 00:00:01 |    100K|00:00:00.01 |     190 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | T_IX |    100K|      1 |       |     1   (0)| 00:00:01 |  10000 |00:00:00.04 |     608 |       |       |          |
|   6 |   TABLE ACCESS BY INDEX ROWID| T    |  10000 |      1 |     7 |     2   (0)| 00:00:01 |  10000 |00:00:00.02 |   10000 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Plan ii) Hash Join Right Semi - 100K buffers.

select /*+ gather_plan_statistics */
       id
  from "T"
 where exists ( select /*+  */
                       null
                  from "F"
                 where T.num = F.num
              )
/
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |       |   100K(100)|          |  10000 |00:00:00.70 |     100K|  76520 |       |       |          |
|*  1 |  HASH JOIN RIGHT SEMI                |      |      1 |    100K|  1171K|   100K  (1)| 00:00:04 |  10000 |00:00:00.70 |     100K|  76520 |  6597K|  3201K| 6933K (0)|
|   2 |   TABLE ACCESS FULL                  | F    |      1 |    100K|   488K|   178   (2)| 00:00:01 |    100K|00:00:00.01 |     190 |      0 |       |       |          |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |    100K|   683K|   100K  (1)| 00:00:04 |    100K|00:00:16.00 |     100K|  76520 |       |       |          |
|*  4 |    INDEX FULL SCAN                   | T_IX |      1 |    100K|       |   227   (2)| 00:00:01 |    100K|00:00:00.05 |     225 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

iii) Hash "Index" Join & Nested Loops Table Access - 10,420 buffers

select /*+ gather_plan_statistics */
       id
  from "T"
 where rowid in ( select T2.rowid
                    from "T" "T2"
                         join "F"
                           on T2.num = F.num
                )
/
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |       |  1074 (100)|          |  10000 |00:00:00.06 |   10420 |       |       |          |
|   1 |  NESTED LOOPS               |          |      1 |      1 |    29 |  1074   (3)| 00:00:01 |  10000 |00:00:00.06 |   10420 |       |       |          |
|   2 |   VIEW                      | VW_NSO_1 |      1 |    100K|  1171K|   345   (4)| 00:00:01 |  10000 |00:00:00.04 |     420 |       |       |          |
|   3 |    HASH UNIQUE              |          |      1 |      1 |  1855K|            |          |  10000 |00:00:00.04 |     420 |  1783K|  1783K| 3064K (0)|
|*  4 |     HASH JOIN RIGHT SEMI    |          |      1 |    100K|  1855K|   345   (4)| 00:00:01 |  10000 |00:00:00.02 |     420 |  6597K|  3201K| 6877K (0)|
|   5 |      TABLE ACCESS FULL      | F        |      1 |    100K|   488K|   178   (2)| 00:00:01 |    100K|00:00:00.01 |     190 |       |       |          |
|*  6 |      INDEX FAST FULL SCAN   | T_IX     |      1 |    100K|  1367K|   162   (2)| 00:00:01 |    100K|00:00:00.01 |     230 |       |       |          |
|   7 |   TABLE ACCESS BY USER ROWID| T        |  10000 |      1 |    17 |     1   (0)| 00:00:01 |  10000 |00:00:00.02 |   10000 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

January 22, 2025 - 4:51 pm UTC

To avoid the excess block gets on T of plan ii), it seems I either ought to use your rowid technique or use an NL hint

There are at least two other options:

- Use SQL Plan Baselines. These enable you to lock in the plan you want without hints. They are available in all editions at no additional cost (though there are several limitations in SE)

- Create an index over ( num, id )

This gives a better plan than all the other options - at least for this example:

create index i_num_id_i on t ( num, id );
set feed only
select 
       id
  from "T"
 where exists ( select /*+  */
                       null
                  from "F"
                 where T.num = F.num
              );
set feed on
select * from dbms_xplan.display_cursor ( format => 'ALLSTATS LAST' );

----------------------------------------------------------------------------------------------------------------------------------| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |            |      1 |        |  10000 |00:00:00.19 |    2885 |   2585 |       |       |          ||*  1 |  HASH JOIN RIGHT SEMI |            |      1 |    100K|  10000 |00:00:00.19 |    2885 |   2585 |  6597K|  3201K| 6936K (0)||   2 |   TABLE ACCESS FULL   | F          |      1 |    100K|    100K|00:00:00.01 |     190 |      0 |       |       |          ||*  3 |   INDEX FAST FULL SCAN| I_NUM_ID_I |      1 |    100K|    100K|00:00:00.15 |    2695 |   2585 |       |       |          |----------------------------------------------------------------------------------------------------------------------------------

If you're happy with the nested loops plan, I'm inclined to use baselines to get this plan as a starting point. Then look into whether the covering index gives a big enough improvement to be worthwhile.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

How to get Hash Index Join Before Table Access?

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs