Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 13764 invoked from network); 14 Jul 2010 04:02:50 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 14 Jul 2010 04:02:50 -0000 Received: (qmail 31827 invoked by uid 500); 14 Jul 2010 04:02:49 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 31628 invoked by uid 500); 14 Jul 2010 04:02:48 -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 31620 invoked by uid 99); 14 Jul 2010 04:02:47 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 14 Jul 2010 04:02:47 +0000 X-ASF-Spam-Status: No, hits=4.7 required=10.0 tests=FREEMAIL_FROM,FREEMAIL_REPLY,HTML_MESSAGE,RCVD_IN_DNSWL_NONE,SPF_PASS,T_TO_NO_BRKTS_FREEMAIL X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of erandajira@gmail.com designates 74.125.83.172 as permitted sender) Received: from [74.125.83.172] (HELO mail-pv0-f172.google.com) (74.125.83.172) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 14 Jul 2010 04:02:39 +0000 Received: by pvd12 with SMTP id 12so3066163pvd.31 for ; Tue, 13 Jul 2010 21:02:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:content-type; bh=tjzV7SAYOnKOpFG63DFogAllMHwTTmzuTFpi0rMCyGc=; b=xALAzQr9wlvEod8Dd16Gcj6Z/KUyMAWqAsLRoqy5M3kYfKcXEd5FE6hvm3MkuaB7hv Op2JLB6NHc5f4q9ntySTQupdzHDZhi/qeXACHPvkoKx8SxC3M5xLna0p/Gb/u285AfgB vCLJ510mxYLwU36FpIMRJorzF0purA1O8BDCc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=R3N+5KkozrV3cQOqvcmxfT0LoUk9BJ4m5fXS7Aoaz2p5LquGo64qY2iTGnOaqgrvUu WJ54JRaB+4wEvm2oGG0s7a5x5J65wkWj5r9E6/LZDfWTIgZ57cR3uZKZc76oFDZWhjd+ Vi2ZTNZ+nFnGpvd32tfz7eM4kU0R+ck/KaNkE= MIME-Version: 1.0 Received: by 10.142.148.10 with SMTP id v10mr19979192wfd.105.1279080137173; Tue, 13 Jul 2010 21:02:17 -0700 (PDT) Received: by 10.142.131.1 with HTTP; Tue, 13 Jul 2010 21:02:17 -0700 (PDT) In-Reply-To: References: Date: Wed, 14 Jul 2010 09:32:17 +0530 Message-ID: Subject: Re: JIRA Issues that would nice to have a Graphical Query Plan Attached From: Eranda Sooriyabandara To: derby-dev@db.apache.org Content-Type: multipart/alternative; boundary=000e0cd28dba3732cc048b510d25 X-Virus-Checked: Checked by ClamAV on apache.org --000e0cd28dba3732cc048b510d25 Content-Type: text/plain; charset=ISO-8859-1 Hi Nirmal, Thanks for the details and I will go through it. >>SELECT a.USER_ID, a.USER_NAME, b.account_id FROM Users a JOIN Messaged_Users b ON a.USER_ID=b.USER_ID AND b.account_id=(?) It is not a sql query. It's a prepared statement. if you are using ij you can do it as follows ij> prepare s1 as 'SELECT a.USER_ID, a.USER_NAME, b.account_id FROM Users a JOIN Messaged_Users b ON a.USER_ID=b.USER_ID AND b.account_id=(?)' and then the execution ij> execute s1 using 'values(1)'; <===== This should be tracked Thanks Eranda On Tue, Jul 13, 2010 at 10:29 PM, Nirmal Fernando wrote: > Hi Eranda, > > Please see my comments inline. > > > Did you do it to prepared statement? > > I did it for this query: > SELECT a.USER_ID, a.USER_NAME, b.account_id FROM Users a JOIN > Messaged_Users b ON a.USER_ID=b.USER_ID AND b.account_id=(?) > > I suppose you need to know the query plan followed by the optimizer, > for that query. > > > Also need a little explanation to understand the doc. > > It's still in very basic HTML stage, sorry if it's not that clear. > It displays the plan followed by the optimizer as a tree. > In this case query has executed like: > > PROJECTION > | > | > HASHJOIN > | > _ |_____________ > | | > TABLESCAN HASHSCAN > > Under each node selected portion of details are shown > if they're not null. > > If you want to know the meanings of entries in tables > you can refer to sysxplain tables > (http://db.apache.org/derby/docs/dev/ref/rref_xplain_tables.html). > > Thanks. > > > Thanks > > Eranda > > > > On Tue, Jul 13, 2010 at 9:39 PM, Nirmal Fernando > > > wrote: > >> > >> Hi Eranda, > >> > >> I've attached a PDF to the JIRA issue. > >> > >> Thanks for the request! > >> > >> On Tue, Jul 13, 2010 at 2:09 PM, Eranda Sooriyabandara > >> wrote: > >> > > >> > Hi Nirmal, > >> > Can you do it for the following issue > >> > https://issues.apache.org/jira/browse/DERBY-3892 > >> > Thanks > >> > Eranda > >> > On Tue, Jul 13, 2010 at 12:47 PM, Nirmal Fernando > >> > wrote: > >> >> > >> >> Hi All, > >> >> > >> >> These days we're testing the basic HTMLs generated using > >> >> PlanExporter tool. So, I would really happy to attach Graphical Query > >> >> Plans > >> >> for JIRA issues which may possibly helpful to understand where's > >> >> the problem. > >> >> > >> >> If you know any issues that generates wrong results please reply to > >> >> this thread, > >> >> with the link to the JIRA. It would be nice if you have sql commands > to > >> >> setup > >> >> the environment needed to that issue. > >> >> > >> >> Please note that issues with exceptions/assert failure can't import > to > >> >> a Graphical Query Plan, > >> >> since as for those queries, there's no statistics captured by Xplain > >> >> tables. > >> >> > >> >> Thanks. > >> >> > >> >> -- > >> >> Best Regards, > >> >> Nirmal > >> >> > >> >> C.S.Nirmal J. Fernando > >> >> Department of Computer Science & Engineering, > >> >> Faculty of Engineering, > >> >> University of Moratuwa, > >> >> Sri Lanka. > >> >> > >> > > >> > >> > >> > >> -- > >> Best Regards, > >> Nirmal > >> > >> C.S.Nirmal J. Fernando > >> Department of Computer Science & Engineering, > >> Faculty of Engineering, > >> University of Moratuwa, > >> Sri Lanka. > > > > > > > > -- > Best Regards, > Nirmal > > C.S.Nirmal J. Fernando > Department of Computer Science & Engineering, > Faculty of Engineering, > University of Moratuwa, > Sri Lanka. > --000e0cd28dba3732cc048b510d25 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Nirmal,
Thanks for the details and I will go through it.

>&= gt;SELECT a.USER_ID, a.USER_NAME, b.account_id FROM Users a JOIN
Messaged_Users b ON a.USER_ID=3Db.USER_ID AND b.account_id=3D(?)
It is n= ot a sql query. It's a prepared statement.

if you are using ij y= ou can do it as follows
ij> prepare s1 as 'SELECT a.USER_ID, a.US= ER_NAME, b.account_id FROM Users a JOIN
Messaged_Users b ON a.USER_ID=3Db.USER_ID AND b.account_id=3D(?)'
an= d then the execution
ij> execute s1 using 'values(1)';=A0=A0= =A0 <=3D=3D=3D=3D=3D This should be tracked

Thanks
Eranda
=
On Tue, Jul 13, 2010 at 10:29 PM, Nirmal Fernando <nirmal070125@gmail.com> wrote:
Hi Eranda,

Please see my comments inline.

> Did you do it to prepared statement?

I did it for this query:
SELECT a.USER_ID, a.USER_NAME, b.account_id FROM Users a JOIN
Messaged_Users b ON a.USER_ID=3Db.USER_ID AND b.account_id=3D(?)

I suppose you need to know the query plan followed by the optimizer,
for that query.

> Also need a little explanation to understand the doc.

It's still in very basic HTML stage, sorry if it's not that c= lear.
It displays the plan followed by the optimizer as a tree.
In this case query has executed like:

PROJECTION
=A0 =A0 |
=A0 =A0 |
HASHJOIN
=A0 =A0 |
=A0_ =A0|_____________
| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
TABLESCAN =A0 =A0 =A0 =A0 HASHSCAN

Under each node selected portion of details are shown
if they're not null.

If you want to know the meanings of entries in tables
you can refer to sysxplain tables
(http://db.apache.org/derby/docs/dev/ref/rref_xplain_tab= les.html).

Thanks.

> Thanks
> Eranda
>
> On Tue, Jul 13, 2010 at 9:39 PM, Nirmal Fernando <nirmal070125@gmail.com>
> wrote:
>>
>> Hi Eranda,
>>
>> I've attached a PDF to the JIRA issue.
>>
>> Thanks for the request!
>>
>> On Tue, Jul 13, 2010 at 2:09 PM, Eranda Sooriyabandara
>> <erandajira@gmail.com> wrote:
>> >
>> > Hi Nirmal,
>> > Can you do it for the following issue
>> > https://issues.apache.org/jira/browse/DERBY-3892
>> > Thanks
>> > Eranda
>> > On Tue, Jul 13, 2010 at 12:47 PM, Nirmal Fernando
>> > <nirmal070125@gm= ail.com> wrote:
>> >>
>> >> Hi All,
>> >>
>> >> These days we're testing the basic HTMLs generated us= ing
>> >> PlanExporter tool. So, I would really happy to attach Gra= phical Query
>> >> Plans
>> >> for JIRA issues which may possibly helpful to understand = where's
>> >> the problem.
>> >>
>> >> If you know any issues that generates wrong results pleas= e reply to
>> >> this thread,
>> >> with the link to the JIRA. It would be nice if you have s= ql commands to
>> >> setup
>> >> the environment needed to that issue.
>> >>
>> >> Please note that issues with exceptions/assert failure ca= n't import to
>> >> a Graphical Query Plan,
>> >> since as for those queries, there's no statistics cap= tured by Xplain
>> >> tables.
>> >>
>> >> Thanks.
>> >>
>> >> --
>> >> Best Regards,
>> >> Nirmal
>> >>
>> >> C.S.Nirmal J. Fernando
>> >> Department of Computer Science & Engineering,
>> >> Faculty of Engineering,
>> >> University of Moratuwa,
>> >> Sri Lanka.
>> >>
>> >
>>
>>
>>
>> --
>> Best Regards,
>> Nirmal
>>
>> C.S.Nirmal J. Fernando
>> Department of Computer Science & Engineering,
>> Faculty of Engineering,
>> University of Moratuwa,
>> Sri Lanka.
>
>



--
Best Regards,
Nirmal

C.S.Nirmal J. Fernando
Department of Computer Science & Engineering,
Faculty of Engineering,
University of Moratuwa,
Sri Lanka.

--000e0cd28dba3732cc048b510d25--