6 Replies Latest reply: Jan 26, 2009 12:42 PM by 247275 RSS

    precompute_subquery hint

    247275
      What exactly does the precompute_subquery hint do?
      Is the behaviour any different when working across distributed databases i.e. subquery data comes from remote database?
        • 1. Re: precompute_subquery hint
          Timur Akhmadeev
          Well, there's almost no information about this hint in internet. Why do you think it is related to queries with DB link?
          • 2. Re: precompute_subquery hint
            Tanel Poder
            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).

            --
            Tanel Poder
            http://blog.tanelpoder.com
            • 3. Re: precompute_subquery hint
              Timur Akhmadeev
              Hello, Tanel, thank you for explanation! The recursive query be like this:
              SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (SELECT /*+ PRECOMPUTE_SUBQUERY */ id FROM t2)
              And explain plan also shows effect of this hint:
              --------------------------------------------------------------------------
              | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
              --------------------------------------------------------------------------
              |   0 | SELECT STATEMENT  |      |    10 |   130 |     2   (0)| 00:00:01 |
              |*  1 |  TABLE ACCESS FULL| T1   |    10 |   130 |     2   (0)| 00:00:01 |
              --------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 1 - filter("ID"=1 OR "ID"=2 OR "ID"=3 OR "ID"=4 OR "ID"=5 OR "ID"=6
                            OR "ID"=7 OR "ID"=8 OR "ID"=9 OR "ID"=10)
              • 4. Re: precompute_subquery hint
                Tanel Poder
                Yep, whatever text is in the subquery, will be extracted in the semantic check phase of a query (after syntax check is done), including any comments etc in it, and a recursive query using that SQL is executed. So this extraction of a subquery is done purely by copying part of SQL text (string) out and executing it.

                The distributed queries over dblink work differently - the remote part of query text is built by "unparsing" the SQL accessing remote objects from main cursor.

                --
                Tanel Poder
                http://blog.tanelpoder.com
                • 5. Re: precompute_subquery hint
                  Tanel Poder
                  I wrote a detailed blog entry about this behaviour and internals:

                  [http://blog.tanelpoder.com/2009/01/23/multipart-cursor-subexecution-and-precompute_subquery-hint/]
                  • 6. Re: precompute_subquery hint
                    247275
                    Thanks for the detailed explanation.