3 Replies Latest reply on Jun 22, 2015 12:29 PM by Steven Hawkins

    Idea:  API to force a re-plan

    Mark Addleman Master

      I've been thinking quite a bit about a few different use cases:

      • Incrementally built caches / materialized views
      • Altering client queries to enforce source-specific semantics
      • Rearranging a client query to guarantee a particular order of execution
      • Taking advantage of source-specific knowledge to influence costing

       

      I tried applying the PreParser API to these use cases but its fundamental problem is that most of the transformations that I want to do are related to sources.  What do you think of an API similar to the PreParser API but sits within the planner?  At first blush, if the API were to take a plan as input and produce an AST or null as output.  If the API produces an AST, the engine would restart the planner, calling the API again with the ne plan.  If the API produces null, the planner proceeds as normal.  Obviously, this can cause a loop but this falls in the category of "with great power comes...."

       

      Another idea is to give pass the CommandContext to the API.  This would allow the API to execute queries to obtain costing information - thus making the costing more dynamic.

       

      Thoughts?

        • 1. Re: Idea:  API to force a re-plan
          Steven Hawkins Master

          > At first blush, if the API were to take a plan as input and produce an AST or null as output.

           

          Unfortunately the classes involved are not in that great of shape for being exposed as an api, so I would be hesitant to do that.

           

          > Another idea is to give pass the CommandContext to the API.  This would allow the API to execute queries to obtain costing information - thus making the costing more dynamic.

           

          We should differentiate the scenarios.  To just obtain fresh costing information could be done with a fairly targeted enhancement.  To push less to the source could also be a relatively small change.  Beyond that are you primarily looking to influence the join structure?

          • 2. Re: Idea:  API to force a re-plan
            Mark Addleman Master

            > We should differentiate the scenarios

             

            Sounds good.  The separate scenarios are:

            1. Obtain costing information on a per query basis.  For API modeled as tables, the costing can be heavily influenced by the particular combination of parameters so a mechanism to evaluate costing after analyzing the query structure would be nice.  I imagine something similar to the way result set caching is handled in the translator.
            2. Pushing less to the source - What do you mean by this?
            3. Influencing the join structure - yes, but I'm having trouble the form this would take without getting into the middle of the planning phase.
            • 3. Re: Idea:  API to force a re-plan
              Steven Hawkins Master

              1) Yes that is doable.  A check prior to heart of join planning could give us a better idea about how to proceed.

              2) Indicate that there are parts of the query that the engine should execute rather than pushing down.

              3) #1 would hopefully be sufficient for this.