Author: tolusha Date: 2011-09-14 10:35:27 -0400 (Wed, 14 Sep 2011) New Revision: 4913 Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2ConnectionFactory.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2MultiDbJDBCConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2SingleDbJDBCConnection.java jcr/trunk/exo.jcr.docs/exo.jcr.docs.developer/en/src/main/docbook/en-US/modules/jcr/query-handler-config.xml Log: EXOJCR-1507: Improve RDBMS reindexing for DB2 Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2ConnectionFactory.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2ConnectionFactory.java 2011-09-14 14:16:32 UTC (rev 4912) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2ConnectionFactory.java 2011-09-14 14:35:27 UTC (rev 4913) @@ -138,6 +138,6 @@ @Override public boolean isReindexingSupport() { - return false; + return true; } } Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2MultiDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2MultiDbJDBCConnection.java 2011-09-14 14:16:32 UTC (rev 4912) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2MultiDbJDBCConnection.java 2011-09-14 14:35:27 UTC (rev 4913) @@ -72,10 +72,9 @@ FIND_NODES_AND_PROPERTIES = "select J.*, P.ID AS P_ID, P.NAME AS P_NAME, P.VERSION AS P_VERSION, P.P_TYPE, P.P_MULTIVALUED," + " V.DATA, V.ORDER_NUM, V.STORAGE_DESC from JCR_MVALUE V, JCR_MITEM P" - + " join (select A.* from" - + " (select Row_Number() over (order by I.ID) as r__, I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_INDEX, I.N_ORDER_NUM" - + " from JCR_MITEM I where I.I_CLASS=1) as A where A.r__ <= ? and A.r__ > ?) J on P.PARENT_ID = J.ID" - + " where P.I_CLASS=2 and V.PROPERTY_ID=P.ID order by J.ID"; + + " join (select I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_INDEX, I.N_ORDER_NUM from JCR_MITEM I" + + " where I.I_CLASS=1 AND I.ID > ? order by I.ID LIMIT ?,?) J on P.PARENT_ID = J.ID" + + " where P.I_CLASS=2 and V.PROPERTY_ID=P.ID order by J.ID"; } /** @@ -93,8 +92,9 @@ findNodesAndProperties.clearParameters(); } - findNodesAndProperties.setInt(1, offset + limit); + findNodesAndProperties.setString(1, lastNodeId); findNodesAndProperties.setInt(2, offset); + findNodesAndProperties.setInt(3, limit); return findNodesAndProperties.executeQuery(); } Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2SingleDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2SingleDbJDBCConnection.java 2011-09-14 14:16:32 UTC (rev 4912) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DB2SingleDbJDBCConnection.java 2011-09-14 14:35:27 UTC (rev 4913) @@ -72,10 +72,8 @@ FIND_NODES_AND_PROPERTIES = "select J.*, P.ID AS P_ID, P.NAME AS P_NAME, P.VERSION AS P_VERSION, P.P_TYPE, P.P_MULTIVALUED," + " V.DATA, V.ORDER_NUM, V.STORAGE_DESC from JCR_SVALUE V, JCR_SITEM P" - + " join (select A.* from" - + " (select Row_Number() over (order by I.ID) as r__, I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_INDEX, I.N_ORDER_NUM" - + " from JCR_SITEM I where I.CONTAINER_NAME=? and I.I_CLASS=1) as A where A.r__ <= ? and A.r__ > ?" - + ") J on P.PARENT_ID = J.ID" + + " join (select I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_INDEX, I.N_ORDER_NUM from JCR_SITEM I" + + " where I.CONTAINER_NAME=? AND I.I_CLASS=1 AND I.ID > ? order by I.ID LIMIT ?,?) J on P.PARENT_ID = J.ID" + " where P.I_CLASS=2 and P.CONTAINER_NAME=? and V.PROPERTY_ID=P.ID order by J.ID"; } @@ -95,9 +93,10 @@ } findNodesAndProperties.setString(1, containerName); - findNodesAndProperties.setInt(2, offset + limit); + findNodesAndProperties.setString(2, lastNodeId); findNodesAndProperties.setInt(3, offset); - findNodesAndProperties.setString(4, containerName); + findNodesAndProperties.setInt(4, limit); + findNodesAndProperties.setString(5, containerName); return findNodesAndProperties.executeQuery(); } Modified: jcr/trunk/exo.jcr.docs/exo.jcr.docs.developer/en/src/main/docbook/en-US/modules/jcr/query-handler-config.xml =================================================================== --- jcr/trunk/exo.jcr.docs/exo.jcr.docs.developer/en/src/main/docbook/en-US/modules/jcr/query-handler-config.xml 2011-09-14 14:16:32 UTC (rev 4912) +++ jcr/trunk/exo.jcr.docs/exo.jcr.docs.developer/en/src/main/docbook/en-US/modules/jcr/query-handler-config.xml 2011-09-14 14:35:27 UTC (rev 4913) @@ -215,8 +215,16 @@ improved by setting the parameter "enable_seqscan" to "off" or "default_statistics_target" to at least "50" in the configuration of your database. Then you need to restart DB server and make analyze of - the JCR_SVALUE (JCR_MVALUE) table.</para> + the JCR_SVALUE (or JCR_MVALUE) table.</para> </note> + + <note> + <para>If you use DB2 and the parameter rdbms-reindexing is set to + true, the performance of the queiries used while indexing can be + improved by making statisticks on tables by running "RUNSTATS ON TABLE + &lt;scheme&gt;.&lt;table&gt; WITH DISTRIBUTION AND INDEXES ALL" for + JCR_SITEM (or JCR_MITEM) and JCR_SVALUE (or JCR_MVALUE) tables.</para> + </note> </section> <section>