Re: [jira] Updated: (DERBY-573) Provide support for optimizer overrides in Derby.

[Satheesh Bandaram]

I am reviewing this patch and will commit it if looks good. If there are any other comments, let us know.. I think this is a useful patch that shows if user specified optimizer hints are being used in RUNSTAT display. Satheesh Mamta A. Satoor (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-573?page=all ] Mamta A. Satoor updated DERBY-573: ---------------------------------- Attachment: Derby573OptimizerOverridesAndRunTimeStatistics011206.txt I have attached a patch named Derby573OptimizerOverridesAndRunTimeStatistics011206.txt to JIRA Derby573 Provide support for optimizer overrides in Derby. This patch enables users to see the optimizer overrides specified in the sql as part of runtime statistics info. This is achieved by changing the generator so that these properties get passed from compile time to execute time. This change in generate phase can be found in FromBaseTable, BaseJoinStrategy and JoinNode. The changes in the other classes is for returning the correct number of arguments to the scan. That change is in getScanArgs method. In addition, I have changed the existing lang/optimizerOverrides.sql to test this patch. derbyall suite has run fine on my Windows XP m/c with Sun's jdk14. svn stat M java\engine\org\apache\derby\impl\sql\compile\BaseJoinStrategy.java M java\engine\org\apache\derby\impl\sql\compile\NestedLoopJoinStrategy.java M java\engine\org\apache\derby\impl\sql\compile\SelectNode.java M java\engine\org\apache\derby\impl\sql\compile\JoinNode.java M java\engine\org\apache\derby\impl\sql\compile\HashJoinStrategy.java M java\engine\org\apache\derby\impl\sql\compile\FromBaseTable.java M java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj M java\engine\org\apache\derby\impl\sql\compile\HalfOuterJoinNode.java M java\engine\org\apache\derby\impl\sql\execute\HashLeftOuterJoinResultSet.java M java\engine\org\apache\derby\impl\sql\execute\HashJoinResultSet.java M java\engine\org\apache\derby\impl\sql\execute\LastIndexKeyResultSet.java M java\engine\org\apache\derby\impl\sql\execute\GenericResultSetFactory.java M java\engine\org\apache\derby\impl\sql\execute\DistinctScanResultSet.java M java\engine\org\apache\derby\impl\sql\execute\HashScanResultSet.java M java\engine\org\apache\derby\impl\sql\execute\InsertResultSet.java M java\engine\org\apache\derby\impl\sql\execute\TableScanResultSet.java M java\engine\org\apache\derby\impl\sql\execute\rts\RealNestedLoopLeftOuterJoinStatistics.java M java\engine\org\apache\derby\impl\sql\execute\rts\RealHashLeftOuterJoinStatistics.java M java\engine\org\apache\derby\impl\sql\execute\rts\RealHashJoinStatistics.java M java\engine\org\apache\derby\impl\sql\execute\rts\RealJoinResultSetStatistics.java M java\engine\org\apache\derby\impl\sql\execute\rts\RealTableScanStatistics.java M java\engine\org\apache\derby\impl\sql\execute\rts\RealNestedLoopJoinStatistics.java M java\engine\org\apache\derby\impl\sql\execute\RealResultSetStatisticsFactory.java M java\engine\org\apache\derby\impl\sql\execute\DependentResultSet.java M java\engine\org\apache\derby\impl\sql\execute\BulkTableScanResultSet.java M java\engine\org\apache\derby\impl\sql\execute\MergeJoinResultSet.java M java\engine\org\apache\derby\impl\sql\execute\NestedLoopJoinResultSet.java M java\engine\org\apache\derby\impl\sql\execute\NestedLoopLeftOuterJoinResultSet.java M java\engine\org\apache\derby\impl\sql\execute\JoinResultSet.java M java\engine\org\apache\derby\iapi\sql\compile\Optimizable.java M java\engine\org\apache\derby\iapi\sql\execute\ResultSetFactory.java M java\engine\org\apache\derby\iapi\reference\SQLState.java M java\engine\org\apache\derby\loc\messages_en.properties M java\testing\org\apache\derbyTesting\functionTests\tests\lang\optimizerOverrides.sql M java\testing\org\apache\derbyTesting\functionTests\master\DerbyNet\optimizerOverrides.out M java\testing\org\apache\derbyTesting\functionTests\master\DerbyNetClient\optimizerOverrides.out M java\testing\org\apache\derbyTesting\functionTests\master\optimizerOverrides.out Provide support for optimizer overrides in Derby. ------------------------------------------------- Key: DERBY-573 URL: http://issues.apache.org/jira/browse/DERBY-573 Project: Derby Type: New Feature Components: SQL Versions: 10.2.0.0 Reporter: Mamta A. Satoor Assignee: Mamta A. Satoor Attachments: Derby573OptimizerOverridesAndRunTimeStatistics011206.txt, derby572OptimizerOverridesAndUpgrade120605.txt, derby572OptimizerOverridesAndUpgrade121105.txt, derby572OptimizerOverridesAndUpgrade121205.txt, derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries.