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?
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")