The query is only performant when the domains are hardcoded in the WHERE clause because of how Oracle handles the ROWNUM keyword.

When using ROWNUM, Oracle must materialize the sub-query to generate the row numbering, which generates all the rows from the XMLTABLE at that point. This means that the SQL engine cannot use an index on the column and is forced to perform a full table scan.

In contrast, when you filter on i.name in(...), Oracle can use an index on the column because it knows exactly how many rows will be returned. Without this filter, the explain plan shows that Oracle is performing a full table scan, which is much slower.

If you remove ROWNUM from the SELECT clause of the sub-query, Oracle does not need to materialize the sub-query, and can re-write the query to push the sub-query into the outer query. This allows Oracle to perform the LEFT JOIN before generating the XMLTABLE, which enables it to use an index on the column and avoid a full table scan.

To make this change, you would need to modify your sub-query to not use ROWNUM, like so:

SELECT /*+ NOLOGGING */ 
       b.domain_name,
       c.subtype_code,
       c.subtype_description,
       c.subtype_field,
       c.table_name
FROM   (SELECT distinct domain_name FROM ... /* some other query */) b
JOIN   table1 t ON b.domain_name = t.domain_name
LEFT JOIN xmltable (...) c ON t.sub_type_name = c.sub_type_name;

In this modified sub-query, the NOLOGGING hint is used to force Oracle not to log the results of the sub-query. This can improve performance in some cases.

Also note that you may need to use the UNLISTED keyword instead of NOLOGGING, because it’s a new feature available from 12cR2 onwards, and if your database version is lower than that, then using UNLISTED might give you better results.

Last modified on 2025-02-27