You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Aakash Bordia <bo...@gmail.com> on 2007/07/12 01:28:48 UTC
questions about querytreenode and optimization
Hi, I am issuing a simple join query like:
select * from t1,t2 where t1.c1=t2.c1;
Here comes my novice questions(bear with me if some of these dont make
sense, trying to learn the compiler part):
1) I am not sure where to look for the accesspath(both base tables and
join), as the values in AccessPathImpl (at join or PRN or base table level)
seem un-intuitive.
2) Also it seems we hang the Level2OptimizerImpl objects off of the base
table FromBaseTable, but I dont see it up in the tree anywhere. Is there a
reason for this?
Any other pointers for learning about the querytreenodes are appreciated !
Thanks
Aakash
Re: questions about querytreenode and optimization
Posted by Aakash Bordia <bo...@gmail.com>.
Hello, Thanks for your response. You asked me about:
>> > 2) Also it seems we hang the Level2OptimizerImpl objects off of the
>> > base table FromBaseTable, but I dont see it up in the tree anywhere.
>> > Is there a reason for this?
What I meant is , after the modifyAccessPaths() has returned, I would expect
an overall optimizer object to hang off of the top cursornode which tells me
the enitre access strategy, but I see optimizer(Level2OptimizerImpl) as a
member of the trulyTheBestAccessPath wherever it is not null. I think this
is more of a design question than anything else. I am coming from another
compiler background, where the query tree and the optimizer plan are 2
different data structures. But in Derby, everything is going off of the same
tree and the separation of concerns is not that much, which is ok I think.
Just a different approach. I am guessing the class generation needs that
optimizer object at every node where there is a trulyTheBestAccessPath.
Thanks
Aakash
On 7/12/07, Army <qo...@gmail.com> wrote:
>
> Aakash Bordia wrote:
> > I wanted to mention that I am in the debugger, and whatever I asked was
> > in context of that (that is while debugging the code, I am not sure
> > where to find the access path in the QTN once the optimize method is
> > done with. Same for the second question).
>
> <snip>
>
> >> > 1) I am not sure where to look for the accesspath(both base tables
> and
> >> > join),
>
> If you're looking for the *final* join strategies, index choices, and join
> order
> for a query, then one option is to set the derby.language.logQueryPlanproperty
> to "true". Then after you execute a query and close the result set, the
> full
> query plan will be printed to the "derby.log" file. For large queries the
> plan
> can be a bit unruly, but generally speaking you can find the join
> strategy, join
> order, and choice of conglomerate (index or base table) along with other
> scan
> information from that plan.
>
> >> > as the values in AccessPathImpl (at join or PRN or base table
> >> > level) seem un-intuitive.
>
> AccessPathImpl is in fact the description of the access path for a given
> node.
> This object shows you the join strategy and conglomerate choice (index or
> base
> table) for a query tree node. The latter (conglomerate) only applies to
> base
> tables. The "conglomerate descriptor" within AccessPathImpl can be used
> to
> determine whether or not it's an index, and if so, which one.
>
> If you're looking for the access path after optimization is complete, then
> you
> might want to put a breakpoint in DMLStatementNode.optimizeStatement(),
> right
> after the line which says:
>
> resultSet = resultSet.modifyAccessPaths()
>
> When "modifyAccessPaths()" returns, all decisions regarding access paths
> have
> been made and have been saved within each query tree node. So then you
> can
> examine the tree rooted at "resultSet" to see all of the information you
> want.
> Look for the "trulyTheBestAccessPath" field for a given node; if that
> field is
> null or if it doesn't have the information you need, then the node
> probably has
> one or more children at which you should look.
>
> Note that if you are examining access paths *during* optimization, then
> "trulyTheBestAccessPath" will not be set. Instead you should look at the
> "currentAccessPath" and "bestAccessPath" fields.
>
> That said, trulyTheBestAccessPath (along with the other access path
> fields) is
> in fact an instance of AccessPathImpl, so if you find that to be
> non-intuitive,
> then perhaps your best bet is to use the query plan logging mentioned
> above?
>
> >> > 2) Also it seems we hang the Level2OptimizerImpl objects off of the
> >> > base table FromBaseTable, but I dont see it up in the tree anywhere.
> >> > Is there a reason for this?
>
> I'm not sure I understand what this question is asking; can you rephrase
> it?
>
> I'm glad to see that you are interested in the optimizer. If you are
> looking
> for more details about how things work (or how are they supposed to work),
> you
> might want to take a look at the following page from the Derby wiki:
>
> http://wiki.apache.org/db-derby/LanguageOptimize
>
> That may help you figure out where to trace while debugging, as well. If
> you
> have any questions, please feel free to ask.
>
> Army
>
>
Re: questions about querytreenode and optimization
Posted by Army <qo...@gmail.com>.
Aakash Bordia wrote:
> I wanted to mention that I am in the debugger, and whatever I asked was
> in context of that (that is while debugging the code, I am not sure
> where to find the access path in the QTN once the optimize method is
> done with. Same for the second question).
<snip>
>> > 1) I am not sure where to look for the accesspath(both base tables and
>> > join),
If you're looking for the *final* join strategies, index choices, and join order
for a query, then one option is to set the derby.language.logQueryPlan property
to "true". Then after you execute a query and close the result set, the full
query plan will be printed to the "derby.log" file. For large queries the plan
can be a bit unruly, but generally speaking you can find the join strategy, join
order, and choice of conglomerate (index or base table) along with other scan
information from that plan.
>> > as the values in AccessPathImpl (at join or PRN or base table
>> > level) seem un-intuitive.
AccessPathImpl is in fact the description of the access path for a given node.
This object shows you the join strategy and conglomerate choice (index or base
table) for a query tree node. The latter (conglomerate) only applies to base
tables. The "conglomerate descriptor" within AccessPathImpl can be used to
determine whether or not it's an index, and if so, which one.
If you're looking for the access path after optimization is complete, then you
might want to put a breakpoint in DMLStatementNode.optimizeStatement(), right
after the line which says:
resultSet = resultSet.modifyAccessPaths()
When "modifyAccessPaths()" returns, all decisions regarding access paths have
been made and have been saved within each query tree node. So then you can
examine the tree rooted at "resultSet" to see all of the information you want.
Look for the "trulyTheBestAccessPath" field for a given node; if that field is
null or if it doesn't have the information you need, then the node probably has
one or more children at which you should look.
Note that if you are examining access paths *during* optimization, then
"trulyTheBestAccessPath" will not be set. Instead you should look at the
"currentAccessPath" and "bestAccessPath" fields.
That said, trulyTheBestAccessPath (along with the other access path fields) is
in fact an instance of AccessPathImpl, so if you find that to be non-intuitive,
then perhaps your best bet is to use the query plan logging mentioned above?
>> > 2) Also it seems we hang the Level2OptimizerImpl objects off of the
>> > base table FromBaseTable, but I dont see it up in the tree anywhere.
>> > Is there a reason for this?
I'm not sure I understand what this question is asking; can you rephrase it?
I'm glad to see that you are interested in the optimizer. If you are looking
for more details about how things work (or how are they supposed to work), you
might want to take a look at the following page from the Derby wiki:
http://wiki.apache.org/db-derby/LanguageOptimize
That may help you figure out where to trace while debugging, as well. If you
have any questions, please feel free to ask.
Army
Re: questions about querytreenode and optimization
Posted by Anurag shekhar <An...@Sun.COM>.
After determining the best path (based on cost) a java class is
generated for the query. You can dump this class file on by setting
DumpClassFile to true.
anurag
Aakash Bordia wrote:
> Thanks Anurag, I did look at the documentation and the debug property
> you mentioned. I wanted to mention that I am in the debugger, and
> whatever I asked was in context of that (that is while debugging the
> code, I am not sure where to find the access path in the QTN once the
> optimize method is done with. Same for the second question).
>
> Thanks
> Aakash
>
>
> On 7/12/07, *Anurag Shekhar* <Anurag.Shekhar@sun.com
> <ma...@sun.com>> wrote:
>
> QueryTreeNode is constructed while parsing the sql statement. This is
> the base class of all
> the node created while parsing the sql statement. Probably you should
> look
> at java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj to
> see how three is generated.
> You can print the parse tree by turning on debug on "DumpParseTree".
>
> Best place to start on optimization will be the paper on derby
> optimizer
> http://db.apache.org/derby/papers/optimizer.html
>
> I am not sure if I have understood the your question about
> AccessPath can
> you please elaborate it ?
> anurag
>
> Aakash Bordia wrote:
> > Hi, I am issuing a simple join query like:
> > select * from t1,t2 where t1.c1=t2.c1;
> >
> > Here comes my novice questions(bear with me if some of these
> dont make
> > sense, trying to learn the compiler part):
> >
> > 1) I am not sure where to look for the accesspath(both base
> tables and
> > join), as the values in AccessPathImpl (at join or PRN or base
> table
> > level) seem un-intuitive.
> >
> > 2) Also it seems we hang the Level2OptimizerImpl objects off of the
> > base table FromBaseTable, but I dont see it up in the tree anywhere.
> > Is there a reason for this?
> >
> > Any other pointers for learning about the querytreenodes are
> appreciated !
> > Thanks
> > Aakash
>
>
Re: questions about querytreenode and optimization
Posted by Aakash Bordia <bo...@gmail.com>.
Thanks Anurag, I did look at the documentation and the debug property you
mentioned. I wanted to mention that I am in the debugger, and whatever I
asked was in context of that (that is while debugging the code, I am not
sure where to find the access path in the QTN once the optimize method is
done with. Same for the second question).
Thanks
Aakash
On 7/12/07, Anurag Shekhar <An...@sun.com> wrote:
>
> QueryTreeNode is constructed while parsing the sql statement. This is
> the base class of all
> the node created while parsing the sql statement. Probably you should
> look at java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj to
> see how three is generated.
> You can print the parse tree by turning on debug on "DumpParseTree".
>
> Best place to start on optimization will be the paper on derby optimizer
> http://db.apache.org/derby/papers/optimizer.html
>
> I am not sure if I have understood the your question about AccessPath can
> you please elaborate it ?
> anurag
>
> Aakash Bordia wrote:
> > Hi, I am issuing a simple join query like:
> > select * from t1,t2 where t1.c1=t2.c1;
> >
> > Here comes my novice questions(bear with me if some of these dont make
> > sense, trying to learn the compiler part):
> >
> > 1) I am not sure where to look for the accesspath(both base tables and
> > join), as the values in AccessPathImpl (at join or PRN or base table
> > level) seem un-intuitive.
> >
> > 2) Also it seems we hang the Level2OptimizerImpl objects off of the
> > base table FromBaseTable, but I dont see it up in the tree anywhere.
> > Is there a reason for this?
> >
> > Any other pointers for learning about the querytreenodes are appreciated
> !
> > Thanks
> > Aakash
>
>
Re: questions about querytreenode and optimization
Posted by Anurag Shekhar <An...@Sun.COM>.
QueryTreeNode is constructed while parsing the sql statement. This is
the base class of all
the node created while parsing the sql statement. Probably you should
look at java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj to
see how three is generated.
You can print the parse tree by turning on debug on "DumpParseTree".
Best place to start on optimization will be the paper on derby optimizer
http://db.apache.org/derby/papers/optimizer.html
I am not sure if I have understood the your question about AccessPath can
you please elaborate it ?
anurag
Aakash Bordia wrote:
> Hi, I am issuing a simple join query like:
> select * from t1,t2 where t1.c1=t2.c1;
>
> Here comes my novice questions(bear with me if some of these dont make
> sense, trying to learn the compiler part):
>
> 1) I am not sure where to look for the accesspath(both base tables and
> join), as the values in AccessPathImpl (at join or PRN or base table
> level) seem un-intuitive.
>
> 2) Also it seems we hang the Level2OptimizerImpl objects off of the
> base table FromBaseTable, but I dont see it up in the tree anywhere.
> Is there a reason for this?
>
> Any other pointers for learning about the querytreenodes are appreciated !
> Thanks
> Aakash