How do you create an index on a subquery factored temporary table?

Question

I've got a query which has a WITH statement for a subquery at the top, and I'm then running a couple of CONNECT BYs on the subquery. The subquery can contain tens of thousands of rows, and there's no limit to the depth of the CONNECT BY hierarchy. Currently, this query takes upwards of 30 seconds; is it possible to specify indexes to put on the temporary table created for the factored subquery to speed up the CONNECT BYs, or speed it up another way?

Answer 1

There is no way to do it right in the query: Oracle does not support Eager Spool.

You can temporarily store your resultset in an indexed temporary table and issue the CONNECT BY query against it.

However, for the unsargable equality conditions in the query, the CONNECT BY usually builds a hash table which is in most cases even better than an index.

Could you please post your query here?

Answer 2

You might be able to use the MATERIALIZE hint with query subfactoring so that the subquery isn't being rerun iteratively. While it's undocumented, it seems to reliably flush the results of a WITH clause into a temporary table.

Jonathan Lewis' blog has several examples of how it can be used. There is some risk, however, due to the hint's undocumented nature.