db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2487) Enhance Derby with EXPLAIN Functionality
Date Thu, 05 Mar 2009 22:27:56 GMT

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

Bryan Pendleton commented on DERBY-2487:

Knut, thanks very much for the thoughts and suggestions. I have had many of the
same concerns, so it's very nice to have a chance to talk about them. Here's a couple
of my reactions:

- storing the data in temporary session-scoped tables is intriguing, but it seems like
  that would make it harder for people who want to intentionally include data from
  multiple sessions and/or retain the data beyond the end of their session; they'd have
  to copy the data out to their own tables to save it.

 - I agree that users could be confused by seeing multiple sessions data in the same
   tables, although there are fields that help them distinguish this data: session id,
   transaction id, drda id, etc.

 - your points about short-comings with respect to:
   - compressing/recovering space in the xplain tables
   - reset deleting all the data, even including data they might want to keep
   - having to upgrade the tables
   all seem valid, but all seem like they could be addressed by additional follow-on features.
   For example we could in a future release provide a system procedure which compressed
   the xplain tables, and could provide a modification to the reset procedure which allowed
   for only resetting a subset of the data (by session id, etc.).

- Your point about not wanting to incur costs for this feature when the user isn't
  using the feature is quite a strong point, and it definitely concerns me too. Adding
  6 new system tables isn't something we should do casually. In addition to the
  ideas you had (use temporary tables, use user tables), it seems like we could
  have a few more possibilities:
  - create the tables as system tables, but do it on demand, when the feature is first used.
  - create the tables as system tables, but not automatically nor on demand; instead,
    have some additional system procedures which the user could call to:
    a) create the system tables for the xplain feature, prior to using it
    b) drop the system tables when they were not using the feature and wanted to avoid overhead.

- The idea about being able to use ordinary user tables for storing this data is also
   very interesting, but it seems to raise some further questions:
   - the user would presumably need to pre-create these tables, and give them the
     right structure. Or would we auto-create them for the user, following some pattern like
you suggest?
   - it seems like this would introduce some new potential error conditions and
     opportunities for the user to confuse themselves:
     - tables already existed, or couldn't be created (security problems, resource problems)
     - tables existed, but the schema wasn't a perfect match (would we allow this anyway if

       the schema was a superset of what we needed?)
     - tables unexpectedly disappeared or misbehaved in mid-flight.

- I'm also not really sure what we'd have to do to the current implementation to use user
  that is, how does it affect the code? I guess we'd have to store the information about which
  user tables to use, presumably in the LCC where the patch currently stores the xplain style
and mode,
  and then we'd have to change the visitor code to have the appropriate code for inserting
  data into the user tables, which hopefully is quite similar to the code which inserts the
   into the sytsem tables, except that the system tables are quite easy to find from a code
   point of view, because we just can use the internal system catalog numbers, whereas user
   tables would have to go through bind processing to figure out which actual tables to use.

Thanks very much for raising these concerns; I'm very interested to hear 
more about what you and others think about this issue.

> Enhance Derby with EXPLAIN Functionality
> ----------------------------------------
>                 Key: DERBY-2487
>                 URL: https://issues.apache.org/jira/browse/DERBY-2487
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions:
>            Reporter: Felix Beyer
>            Assignee: Bryan Pendleton
>            Priority: Minor
>         Attachments: Derby physical XPLAIN schema.png, incorporateTrunkChanges.diff,
removeSourceDepth.diff, RSProtocolNew.pdf, rts.xls, small logical xplain schema.pdf, startRegressionTest.diff,
startRegressionTest.diff, startUpgradeTests.diff, updateRegressionTests.diff, updateRegressionTests.diff,
usage.txt, xplain_patch_v1.txt, xplainClasses.pdf
> This enhancement extends Derby with EXPLAIN functions. Users want to have more feedback
than they`re getting with the current RuntimeStatistics facility. This extension is based
on the RuntimeStatistics/ResultSetStatistics functions / classes. 

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message