Author: areshetnyak Date: 2011-08-11 10:05:08 -0400 (Thu, 11 Aug 2011) New Revision: 4744 Modified: jcr/trunk/exo.jcr.component.core/pom.xml jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java Log: EXOJCR-1478 : Query optimization for DB Oracle. Modified: jcr/trunk/exo.jcr.component.core/pom.xml =================================================================== --- jcr/trunk/exo.jcr.component.core/pom.xml 2011-08-11 13:22:33 UTC (rev 4743) +++ jcr/trunk/exo.jcr.component.core/pom.xml 2011-08-11 14:05:08 UTC (rev 4744) @@ -30,7 +30,7 @@ <name>eXo JCR :: Component :: Core Service</name> <description>eXo JCR Service core component</description> <properties> - <jcr.test.configuration.file>/conf/standalone/test-configuration-jbc.xml</jcr.test.configuration.file> + <jcr.test.configuration.file>/conf/standalone/test-configuration-sjdbc-jbc.xml</jcr.test.configuration.file> <jbosscache.shareable>true</jbosscache.shareable> <jbc.specific.1>**/jbc/**</jbc.specific.1> <jbc.specific.2>**/jbosscache/**</jbc.specific.2> @@ -261,7 +261,7 @@ <scope>test</scope> </dependency> <!-- For Oracle 10g support (local-jcr repository) --> - <!-- dependency> + <dependency> <groupId>ojdbc</groupId> <artifactId>ojdbc</artifactId> <version>14</version> @@ -272,7 +272,7 @@ <artifactId>orai18n</artifactId> <version>14</version> <scope>test</scope> - </dependency --> + </dependency> <!-- For IBM DB2 support (local-jcr repository) --> <!-- dependency> <groupId>com.ibm.db2</groupId> Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java 2011-08-11 13:22:33 UTC (rev 4743) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java 2011-08-11 14:05:08 UTC (rev 4744) @@ -63,7 +63,13 @@ "select I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_CLASS, I.I_INDEX, I.N_ORDER_NUM, I.P_TYPE, I.P_MULTIVALUED," + " V.ORDER_NUM, V.DATA, V.STORAGE_DESC from JCR_MITEM I LEFT OUTER JOIN JCR_MVALUE V ON (V.PROPERTY_ID=I.ID)" + " where I.I_CLASS=2 and I.PARENT_ID=? order by I.NAME"; + + protected static final String FIND_ITEM_QPATH_BY_ID_CQ_QUERY = + "select I.ID, I.PARENT_ID, I.NAME, I.I_INDEX" + + " from JCR_MITEM I, (SELECT ID, PARENT_ID from JCR_MITEM where ID=?) J" + + " where I.ID = J.ID or I.ID = J.PARENT_ID"; + protected String PATTERN_ESCAPE_STRING = "\\"; //valid for HSQL, Sybase, DB2, MSSQL, ORACLE /** @@ -152,10 +158,7 @@ + " from JCR_MITEM I, JCR_MVALUE V" + " where I.I_CLASS=2 and I.PARENT_ID=? and (I.NAME='[http://www.jcp.org/jcr/1.0]primaryType' or I.NAME='[http://www.jcp.org/jcr/1.0]mixinTypes' or I.NAME='[http://www.exoplatform.com/jcr/exo/1.0]owner' or I.NAME='[http://www.exoplatform.com/jcr/exo/1.0]permissions') and I.ID=V.PROPERTY_ID"; - FIND_ITEM_QPATH_BY_ID_CQ = - "select I.ID, I.PARENT_ID, I.NAME, I.I_INDEX" - + " from JCR_MITEM I, (SELECT ID, PARENT_ID from JCR_MITEM where ID=?) J" - + " where I.ID = J.ID or I.ID = J.PARENT_ID"; + FIND_ITEM_QPATH_BY_ID_CQ = FIND_ITEM_QPATH_BY_ID_CQ_QUERY; FIND_LAST_ORDER_NUMBER_BY_PARENTID = "select count(*), max(N_ORDER_NUM) from JCR_MITEM where I_CLASS=1 and PARENT_ID=?"; Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java 2011-08-11 13:22:33 UTC (rev 4743) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java 2011-08-11 14:05:08 UTC (rev 4744) @@ -41,7 +41,12 @@ protected static final String FIND_PROPERTIES_BY_PARENTID_CQ_QUERY = MultiDbJDBCConnection.FIND_PROPERTIES_BY_PARENTID_CQ_QUERY.replaceFirst("select", "select /*+ INDEX(I JCR_IDX_MITEM_PARENT_ID) INDEX(V JCR_IDX_MVALUE_PROPERTY)*/"); + + protected static final String FIND_ITEM_QPATH_BY_ID_CQ_QUERY = + MultiDbJDBCConnection.FIND_ITEM_QPATH_BY_ID_CQ_QUERY.replaceFirst("SELECT", + "SELECT /*+ INDEX(JCR_MITEM JCR_PK_MITEM) */"); + /** * Oracle Multidatabase JDBC Connection constructor. * @@ -80,6 +85,7 @@ super.prepareQueries(); FIND_NODES_BY_PARENTID_CQ = FIND_NODES_BY_PARENTID_CQ_QUERY; FIND_PROPERTIES_BY_PARENTID_CQ = FIND_PROPERTIES_BY_PARENTID_CQ_QUERY; + FIND_ITEM_QPATH_BY_ID_CQ = FIND_ITEM_QPATH_BY_ID_CQ_QUERY; FIND_PROPERTIES_BY_PARENTID_AND_PATTERN_CQ_TEMPLATE = "select /*+ INDEX(I JCR_IDX_MITEM_PARENT_ID) INDEX(V JCR_IDX_MVALUE_PROPERTY)*/ I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_CLASS, I.I_INDEX, I.N_ORDER_NUM, I.P_TYPE, I.P_MULTIVALUED," + " V.ORDER_NUM, V.DATA, V.STORAGE_DESC from JCR_MITEM I LEFT OUTER JOIN JCR_MVALUE V ON (V.PROPERTY_ID=I.ID)"; Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java 2011-08-11 13:22:33 UTC (rev 4743) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java 2011-08-11 14:05:08 UTC (rev 4744) @@ -42,6 +42,10 @@ SingleDbJDBCConnection.FIND_PROPERTIES_BY_PARENTID_CQ_QUERY.replaceFirst("select", "select /*+ INDEX(I JCR_IDX_SITEM_PARENT_ID) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/"); + protected static final String FIND_ITEM_QPATH_BY_ID_CQ_QUERY = + SingleDbJDBCConnection.FIND_ITEM_QPATH_BY_ID_CQ_QUERY.replaceFirst("SELECT", + "SELECT /*+ INDEX(JCR_SITEM JCR_PK_SITEM) */"); + /** * Oracle Singledatabase JDBC Connection constructor. * @@ -81,6 +85,7 @@ super.prepareQueries(); FIND_NODES_BY_PARENTID_CQ = FIND_NODES_BY_PARENTID_CQ_QUERY; FIND_PROPERTIES_BY_PARENTID_CQ = FIND_PROPERTIES_BY_PARENTID_CQ_QUERY; + FIND_ITEM_QPATH_BY_ID_CQ = FIND_ITEM_QPATH_BY_ID_CQ_QUERY; FIND_PROPERTIES_BY_PARENTID_AND_PATTERN_CQ_TEMPLATE = "select /*+ INDEX(I JCR_IDX_SITEM_PARENT_ID) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/ I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_CLASS, I.I_INDEX, I.N_ORDER_NUM, I.P_TYPE, I.P_MULTIVALUED, V.ORDER_NUM," + " V.DATA, V.STORAGE_DESC from JCR_SITEM I LEFT OUTER JOIN JCR_SVALUE V ON (V.PROPERTY_ID=I.ID)"; Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java 2011-08-11 13:22:33 UTC (rev 4743) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java 2011-08-11 14:05:08 UTC (rev 4744) @@ -61,6 +61,11 @@ "select I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_CLASS, I.I_INDEX, I.N_ORDER_NUM, I.P_TYPE, I.P_MULTIVALUED, V.ORDER_NUM," + " V.DATA, V.STORAGE_DESC from JCR_SITEM I LEFT OUTER JOIN JCR_SVALUE V ON (V.PROPERTY_ID=I.ID)" + " where I.I_CLASS=2 and I.CONTAINER_NAME=? and I.PARENT_ID=? order by I.NAME"; + + protected static final String FIND_ITEM_QPATH_BY_ID_CQ_QUERY = + "select I.ID, I.PARENT_ID, I.NAME, I.I_INDEX" + + " from JCR_SITEM I, (SELECT ID, PARENT_ID from JCR_SITEM where ID=?) J" + + " where I.ID = J.ID or I.ID = J.PARENT_ID"; protected static final String PATTERN_ESCAPE_STRING = "\\"; //valid for HSQL, Sybase, DB2, MSSQL, ORACLE @@ -158,10 +163,7 @@ + " from JCR_SITEM I, JCR_SVALUE V" + " where I.I_CLASS=2 and I.CONTAINER_NAME=? and I.PARENT_ID=? and (I.NAME='[http://www.jcp.org/jcr/1.0]primaryType' or I.NAME='[http://www.jcp.org/jcr/1.0]mixinTypes' or I.NAME='[http://www.exoplatform.com/jcr/exo/1.0]owner' or I.NAME='[http://www.exoplatform.com/jcr/exo/1.0]permissions') and I.ID=V.PROPERTY_ID"; - FIND_ITEM_QPATH_BY_ID_CQ = - "select I.ID, I.PARENT_ID, I.NAME, I.I_INDEX" - + " from JCR_SITEM I, (SELECT ID, PARENT_ID from JCR_SITEM where ID=?) J" - + " where I.ID = J.ID or I.ID = J.PARENT_ID"; + FIND_ITEM_QPATH_BY_ID_CQ = FIND_ITEM_QPATH_BY_ID_CQ_QUERY; FIND_LAST_ORDER_NUMBER_BY_PARENTID = "select count(*), max(N_ORDER_NUM) from JCR_SITEM where I_CLASS=1 and CONTAINER_NAME=? and PARENT_ID=?";