db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "C.S. Nirmal J. Fernando (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
Date Sun, 18 Apr 2010 10:52:25 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12858291#action_12858291

C.S. Nirmal J. Fernando commented on DERBY-4587:

Hi Bryan,

I had a look on using XML operations in Derby more thoroughly and found out following important

* In order to retrieve XML data using XMLSERIALIZE, we should first have a table column which
is in XML data type.

         So is it possible to change SYSXPLAIN tables such that they store XML data of each
row inserted, in a XML data type column?

* When inserting XML data we have to insert some set of XML keywords, such as Document, Preserve

         eg: insert into t(a) values (XMLPARSE(DOCUMENT '<title>Derby</title>'

* When using XMLSERIALIZE we should use 'as' sql key word,

        eg: select XMLSERIALIZE(a as CLOB) from t;   //we can use varchar as well

* Execution of XMLSERIALIZE statement will result a small XML fragment.

        eg: <title>Derby</title>

I think this will be an easy way, if we can somehow add a XML data type columns to SYSXPLAIN

May be we can store these small xml fragments in another table with XML data type columns
and retrieve the large XML document after the execution of the query. (Note: We may have to
write those rows retrieved into a .xml file)


> Add tools for improved analysis and understanding of query plans and execution statistics
> -----------------------------------------------------------------------------------------
>                 Key: DERBY-4587
>                 URL: https://issues.apache.org/jira/browse/DERBY-4587
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL, Tools
>            Reporter: Bryan Pendleton
>            Assignee: Bryan Pendleton
>         Attachments: Derby Query Plan Screen Shot 2.jpg, Derby_Query_Plan_Screen_Shot.jpg,
PostgreSQL license.jpg, Read_Me.txt, Source.rar
> I think it would be great to see some work in the area of tools for helping
> with the analysis of complex query execution. Quite frequently, users of
> Derby have trouble comprehending (a) how their query is being translated
> into a query plan by the optimizer, and (b) what the execution-time resource
> usage of the various parts of the query is.
> There are low-level features in Derby which capture this information and
> record it, such as logQueryPlan, and the XPLAIN tables, but there is a lot
> of opportunity for designing higher-level tools which can process the query
> plan and execution statistics information and present it in a more
> comprehensible fashion. 

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message