[
https://issues.apache.org/jira/browse/DERBY-269?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-269:
----------------------------------
Attachment: DERBY269_update_statistics_svnstat_ver1.txt
DERBY269_update_statistics_svndiff_ver1.txt
I have a patch for implementing update statistics as a stored procedure. The
main logic is to convert the stored procedure request to an ALTER TABLE...
sql(bear in mind that this generated sql is not available for the user to issue
directly. This alter table syntax will be accepted only when sql is not coming
directly from the user. This is similar in concept to what we do for compress
table) and go through the existing ALTER TABLE code to do table/schema/index
validation, ensuring proper permissions exist for user to call update
statistics etc. The actual code to update the statistics has been (literally)
copied from the existing disabled code for update statistics in Derby
(impl.sql.execute.UpdateStatisticsConstantAction.java) The user can invoke
update statistics for just one index on a table or for all indexes on a table.
The syntax of the new stored procedure is as follows
call syscs_util.SYSCS_UPDATE_STATISTICS(schemaname, tablename, indexname)
If the user specifies null for the indexname, then all the indexes on the
tablename will have their statistics updated. If the user does specify an
indexname, then statistics will be updated only for that index.
The patch here include engine changes and upgrade changes. It also fixes
existing tests that were impacted by the engine changes. TODO : I need to add
more tests for the actual functionality. The existing disabled test
selectivity.sql appears to have some tests for old disabled (non-compliant)
update statistics syntax. I will use these existing tests as the basis for the
new junit tests for the new stored procedure.
The files impacted by this patch are as follows. I will briefly go over the
actual code changes in the following paragraph
$ svn stat -q
M java\engine\org\apache\derby\impl\sql\compile\AlterTableNode.java
M java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
M
java\engine\org\apache\derby\impl\sql\execute\GenericConstantActionFactory.java
M
java\engine\org\apache\derby\impl\sql\execute\AlterTableConstantAction.java
M java\engine\org\apache\derby\impl\sql\catalog\DataDictionaryImpl.java
M java\engine\org\apache\derby\impl\sql\catalog\DD_Version.java
M java\engine\org\apache\derby\iapi\sql\dictionary\IndexRowGenerator.java
M java\engine\org\apache\derby\iapi\sql\dictionary\DataDictionary.java
M java\engine\org\apache\derby\catalog\SystemProcedures.java
M
java\testing\org\apache\derbyTesting\functionTests\tests\lang\RolesTest.java
M
java\testing\org\apache\derbyTesting\functionTests\tests\lang\GrantRevokeDDLTest.java
M
java\testing\org\apache\derbyTesting\functionTests\tests\upgradeTests\_Suite.java
M
java\testing\org\apache\derbyTesting\functionTests\tests\upgradeTests\Changes10_2.java
M
java\testing\org\apache\derbyTesting\functionTests\tests\upgradeTests\UpgradeRun.java
A
java\testing\org\apache\derbyTesting\functionTests\tests\upgradeTests\Changes10_5.java
M
java\testing\org\apache\derbyTesting\functionTests\master\db2Compatibility.out
Parser, compile and execute time changes for the new stored procedure For the
stored procedure, we generate one of the following 2 ALTER TABLE ... sqls This
sql is generated if user wants us to update the statistics of all the indexes
on a table
ALTER TABLE (schemaname.)tablename ALL UPDATE STATISTICS
or
This sql is generated if user wants us to update the statistics of only one
index on a table
ALTER TABLE (schemaname.)tablename UPDATE STATISTICS indexname
1)sqlgrammar.jj
We will first make sure that we are at data dictionary version 10.5 or higher
in order to accept the new ALTER TABLE sql syntax. Next, we will make sure that
this ALTER TABLE was generated through internal code and not from the user
code. One these 2 criterias are made, we will generated the compile time node
for ALTER TABLE sql.
2)AlterTableNode.java
The changes in this class are simple and simply make sure that we are ready to
accept the new ALTER TABLE... syntax
3)GenericConstantActionFactory.java
Since we have more parameters to pass from compile phase to execute phase for
ALTER TABLE now, I needed to make changes into
GenericConstantActionFactory.java from those additional params.
4)AlterTableConstantAction.java
The changes in this file are copied from the existing disabled code for update
statistics in impl.sql.execute.UpdateStatisticsConstantAction.java. This
existing code relies on a method in IndexRowGenerator.java which I added back
again (this method was removed by Dan a long time back because it was not being
used by any active code in Derby. The method was removed as part of revision
565966).
5)SystemProcedures.java
This is where we intercept the call to SYSCS_UPDATE_STATISTICS and convert it
into internal syntax of ALTER TABLE... sql
The following changes in engine code are related to upgrade code
1)DataDictionaryImpl.java
It marks the database to be at version 10.5 and it adds the new system stored
procedure SYSCS_UPDATE_STATISTICS to the data dictionary for 10.5 release.
2)DD_Version.java
This provides a way to add the stored procedure SYSCS_UPDATE_STATISTICS to the
data dictionary during hard upgrade to 10.5 release of an existing db.
Following existing tests needed some modifications
1)RolesTest.java, GrantRevokeDDLTest.java, db2Compatibility.out
The number of stored procedures have increased by one and hence these tests
have to take that into account.
Following tests changes are for upgrade testing(added a new test for 10.5
release and the new test is called Changes10_5.java
1)upgradeTests/_Suite.java, Changes10_2.java, UpgradeRun.java
I will appreciate any feedback on the patch.
> Provide some way to update index cardinality statistics (e.g. reimplement
> update statistics)
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-269
> URL: https://issues.apache.org/jira/browse/DERBY-269
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.2.0, 10.3.1.4
> Reporter: Stan Bradbury
> Assignee: Mamta A. Satoor
> Attachments: DERBY269_update_statistics_svndiff_ver1.txt,
> DERBY269_update_statistics_svnstat_ver1.txt
>
>
> Performance problems are being reported that can be resolved by updating the
> cardinality statistics used by the optimizer. Currently the only time the
> statistics are guaranteed to be an up-to-date is when the index is first
> created on a fully populated table. This is most easily accomplished on an
> existing table by using the command:
> alter table <table-name> compress [sequential]
> Compress table is an I/O intensive task. A better way to achieve this would
> be to re-enable parser support for the 'update statistics' command or
> re-implement the update in some other fashion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
