[
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 tables;
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 the
data into the user tables, which hopefully is quite similar to the code which
inserts the data
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: 10.2.2.0
> 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.
