Vadim B. Mikheev wrote:
>
> Peter T Mount wrote:
> >
> > On Wed, 4 Mar 1998 ocie@paracel.com wrote:
> >
> > > This may sound like an obvious question, but if a user defines a
> > > query, do we save the query plan? This would reduce the
> > > communications between the client and server (a small gain), and allow
> > > the server to start executing the query as soon as it recognized the
> > > name of the stored query and parsed the arguments.
> >
> > Not sure ofhand, but it would be useful for JDBC's PreparedStatement and
> > CallableStatement classes
>
> We can implement it very easy, and fast. Execution plan may be reused
> many times. Is this feature in standard ?
> What is proposed syntax if not ?
I don't think it is so much a question of syntax as it is a question
of what we do in the backend. Suppose I create a stored query in SQL.
We already store the SQL source for this in the database, right? So
when it comes time to execute the query, we take this SQL and execute
it as if the user had entered it directly. What I am proposing would
be to basically store the compiled query plan as well.
I do see a couple sticky points:
We would need some information about which variables are to be
substituted into this query plan, but this should be fairly
straightforward.
Some querys may not respond well to this, for example, if a table had
an index on an integer field f1, this would probably be the best way
to satisfy a select where f1<10. But if this were in a query as f1<x,
then a sufficiently high value of x might make this not such a good
way to run the query. I haven't looked into this, but I would assume
that the optimizer relies on the specific values in such cases.
We need to be able to handle changes to the structures and contents of
the tables. If the query plan is built and we add 10000 rows to a
table it references, the query should probably be recompiled. We
could probably do this at vacuum time. There is also a small chance
that a table or index that the query plan was using is dropped. We
could automatically rebuild the query if the table was created after
the query was compiled.
Boy, to look at this, you'd think I had already built one of these :)
I haven't but I'm willing to give it a shot.
Ocie