How do I get rid of the sort merge join? The explain plan and the query are given below:
Code:
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 575 | 19 | | |
| 1 | SORT UNIQUE | | 1 | 575 | 19 | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| ISSUE | 1 | 29 | 2 | ROWID | ROW L |
| 3 | NESTED LOOPS | | 1 | 575 | 15 | | |
| 4 | NESTED LOOPS | | 4 | 2184 | 13 | | |
| 5 | NESTED LOOPS | | 7 | 2457 | 11 | | |
| 6 | MERGE JOIN CARTESIAN | | 6406 | 1707K| 11 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 | PARTITION HASH ALL | | | | | 1 | 8 |
|* 8 | INDEX FAST FULL SCAN | SYS_IOT_TOP_30857 | 7 | 1365 | 2 | 1 | 8 |
| 9 | BUFFER SORT | | 980 | 76440 | 9 | | |
| 10 | PARTITION HASH ALL | | | | | 1 | 12 |
|* 11 | INDEX FAST FULL SCAN | SYS_IOT_TOP_30830 | 980 | 76440 | 2 | 1 | 12 |
| 12 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
|* 13 | INDEX UNIQUE SCAN | SYS_IOT_TOP_30830 | 1 | 78 | | KEY | KEY |
| 14 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
|* 15 | INDEX RANGE SCAN | SYS_IOT_TOP_30857 | 1 | 195 | 1 | KEY | KEY |
|* 16 | INDEX RANGE SCAN | XIF9ISSUE1 | 115 | | 1 | | |
----------------------------------------------------------------------------------------------------