MERGE JOIN on two indexes still causing a SORT?

INDEX_ASC (or just INDEX) is the hint you might want to try in order to compare performance with real data.

I am a little surprised that you get any kind of index scan for the outer row source, since B*Tree indexes cannot find NULL keys and ZZ_BASE does not have a NOT NULL constraint. Adding that and hinting a bit more will get you the full scan in index order of the ZZ_C_I index. That does not save you the SORT JOIN step, unfortunately, but at least it should be much faster - O(n) - since the data is already sorted.

alter table zz_base modify (id not null);SELECT   /*+ leading(zz_base) USE_MERGE(ZZ_CHILD)       index_asc(zz_base (id)) index(zz_child (id)) */ ZZ_BASE.ID FROM ZZ_BASE left outer join ZZ_CHILD on zz_base.id=zz_child.id;

This query uses the following execution plan:

------------------------------------------------------------------------------------| Id  | Operation         | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |        |  1000K|  9765K|       |  8241   (3)| 00:00:50 ||   1 |  MERGE JOIN OUTER |        |  1000K|  9765K|       |  8241   (3)| 00:00:50 ||   2 |   INDEX FULL SCAN | ZZ_B_I |  1000K|  4882K|       |  2258   (2)| 00:00:14 ||*  3 |   SORT JOIN       |        |  1000K|  4882K|    22M|  5983   (3)| 00:00:36 ||   4 |    INDEX FULL SCAN| ZZ_C_I |  1000K|  4882K|       |  2258   (2)| 00:00:14 |------------------------------------------------------------------------------------