5 Replies Latest reply on May 29, 2015 10:41 AM by Steven Hawkins

    Feature request - metadata determined order of access patterns

    Mark Addleman Master

      I'd like to have metadata control over the order in which the planner picks the appropriate access pattern.  Something as simple as the planner always picks the first matching access pattern would suffice.

        • 1. Re: Feature request - metadata determined order of access patterns
          Steven Hawkins Master

          I don't know if this makes sense as a stand alone issue.  There is currently no decision that is based upon the order of the access patterns.

          • 2. Re: Feature request - metadata determined order of access patterns
            Mark Addleman Master

            My case is that table T that models an API.  The API has three forms:

            1. api(a)
            2. api(a,b)

             

            To ensure the planner pushes down appropriate parameters, I would want to create two access patterns but since I have no explicit control over which access pattern the planner chooses, I cannot guarantee that the planner will push qualifers for a and b if the client supplies both. 

             

            This goes back to the problem referenced in Feature request - depjoin-like functionality for stored procedures it's really about trying to create parameterized view-like functionality

            • 3. Re: Feature request - metadata determined order of access patterns
              Steven Hawkins Master

              This starts to get into a pretty grey area.  If you specify predicates against a and b, and they can be pushed, then under most circumstances we will use both - this isn't expressly guaranteed, but we don't currently have a situation where this wouldn't be true.

               

              If there is an access pattern specifying both, then that will be required.  The questions are what does it mean to be missing the b predicate, and what does it mean to have a b predicate that cannot be pushed (usage of a function, unsupported comparison, etc.)  In the former case it's pretty clear that your intent is that the source access should still be allowed since there would be an alternative access pattern.  However the latter case is not clear.  Do you intend for the access to still happen only using a or is it a usage error?  Are you allowing for b values to be projected - select a, b... from T where a = x and b = y?

              • 4. Re: Feature request - metadata determined order of access patterns
                Mark Addleman Master

                Thinking out loud... If the api is being modeled as a table, it must follow table semantics.  In this case, an api call that qualifies argument a must return a superset of the same api call that qualifies arguments a and b.  At best, SELECT * FROM (api(1, 2)) is a performance optimization on SELECT * FROM (api(a)) WHERE api.b=2.  So, if there are two access patterns, one specifying a and another specifying a & b, not pushing b should still allow access.

                 

                Following the same logic, select a,b from T where a = x and b = y had better yield either an empty result set or a single row of x, y.

                 

                Now, the question is, how applicable is the underlying assumption that apis should be modeled as a table.  Obviously, in the general case, there's no reason why api(a,b) should return a subset of api(a).  From that perspective, it's much better to model the api as a stored procedure.  This wouldn't be a problem except that it becomes difficult - or impossible - to apply a performance optimization for batches.  This gets us right back to the other feature request.

                 

                Ultimately, for this particular request, I don't think it's worth it.