You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamta A. Satoor (JIRA)" <de...@db.apache.org> on 2006/01/23 07:48:12 UTC
[jira] Created: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Document optimizer overrides which were introduced in 10.2
----------------------------------------------------------
Key: DERBY-855
URL: http://issues.apache.org/jira/browse/DERBY-855
Project: Derby
Type: Sub-task
Components: Documentation
Versions: 10.2.0.0
Reporter: Mamta A. Satoor
Fix For: 10.2.0.0
Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12365964 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Eric, I noticed 2 places in ctunoptimzoverride.html, where only single - is shown in front of DERBY-PROPERTIES. There should be 2 dashes rather than 1.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, derby855-2.diff, derby855-3.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364436 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Couple comments
1)ctunoptimzoverride.html does a good job of cautioning the users to be careful about the correct syntax when using the -- DERBY-PROPERTIES clause. It will be good to point the users at this time to ctundepthoptover.html where they can verfiy if their overrides were correctly identified by the parser or not.
2)There shouldn't be any space between nested and loop when given as a value to joinStrategy.
3)The Tuning Guide has sections "Working with RunTimeStatistics"->"Analyzing the infromation". Under "Analyzing the information" page, can you add optimizer overrides to the existing list of Statistics timing, statement execution plan and optimizer estimates?
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12365522 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Eric, one other thing. In the Tuning Guide, under Analyzing the information, I think you have already added Optimizer Overrides as a hyper link along with Statistics timing, Statement execution plan, Optimizer estimates(Although I couldn't find that page in the attachements to this JIRA entry). In addition, on each of the following 3 pages, Statistics timing, Statement execution plan, Optimizer estimates, can we add Optimizer Overrides in Related concepts?
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855.diff, derby855_html_files.zip
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-855) Document optimizer overrides which were introduced in 10.2
Posted by Eric Radzinski <er...@yahoo.com>.
Thanks Mamta,
You are correct. I'm still waiting to hear from Dan about my proposed resolutions to his questions.
Dan, can I proceed with implementing my proposed resolutions to your questions? They're documented as a commented directly within derby-855.
"Mamta A. Satoor (JIRA)" <de...@db.apache.org> wrote:
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364658 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Eric, Thanks for taking care of my comments. The changes look good. Dan had couple comments too, which I don't think are not covered in your most recent changes, but maybe you are still working on it.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364658 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Eric, Thanks for taking care of my comments. The changes look good. Dan had couple comments too, which I don't think are not covered in your most recent changes, but maybe you are still working on it.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12365762 ]
Eric Radzinski commented on DERBY-855:
--------------------------------------
How's this (only the second-to-last sentence is new):
joinStrategy
Use the joinStrategy property to override the optimizer's choice of join strategy. The two types of join strategy are called nested loop and hash.
In a nested loop join strategy, for each qualifying row in the outer table, Derby uses the appropriate access path (index or table scan) to find the matching rows in the inner table. In a hash join strategy, Derby constructs a hash table that represents the inner table. For each qualifying row in the outer table, Derby does a quick lookup on the hash table to find the matching rows in the inner table. Derby needs to scan the inner table or index only once to create the hash table. The -DERBY-PROPERTIES parameter must immediately follow the inner table.
Typically, you will use the joinStrategy property only in conjunction with the joinOrder property. Specifying a join strategy without knowing the join order can yield less-than-optimal results.
Valid values include HASH and NESTEDLOOP. The joinStrategy property can be used only within a TableExpression.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855-2.diff, derby855.diff, derby855_html_files.zip, tuning_guide.zip
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364849 ]
Eric Radzinski commented on DERBY-855:
--------------------------------------
There's three open issues on derby-855. I've summarized them here:
--- Does this text adequately address your concern about the description of the constraint property:
constraint
To force the use of the index that enforces a primary key, a foreign key, or unique constraint, use the constraint property and specify the
unqualified name of the constraint. The constraint property can be used only within a TableExpression, and it
can be specified only on base tables; it cannot be specified on views or derived tables.
--- I haven't seen a response about my proposal to modify the syntax to include the properties in the syntax. Here's what I proposed on 1/27:
The syntax for FROM clause properties is:
FROM [ -- DERBY-PROPERTIES joinOrder = FIXED | UNFIXED ]
TableExpression [,TableExpression]*
The syntax for table optimizer override properties, which must be included at the end of a TableExpression, is:
{TableName | ViewName }
[ [ AS ] CorrelationName
[ (SimpleColumnName [ , SimpleColumnName]* ) ] ]
[ -- DERBY-PROPERTIES constraint=<constraint-name> | index=<index-name> | joinStrategy = NESTEDLOOP | HASH ]
--- Regarding the comment about runstats output, is there a doc to-do in this comment? or does this apply only to the output when a foreign key is specified? If there is a doc to-do, can you provide me some details about what it should include?
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: ctunoptimzoverride.html
I think the most recent ctunoptimzoverride.htm addresses your first two issues (adding a pointer to the runtimestats topic and removing the space between nested and loop). The third issue is a simple change that I'll make as soon as I add these two new topics to the ditamap for the tuning guide.
Please let me know if you think these topics are ready to commit.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski reassigned DERBY-855:
------------------------------------
Assign To: Eric Radzinski
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: derby855-3.diff
ctundepthoptover.html
ctunoptimzoverride.html
derby855-3.diff updates the description of the joinStrategy property and updates the example for the joinStrategy property. The HTML files are included for review.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, derby855-2.diff, derby855-3.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12365767 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Eric, how about rather than "less-than-optimal results", we say "less-than-optimal performance"? The results word many be mistaken with the query result. If you/anyone else has better wording, please suggest. Also, in order to have our example match what we recommend, for joinStrategy example, we should use joinOrder in it. As shown below
SELECT * FROM --DERBY-PROPERTIES joinOrder=FIXED
t1 a, t1 b --DERBY-PROPERTIES joinStrategy=nestedloop
WHERE a.c1=b.c1 ;
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855-2.diff, derby855.diff, derby855_html_files.zip, tuning_guide.zip
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: (was: ctundepthoptover.html)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: derby855-2.diff, derby855-3.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364262 ]
Daniel John Debrunner commented on DERBY-855:
---------------------------------------------
This section is confusing to me:
constraint
The Derby optimizer chooses an index, including the indexes that enforce constraints, as the access path for query execution if the index is useful. If there is more than one useful index, in most cases Derby chooses the index that is most useful.
I would expect the sentence after constraint to describe what this override does, instead (I think) you are describing the default behaviour of the optimizer. Scanning quickly through the docs I might assume that using something like:
-- DERBY-PROPERTIES constraint=true
tells the optimizer to pick the best constraint for the query.
Also I don't know if we want something like the synax for the properties in this list, e.g.
constraint=<constraint name>
...
index=<index name>
...
joinOrder={FIXED|UNFIXED}
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: (was: ctunoptimzoverride.html)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: derby855-2.diff, derby855-3.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12365685 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Eric, I hope this is the last piece of information I will have to request you to change. After getting input from Jeff Lichtman and Satheesh Bandaram on joinStrategy, we should change "The joinStrategy property can be used only within a TableExpression, but it must be used in conjunction with the joinOrder property. Do not let the optimizer choose the join order." so that using joinOrder along with joinStrategy is not an absolute rule but is recommended. Let me copy what Jeff said on this issue "I think this should be stated as a piece of advice, not as an absolute rule. Generally, specifying a join strategy doesn't make much sense unless you already know the join order. That is, the same join strategy wouldn't necessarily be optimal for different join orders. But specifying the join strategy without specifying the join order won't prevent the query from running (except perhaps in some corner cases), nor will it cause the query to return the wrong results."
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855-2.diff, derby855.diff, derby855_html_files.zip, tuning_guide.zip
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: (was: ctunoptimzoverride.html)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: derby855-2.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: derby855.diff
derby855_html_files.zip
The attached patch addresses the optimizer overrides that were implemented with DERBY-573. The HTML files are included for review. I also included the HTML TOC file so that you can see where the new files were incorporated into the ditamap.
"Optimizer overrides" was added as the last subtopic under "Working with RunTimeStatistics" -> "Analyzing the information"
"Overriding the default optimizer behavior" was added as the last subtopic under "DML statements and performance"
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855.diff, derby855_html_files.zip
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364700 ]
Daniel John Debrunner commented on DERBY-855:
---------------------------------------------
More on the constraint, is the override limited to indexes backing unique and primary key constaints, or can indexes backing foreign key constraints also be used? I guess this is really a question against the functional spec.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12366851 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Eric, the latest changes look good to me.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, derby855-2.diff, derby855-3.diff, derby855-4.diff, derby855-5.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Jean T. Anderson (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Jean T. Anderson closed DERBY-855:
----------------------------------
no problems with patch applied reported, so closing.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, derby855-2.diff, derby855-3.diff, derby855-4.diff, derby855-5.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364181 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Following are examples of the 4 optimizer overrides
1)constraint
CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY (c1, c2))
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
SELECT * FROM t1 --DERBY-PROPERTIES constraint=cons1
FOR UPDATE
2)index
CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY (c1, c2))
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
CREATE INDEX t1_c1 ON t1(c1)
SELECT * FROM t1 --DERBY-PROPERTIES index=t1_c1
WHERE c1=1
3)joinOrder
CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY (c1, c2));
CREATE TABLE t2 (c1 int not null, c2 int not null, c3 int, CONSTRAINT cons2 UNIQUE(c1, c2))
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
INSERT INTO t2 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
SELECT * FROM --DERBY-PROPERTIES joinOrder=FIXED
t1, t2
WHERE t1.c1=t2.c1
4)joinStrategy
CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY (c1, c2));
CREATE TABLE t2 (c1 int not null, c2 int not null, c3 int, CONSTRAINT cons2 UNIQUE(c1, c2))
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
INSERT INTO t2 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
SELECT * FROM t1 a, t1 b --DERBY-PROPERTIES joinStrategy=nestedloop
WHERE a.c1=b.c1
As for the runtime statistics information, a separate topic in Tuning Guide's "Working with RunTimeStatistics" section sounds good. Along with that, we should have a pointer in your "Overriding the default optimizer behavior" to goto new subtopic in Tuning Guide's "Working with RunTimeStatistics" so the users know about it.
Your understanding of the four properties usage is correct ie joinOrder can be used with a FROM clause; index, constraint, and joinStrategy can be used only within a TableExpression.
Hope this helps. Anymore questions, feel free to post.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: derby855-2.diff
tuning_guide.zip
Mamta, the newest patch (derby855-2.diff) addresses your comments about the errant semicolons and about the line breaks in the example syntax.
All of the related linking is controlled by the ditamap, so I don't need to explicitly add those links. We get them for free. I've attached the HTML version of the entire Tuning Guide so that you can see all of the links that were generated. Unzip the tuning_guide.zip file, open the toc.html file, and then take a look at the new topics and the topics that relate to them.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855-2.diff, derby855.diff, derby855_html_files.zip, tuning_guide.zip
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: derby855-5.diff
ctundepthoptover.html
ctunoptimzoverride.html
derby855-5.diff addresses the line break error in the joinOrder example. HTML files are included for review.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, derby855-2.diff, derby855-3.diff, derby855-4.diff, derby855-5.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364115 ]
Eric Radzinski commented on DERBY-855:
--------------------------------------
I have a few outstanding questions about documenting this issue:
--I need some help extracting relevant examples from the source that you provided, which is located in: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out. Which specific ones would be best to use?
--Satheesh mentioned that we need to let the users know that they can see their optimizer overrides in RUNSTAT output. I'm assuming that this update would go in the tuning guide as a separate topic in the "Working with RunTimeStatistics" section. Can you confirm this?
--I need to mention whether the four properties belong after FROM or after table. Can you confirm my understanding of where the four properties can be used: joinOrder can be used with a FROM clause; index, constraint, and joinStrategy can be used only within a TableExpression. Is that correct?
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364747 ]
Daniel John Debrunner commented on DERBY-855:
---------------------------------------------
Looks like some improvements could be made in the run time statistics output, Satheesh's query said
constraint=fk
and the override info was
index=SQL060131012414010
The next line did contain the foreign key name though.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: (was: tuning_guide.zip)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855-2.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12365521 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
2 comments :
1)The joinOrder and joinStrategy examples have ; at the end of the create table. We should remove those.
2)Also, in examples, I think it will look clean to have the entire create table on one line (ie, rather than
CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY
(c1, c2))
Have
CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY (c1, c2))
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855.diff, derby855_html_files.zip
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: (was: ctundepthoptover.html)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: derby855-2.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: (was: derby855_html_files.zip)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855-2.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: (was: ctunoptimzoverride.html)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: derby855-2.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Jean T. Anderson (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Jean T. Anderson resolved DERBY-855:
------------------------------------
Resolution: Fixed
Committed patch derby855-5.diff, revision 378768. Changes:
$ svn status
A src/tuning/ctunoptimzoverride.dita
A src/tuning/ctundepthoptover.dita
M src/tuning/tuningderby.ditamap
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, derby855-2.diff, derby855-3.diff, derby855-4.diff, derby855-5.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12365460 ]
Daniel John Debrunner commented on DERBY-855:
---------------------------------------------
For the questions in Eric's Feb 01 comment.
-- Text for consttaint looks fine to me.
-- For the syntax, I think you have to surround alternate options with braces,
joinOrder = { FIXED | UNFIXED }
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: (was: ctunoptimzoverride.html)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: derby855-2.diff, derby855-3.diff, derby855-4.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: ctunoptimzoverride.html
ctundepthoptover.html
I think I've addressed all of the open issues.
ctunoptimzoverride.html is a new topic that's titled "Overriding the default optimizer behaviour." It'll go in the "Performance and optimization" section of the Tuning Guide.
ctundepthoptover is a new topic that's titled "Optimizer overrides." It'll go in the "Working with RunTimeStatistics" section of the Tuning Guide.
I'll add the appropriate related links after everybody's satisfied with the content of these two topics.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12365764 ]
Eric Radzinski commented on DERBY-855:
--------------------------------------
sorry...I meant only the second-to-last paragraph is new.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html, derby855-2.diff, derby855.diff, derby855_html_files.zip, tuning_guide.zip
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364698 ]
Daniel John Debrunner commented on DERBY-855:
---------------------------------------------
The suggestion for constraint was
constraint
Use the constraint property to override the index that the optimizer selects and force the use of a particular index or force a table scan.
To force the use of the index that enforces a primary key or unique constraint, use the constraint property and specify the
unqualified name of the constraint.
I think the first sentence is not required. Some of it is duplicated in the second sentence, and the comment about 'force a table scan is wrong'.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: (was: ctundepthoptover.html)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: derby855-2.diff, derby855-3.diff, derby855-4.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364723 ]
Satheesh Bandaram commented on DERBY-855:
-----------------------------------------
I think the contraint override can be used for foreign key constraints as well... I tried this:
create table prim ( i int not null primary key, j int);
create table sec (i int, constraint fk foreign key(i) references prim(i), j int);
select * from sec --derby-properties constraint=fk
;
...........................................
optimizer estimated cost: 136.34
User supplied optimizer overrides on SEC are { index=SQL060131012414010} <=========
Index Scan ResultSet for SEC using constraint FK at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-855) Document optimizer overrides which were
introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=all ]
Eric Radzinski updated DERBY-855:
---------------------------------
Attachment: derby855-4.diff
ctundepthoptover.html
ctunoptimzoverride.html
sorry about that. The editor that I was using was inserting an endash instead of two single dashes.
derby855-4.diff addresses the problem. New html files included for review.
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, derby855-2.diff, derby855-3.diff, derby855-4.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Eric Radzinski (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364265 ]
Eric Radzinski commented on DERBY-855:
--------------------------------------
Regarding your first point, do you suggest to remove the description about the default optimizer behaviour, and just include something like this:
constraint
Use the constraint property to override the index that the optimizer selects and force the use of a particular index or force a table scan.
To force the use of the index that enforces a primary key or unique constraint, use the constraint property and specify the
unqualified name of the constraint.
And regarding your second point, would updating the syntax at the top of the topic resolve this issue:
The syntax for FROM clause properties is:
FROM [ -- DERBY-PROPERTIES joinOrder = FIXED | UNFIXED ]
TableExpression [,TableExpression]*
The syntax for table optimizer override properties, which must be included at the end of a TableExpression, is:
{TableName | ViewName }
[ [ AS ] CorrelationName
[ (SimpleColumnName [ , SimpleColumnName]* ) ] ]
[ -- DERBY-PROPERTIES constraint=<constraint-name> | index=<index-name> | joinStrategy = NESTED LOOP | HASH ]
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12364857 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
Dan's comment about runstats output for constraint name vs index name is more of a code issue. I have entered JIRA entry for this (Derby-910)
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, ctunoptimzoverride.html
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-855) Document optimizer overrides which
were introduced in 10.2
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-855?page=comments#action_12366446 ]
Mamta A. Satoor commented on DERBY-855:
---------------------------------------
I reviewed the html files and found that the eg for joinOrder incorrectly has the table names on the same line as optimizer overrides
SELECT * FROM --DERBY-PROPERTIES joinOrder=FIXED t1, t2
WHERE t1.c1=t2.c1
The table names should not follow --DERBY-PROPERTIES joinOrder=FIXED, instead, they should be on the next line as shown below
SELECT * FROM --DERBY-PROPERTIES joinOrder=FIXED
t1, t2
WHERE t1.c1=t2.c1
> Document optimizer overrides which were introduced in 10.2
> ----------------------------------------------------------
>
> Key: DERBY-855
> URL: http://issues.apache.org/jira/browse/DERBY-855
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Eric Radzinski
> Fix For: 10.2.0.0
> Attachments: ctundepthoptover.html, ctunoptimzoverride.html, derby855-2.diff, derby855-3.diff, derby855-4.diff, derby855.diff
>
> Optimizer overrides support in Derby was added as jira entry DERBY-573. Eric Radzinski is working on the documentation part of the feature. This issue is to keep track of documentation changes.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira