I believe that GTT are not supposed to be created and dropped on the fly, rather once created, it resides in the DB. A memory table, however, is used to hold the sub-result set while the query is under execution.

Notice the "TEMP TABLE TRANSFORMATION" step and the "TABLE ACCESS FULL SYS_TEMP_0FD9D662B_2E34FB" step. Seems pretty conclusive to me it is using a global temporary table to me.

BUT, consider the following.

- This is an undocumented feature. I've given you an example that definitely uses a GTT, but perhaps there are other circumstances that don't.- Oracle will often try to do many actions in memory (like sorts for example) and only overspill to temporary segments when there is not enough memory to complete the operation. Perhaps materialize can do this too. Perhaps not...- Yes. Like any DDL operation, you should not create and drop tables (including GTTs) on the fly, but this is an internal action of the optimizer, so however they do it, I can only assume it is efficient.

A few overriding messages from this:

- Arguing over undocumented features is not the most productive thing in the world. - If in doubt, trace it using a 10046 trace.- If regular trace doesn't help explain things, consider a 10053 trace to see exactly what the optimizer is doing.