I found just now that materialization can not be considered by optimizer if there are no predicates in subquery factoring clause. Of course, i mean cases without forcing materialization through hint “materialize”.
Update: I did some additional tests and found:
- with “table()” but without “xmltable” materialization occurs always regardless of existence of predicates or another tables in subquery factoring clause
- with “xmltable” behavior is very strange – decision about materialization depends from ‘columns …’ clause: when it exists – materialization occurs, if not – not occurs.
- with “selects a subset of table columns” as David Aldridge said – decision still depends from predicates existence