Author: nzamosenchuk Date: 2011-11-24 09:17:18 -0500 (Thu, 24 Nov 2011) New Revision: 5238 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/OracleSingleDbJDBCConnection.java Log: EXOJCR-1653 : Added indexes shows upto 12% query performance increase. Queries were executed in 52ms (avg) previously and in 46ms now. 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-11-24 10:55:47 UTC (rev 5237) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java 2011-11-24 14:17:18 UTC (rev 5238) @@ -97,6 +97,8 @@ + " 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 order by I.ID) A where ROWNUM <= ?) where r__ > ?) J on P.PARENT_ID = J.ID" + " where P.I_CLASS=2 and V.PROPERTY_ID=P.ID order by J.ID"; + + FIND_NODES_BY_PARENTID_LAZILY_CQ = FIND_NODES_BY_PARENTID_LAZILY_CQ.replaceFirst("select", "select /*+ INDEX(I JCR_FK_SITEM_PARENT) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/"); } /** 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-11-24 10:55:47 UTC (rev 5237) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java 2011-11-24 14:17:18 UTC (rev 5238) @@ -98,6 +98,8 @@ + " where I.CONTAINER_NAME=? and I.I_CLASS=1 order by I.ID" + " ) A where ROWNUM <= ?) where r__ > ?) 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"; + + FIND_NODES_BY_PARENTID_LAZILY_CQ = FIND_NODES_BY_PARENTID_LAZILY_CQ.replaceFirst("select", "select /*+ INDEX(I JCR_FK_SITEM_PARENT) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/"); } /**