How to make hash join use index instead of fts 50 million rows
I have a merge statement:
MERGE INTO P DEST
USING T SRC
ON (DEST.col1 = SRC.col1
AND DEST.col2 = SRC.col2
AND DEST.col3 = SRC.col3
AND DEST.col4 = SRC.col4
AND DEST.col5 = SRC.col5)
WHEN MATCHED THEN UPDATE SET col6 = 'N' WHERE DEST.col6 IS NULL;
here is the plan:
PLAN_TABLE_OUTPUT
I've created an index on p(col1,col2,col3,col4,col5), but above plan doesn't change, still fts on table p. I also tried setting workarea_size_policy=manual and increased sort_area_size to 2G, still plan doesn't change.
MERGE INTO P DEST
USING T SRC
ON (DEST.col1 = SRC.col1
AND DEST.col2 = SRC.col2
AND DEST.col3 = SRC.col3
AND DEST.col4 = SRC.col4
AND DEST.col5 = SRC.col5)
WHEN MATCHED THEN UPDATE SET col6 = 'N' WHERE DEST.col6 IS NULL;
here is the plan:
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (%CPU) |
| 0 | MERGE STATEMENT | 799K | 77M | 325K (2) | ||
| 1 | MERGE | P | ||||
| 2 | VIEW | |||||
| 3 | HASH JOIN | 799K | 87M | 994M | 325K (2) | |
| 4 | INDEX FAST FULL SCAN | T_IOT | 37M | 568M | 68153 (1) | |
| 5 | TABLE ACCESS FULL | P | 60M | 5683M | 241K (2) |
I've created an index on p(col1,col2,col3,col4,col5), but above plan doesn't change, still fts on table p. I also tried setting workarea_size_policy=manual and increased sort_area_size to 2G, still plan doesn't change.
0