Цитата: The precompute_subquery hint will take subquery text out of the subquery section, fire it separately (before running main query) in a recursive call context, fetch the results and pass these to main query "filter" condition as a list of OR conditions.
So a query like SELECT * FROM t1 WHERE col1 IN (SELECT /*+ PRECOMPUTE_SUBQUERY */ col2 FROM t2) will be executed as 2 different queries.
1st (recursive) query will be something like:
SELECT DISTINCT * FROM (SELECT /*+ PRECOMPUTE_SUBQUERY */ col2 FROM t2)).
The results are fetched, kept in memory and now 2nd (main) query will be executed, but it won't have to go to the "t2" table anymore. Instead the results from t2 are passed to the filter condition on "t1" as a bunch of OR conditions ( filter = "t1.col1 = 1 OR t1.col1 = 2 OR ..." etc).
This is called subquery unfolding I think and it's different from the query block unparsing which is used for distributed queries. I've seen it used in OLAP queries.
As this is an undocumented hint, it should not be used by developers! The subquery is actually executed during soft parsing time, thus multiple executions of the same child cursor may potentially return wrong results if resultset of the subquery changes (unless Oracle always forces another full parse of these cursors somehow - in which case you can end up with library cache/shared pool latch contention if misusing this feature).
для памяти...