Avoid full table scan with SELECT using PL/SQL table

user_17343 0
Viewed 680

Test data

CREATE TABLE parent AS ( SELECT ROWNUM AS id, 'XXX' AS dummy FROM dual CONNECT BY ROWNUM <= 1000 );
CREATE UNIQUE INDEX idx_parent ON parent(id);

CREATE TABLE child AS ( SELECT CEIL(ROWNUM/5) AS id, 'XXX' AS dummy FROM dual CONNECT BY ROWNUM <= 5000 );
CREATE INDEX idx_child ON child(id);

EXEC dbms_stats.gather_table_stats(USER, 'parent');
EXEC dbms_stats.gather_table_stats(USER, 'child');

Problem

The following query does a full table scan (both with 12.1 and 19.0) on child even though the CARDINALITY hint is considered.
Of course the real query needs some additional data from child.

SELECT child.id
FROM parent
JOIN
(
    SELECT child.id
    FROM child
    GROUP BY child.id
) child ON ( child.id = parent.id )
WHERE parent.id IN ( SELECT /*+ CARDINALITY( tab 1 ) */ COLUMN_VALUE FROM TABLE (sys.odcinumberlist(1) ) tab );

-----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |            |     1 |    19 |    35   (3)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI                  |            |     1 |    19 |    35   (3)| 00:00:01 |
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|            |     1 |     2 |    29   (0)| 00:00:01 |
|   3 |   NESTED LOOPS                         |            |  1000 | 17000 |     6  (17)| 00:00:01 |
|   4 |    VIEW                                |            |  1000 | 13000 |     6  (17)| 00:00:01 |
|   5 |     HASH GROUP BY                      |            |  1000 |  4000 |     6  (17)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                 | CHILD      |  5000 | 20000 |     5   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN                   | IDX_PARENT |     1 |     4 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Both indexes are used as expected if I replace the WHERE clause by this:

WHERE parent.id IN ( 1 );

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     5 |    35 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS       |            |     5 |    35 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN | IDX_PARENT |     1 |     4 |     1   (0)| 00:00:01 |
|   3 |   VIEW              |            |     5 |    15 |     1   (0)| 00:00:01 |
|   4 |    SORT GROUP BY    |            |     5 |    20 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN| IDX_CHILD  |     5 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

It also works when I remove the GROUP BY.

Any idea how to work around that?

oracle12c oracle19c oracle sql
2 Answers

The problem is that the ID column can contain NULL values. If you define the column as NOT NULL, then the index is used.

The index does not contain NULL values. But the GROUP BY must contain this data.

When you restricted the parent.id to 1 in your example, the database could use the index for the concrete value.

user_118268690

You may get the required behviour using the MERGE hint

SELECT child.id
FROM parent
JOIN
(
    SELECT /*+ MERGE */ child.id  ---<<<<< merge the subquery
    FROM child
    GROUP BY child.id
) child ON ( child.id = parent.id )
WHERE parent.id IN ( SELECT /*+ CARDINALITY( tab 1 ) */ COLUMN_VALUE FROM TABLE (sys.odcinumberlist(1) ) tab );

The execution plan is as follows

--------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |            |     5 |    70 |    32   (7)| 00:00:01 |
|   1 |  HASH GROUP BY                            |            |     5 |    70 |    32   (7)| 00:00:01 |
|   2 |   NESTED LOOPS                            |            |     5 |    70 |    31   (4)| 00:00:01 |
|   3 |    NESTED LOOPS                           |            |     1 |    10 |    30   (4)| 00:00:01 |
|   4 |     SORT UNIQUE                           |            |     1 |     2 |    29   (0)| 00:00:01 |
|   5 |      COLLECTION ITERATOR CONSTRUCTOR FETCH|            |     1 |     2 |    29   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN                     | IDX_PARENT |     1 |     8 |     0   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN                       | IDX_CHILD  |     5 |    20 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("PARENT"."ID"=VALUE(KOKBF$))
   7 - access("CHILD"."ID"="PARENT"."ID")

I guess your child table is too small that the CBO does not consider this plan as the best one; but it can have also other reasons.

Additional Note

There is a rather big difference between the predicates

parent.id IN ( subquery )   and

parent.id IN ( 1 )

In the latter case Oracle simple can push the predicate (access("CHILD"."ID"=1)) in the group by subquery. (see hint PUSH_PRED).

But anyway if you 1) know that the subquery returns only one row and 2) you help a little with the predicate the Oracle CBO gets it right without hints

Here the slightly changed query according to 1) and 2) - see comments

SELECT child.id
FROM parent
JOIN
(
    SELECT child.id
    FROM child
    GROUP BY child.id
) child ON ( child.id = parent.id )
WHERE child.id  /* 2) match with *child.id* to help Oracle to unnest */
 =  /* 1) use equal predicate as there is ony one row */
( SELECT /*+ CARDINALITY( tab 1 ) */ COLUMN_VALUE FROM TABLE (sys.odcinumberlist(1) ) tab );

The plan

--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |            |     5 |    85 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                             |            |     5 |    85 |     3   (0)| 00:00:01 |
|   2 |   VIEW                                    |            |     5 |    65 |     3   (0)| 00:00:01 |
|   3 |    HASH GROUP BY                          |            |     5 |    25 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                      | IDX_CHILD  |     5 |    25 |     3   (0)| 00:00:01 |
|   5 |      COLLECTION ITERATOR CONSTRUCTOR FETCH|            |     1 |     2 |    29   (0)| 00:00:01 |
|*  6 |   INDEX UNIQUE SCAN                       | IDX_PARENT |     1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CHILD"."ID"= (SELECT /*+ OPT_ESTIMATE (TABLE "TAB"@"SEL$4" ROWS=1.000000 ) */ 
              VALUE(KOKBF$) FROM TABLE() "KOKBF$0"))
   6 - access("CHILD"."ID"="PARENT"."ID")
user_48081220
Related