[ 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
