Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 92871 invoked from network); 12 Jul 2007 18:40:22 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 12 Jul 2007 18:40:22 -0000 Received: (qmail 52297 invoked by uid 500); 12 Jul 2007 18:40:24 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 52270 invoked by uid 500); 12 Jul 2007 18:40:24 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 52261 invoked by uid 99); 12 Jul 2007 18:40:24 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 12 Jul 2007 11:40:24 -0700 X-ASF-Spam-Status: No, hits=1.7 required=10.0 tests=DNS_FROM_AHBL_RHSBL,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (herse.apache.org: 32.97.110.149 is neither permitted nor denied by domain of qozinx@gmail.com) Received: from [32.97.110.149] (HELO e31.co.us.ibm.com) (32.97.110.149) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 12 Jul 2007 11:40:20 -0700 Received: from d03relay02.boulder.ibm.com (d03relay02.boulder.ibm.com [9.17.195.227]) by e31.co.us.ibm.com (8.13.8/8.13.8) with ESMTP id l6CIdwiX002550 for ; Thu, 12 Jul 2007 14:39:58 -0400 Received: from d03av01.boulder.ibm.com (d03av01.boulder.ibm.com [9.17.195.167]) by d03relay02.boulder.ibm.com (8.13.8/8.13.8/NCO v8.3) with ESMTP id l6CIdwYj229690 for ; Thu, 12 Jul 2007 12:39:58 -0600 Received: from d03av01.boulder.ibm.com (loopback [127.0.0.1]) by d03av01.boulder.ibm.com (8.12.11.20060308/8.13.3) with ESMTP id l6CIdw9T007290 for ; Thu, 12 Jul 2007 12:39:58 -0600 Received: from [127.0.0.1] (svl-arbrown.svl.ibm.com [9.30.38.148]) by d03av01.boulder.ibm.com (8.12.11.20060308/8.12.11) with ESMTP id l6CIduLa007163 for ; Thu, 12 Jul 2007 12:39:58 -0600 Message-ID: <4696757B.5030602@gmail.com> Date: Thu, 12 Jul 2007 11:39:55 -0700 From: Army User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.1) Gecko/20040707 X-Accept-Language: en-us, en MIME-Version: 1.0 To: derby-dev@db.apache.org Subject: Re: questions about querytreenode and optimization References: <5ba791c80707111628w365141b5p5a7210e15b3ba1f0@mail.gmail.com> <469626A8.8090207@sun.com> <5ba791c80707120710s2146c16cp4cf02fe718cd12ae@mail.gmail.com> In-Reply-To: <5ba791c80707120710s2146c16cp4cf02fe718cd12ae@mail.gmail.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org 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). >> > 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