Author: shawkins Date: 2010-08-27 15:00:01 -0400 (Fri, 27 Aug 2010) New Revision: 2497 Added: branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java Removed: branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexCondition.java Modified: branches/7.1.x/client/src/main/java/org/teiid/client/plan/Annotation.java branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/matviews.xml branches/7.1.x/engine/src/main/java/org/teiid/common/buffer/STree.java branches/7.1.x/engine/src/main/java/org/teiid/common/buffer/TupleBrowser.java branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TempMetadataID.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTable.java branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestMaterialization.java branches/7.1.x/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java Log: TEIID-1210 adding support for secondary indexes for materialized views Modified: branches/7.1.x/client/src/main/java/org/teiid/client/plan/Annotation.java =================================================================== --- branches/7.1.x/client/src/main/java/org/teiid/client/plan/Annotation.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/client/src/main/java/org/teiid/client/plan/Annotation.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -33,6 +33,7 @@ public class Annotation implements Externalizable { public static final String MATERIALIZED_VIEW = "Materialized View"; //$NON-NLS-1$ + public static final String CACHED_PROCEDURE = "Cached Procedure"; //$NON-NLS-1$ public static final String HINTS = "Hints"; //$NON-NLS-1$ public enum Priority { Modified: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/matviews.xml =================================================================== --- branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/matviews.xml 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/matviews.xml 2010-08-27 19:00:01 UTC (rev 2497) @@ -46,14 +46,22 @@ <note><para>It is important to ensure that all key/index information is present as these will be used by the materialization process to enhance the performance of the materialized table.</para></note> <para>The target materialized table may also be set in the properties. If the value is left blank, the default, then internal materialization will be used. Otherwise for external materialization, the value should reference the fully qualified name of a table (or possibly view) with the same columns as the materialized view. - For most basic scenarios the simplicity of internal materialization makes it the more appealing option. Other considerations for chosing between internal and external materialization are: + For most basic scenarios the simplicity of internal materialization makes it the more appealing option. <itemizedlist> - <listitem><para>Does the cached data need to be fully durable? If yes, then external materialization should be used. + <title>Reasons to use external materialization</title> + <listitem><para>The cached data needs to be fully durable. Internal materialization should not survive a cluster restart.</para> </listitem> - <listitem><para>Is full control needed of loading and refresh? If yes, then external materialzation should be used. + <listitem><para>Full control is needed of loading and refresh. Internal materialization does offer several system supported methods for refreshing, but does not give full access to the materialized table.</para> </listitem> + <listitem><para>Control is needed over the materialized table definition. + Internal materialization does support <link linkend="internal-index">secondary indexes</link>, but they cannot be directly controlled. + Constraints or other database features cannot be added to internal materialization tables.</para> + </listitem> + <listitem><para>The data volume is large. Internal materialization (and temp tables in general) have memory overhead for each page. + A rough guideline is that no more than 100 million rows should in all materializated tables across all VDBs for every 1 gigabyte of heap.</para> + </listitem> </itemizedlist> </para> <note><para>Materialized view tables are always scoped to the VDB. If a materialized view definition directly or transitively contains a non-deterministic @@ -83,7 +91,7 @@ <listitem> <para>Determine a load and refresh strategy. With the schema created the most simplistic approach is to just load the data. The load can even be done through Teiid with <code>insert into target_table select * from matview option nocache</code>. - That however may be too simplistic because you index creation may be more performant if deferred until after the table has been created. + That however may be too simplistic because your index creation may be more performant if deferred until after the table has been created. Also full snapshot refreshes are best done to a staging table then swapping it for the existing physical table to ensure that the refresh does not impact user queries and to ensure that the table is valid prior to use.</para> </listitem> @@ -94,7 +102,8 @@ <para>Internal materialization creates Teiid temporary tables to hold the materialized table. While these tables are not fully durable, they perform well in most circumstances and the data is present at each Teiid instance which removes the single point of failure and network overhead of an external database. Internal materialization also provides more built-in facilities for refreshing and monitoring.</para> - <para>The cache hint, when used in the context of an internal materialized view transformation query, provides the ability to fine tune the materializated table. The pref_mem option also applies to internal materialized views. Internal table index pages already have a memory preference, so the perf_mem option indicates that the data pages should prefer memory as well.</para> + <para>The cache hint, when used in the context of an internal materialized view transformation query, provides the ability to fine tune the materializated table. + The pref_mem option also applies to internal materialized views. Internal table index pages already have a memory preference, so the perf_mem option indicates that the data pages should prefer memory as well.</para> <section> <title>Loading And Refreshing</title> <para>An internal materialized view table is initially in an invalid state (there is no data). The first user query will trigger an implicit loading of the data. @@ -154,10 +163,8 @@ </section> </section> <section> - <title>Limitations</title> - <itemizedlist> - <listitem><para>Secondary index information is currently not used. An index is only created for the primary key.</para></listitem> - </itemizedlist> + <title>Secondary Indexes</title> + <para>Function based indexes, Covering indexes, converting unique to index</para> </section> </section> </chapter> Modified: branches/7.1.x/engine/src/main/java/org/teiid/common/buffer/STree.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/common/buffer/STree.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/main/java/org/teiid/common/buffer/STree.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -392,4 +392,8 @@ this.preferMemory = preferMemory; } + public boolean isPreferMemory() { + return preferMemory; + } + } \ No newline at end of file Modified: branches/7.1.x/engine/src/main/java/org/teiid/common/buffer/TupleBrowser.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/common/buffer/TupleBrowser.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/main/java/org/teiid/common/buffer/TupleBrowser.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -33,12 +33,14 @@ /** * Implements intelligent browsing over a {@link STree} + * + * TODO: this is not as efficient as it should be over partial matches */ public class TupleBrowser implements TupleSource { private final STree tree; - private List<List<Object>> valueSet; + private TupleSource valueSet; private SPage page; private int index; @@ -53,12 +55,13 @@ private boolean inPartial; /** - * Construct a value based browser + * Construct a value based browser. The {@link TupleSource} should already be in the + * proper direction. * @param sTree * @param valueSet * @param direction */ - public TupleBrowser(STree sTree, List<List<Object>> valueSet, boolean direction) { + public TupleBrowser(STree sTree, TupleSource valueSet, boolean direction) { this.tree = sTree; this.direction = direction; this.valueSet = valueSet; @@ -80,7 +83,7 @@ } private void init(List<Object> lowerBound, - List<Object> upperBound) + List<?> upperBound) throws TeiidComponentException { if (lowerBound != null) { lowerBound.addAll(Collections.nCopies(tree.getKeyLength() - lowerBound.size(), null)); @@ -156,11 +159,11 @@ for (;;) { //first check for value iteration if (!inPartial && valueSet != null) { - if (valueSet.isEmpty()) { + List<?> newValue = valueSet.nextTuple(); + if (newValue == null) { resetState(); return null; } - List<Object> newValue = direction?valueSet.remove(0):valueSet.remove(valueSet.size() -1); if (newValue.size() < tree.getKeyLength()) { init(new ArrayList<Object>(newValue), newValue); inPartial = true; Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -23,9 +23,9 @@ package org.teiid.query.metadata; import java.util.ArrayList; -import java.util.Arrays; import java.util.Collection; import java.util.Collections; +import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Properties; @@ -363,7 +363,11 @@ public Collection getIndexesInGroup(Object groupID) throws TeiidComponentException, QueryMetadataException { if(groupID instanceof TempMetadataID) { - return Collections.EMPTY_LIST; + List<List<TempMetadataID>> result = ((TempMetadataID)groupID).getIndexes(); + if (result == null) { + return Collections.emptyList(); + } + return result; } return this.actualMetadata.getIndexesInGroup(groupID); } @@ -372,11 +376,15 @@ throws TeiidComponentException, QueryMetadataException { if(groupID instanceof TempMetadataID) { + LinkedList<List<TempMetadataID>> result = new LinkedList<List<TempMetadataID>>(); TempMetadataID id = (TempMetadataID)groupID; - if (id.getPrimaryKey() == null) { - return Collections.emptyList(); + if (id.getPrimaryKey() != null) { + result.add(id.getPrimaryKey()); } - return Arrays.asList(groupID); + if (id.getUniqueKeys() != null) { + result.addAll(id.getUniqueKeys()); + } + return result; } return this.actualMetadata.getUniqueKeysInGroup(groupID); } @@ -401,8 +409,8 @@ public List getElementIDsInKey(Object keyID) throws TeiidComponentException, QueryMetadataException { - if (keyID instanceof TempMetadataID) { - return ((TempMetadataID)keyID).getPrimaryKey(); + if (keyID instanceof List) { + return (List)keyID; } return this.actualMetadata.getElementIDsInKey(keyID); @@ -692,10 +700,7 @@ @Override public Object getPrimaryKey(Object metadataID) { if (metadataID instanceof TempMetadataID) { - if (((TempMetadataID)metadataID).getPrimaryKey() != null) { - return metadataID; - } - return null; + return ((TempMetadataID)metadataID).getPrimaryKey(); } return this.actualMetadata.getPrimaryKey(metadataID); } Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TempMetadataID.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TempMetadataID.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TempMetadataID.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -25,6 +25,7 @@ import java.io.Serializable; import java.util.Collection; import java.util.Collections; +import java.util.LinkedList; import java.util.List; import org.teiid.core.util.LRUCache; @@ -37,11 +38,28 @@ * does not exist in a real metadata source. Rather, it is used temporarily * in context of processing a single query. This metadata ID can be used to * represent either a group or an element depending on the constructor used. + * + * TODO: we should be using the real metadata objects, but internal and + * designer legacy keep us on the temp framework */ public class TempMetadataID implements Serializable { private static final int LOCAL_CACHE_SIZE = 8; + static class TableData { + Collection<TempMetadataID> accessPatterns; + List<TempMetadataID> elements; + int cardinality = QueryMetadataInterface.UNKNOWN_CARDINALITY; + List<TempMetadataID> primaryKey; + QueryNode queryNode; + LRUCache<Object, Object> localCache; + CacheHint cacheHint; + List<List<TempMetadataID>> keys; + List<List<TempMetadataID>> indexes; + } + + private static TableData DUMMY_DATA = new TableData(); + public enum Type { VIRTUAL, TEMP, @@ -50,18 +68,11 @@ private String ID; // never null, upper cased fully-qualified string private Type metadataType = Type.VIRTUAL; + private Object originalMetadataID; - //Table metadata - private Collection<TempMetadataID> accessPatterns; - private List<TempMetadataID> elements; // of TempMetadataID, only for group - private int cardinality = QueryMetadataInterface.UNKNOWN_CARDINALITY; - private List<TempMetadataID> primaryKey; - private QueryNode queryNode; - private transient LRUCache<Object, Object> localCache; - private CacheHint cacheHint; + private TableData data; - //Column metadata - private Object originalMetadataID; + //Column metadata private int position; private Class<?> type; // type of this element, only for element @@ -81,8 +92,9 @@ * @param isVirtual whether or not the group is a virtual group */ public TempMetadataID(String ID, List<TempMetadataID> elements, Type type) { + this.data = new TableData(); this.ID = ID; - this.elements = elements; + this.data.elements = elements; int pos = 1; for (TempMetadataID tempMetadataID : elements) { tempMetadataID.setPosition(pos++); @@ -133,7 +145,7 @@ * @return List of TempMetadataID for groups, null for elements */ public List<TempMetadataID> getElements() { - return this.elements; + return this.getTableData().elements; } /** @@ -141,12 +153,12 @@ * @param elem */ protected void addElement(TempMetadataID elem) { - if (this.elements != null) { - this.elements.add(elem); - elem.setPosition(this.elements.size()); + if (this.getTableData().elements != null) { + this.getTableData().elements.add(elem); + elem.setPosition(this.getTableData().elements.size()); } - if (this.localCache != null) { - this.localCache.clear(); + if (this.getTableData().localCache != null) { + this.getTableData().localCache.clear(); } } @@ -198,7 +210,7 @@ return this.ID.hashCode(); } - public void setOriginalMetadataID(Object metadataId) { + public void setOriginalMetadataID(Object metadataId) { this.originalMetadataID = metadataId; } @@ -211,22 +223,22 @@ } public Collection<TempMetadataID> getAccessPatterns() { - if (this.accessPatterns == null) { + if (this.getTableData().accessPatterns == null) { return Collections.emptyList(); } - return this.accessPatterns; + return this.getTableData().accessPatterns; } public void setAccessPatterns(Collection<TempMetadataID> accessPatterns) { - this.accessPatterns = accessPatterns; + this.getTableData().accessPatterns = accessPatterns; } public int getCardinality() { - return this.cardinality; + return this.getTableData().cardinality; } public void setCardinality(int cardinality) { - this.cardinality = cardinality; + this.getTableData().cardinality = cardinality; } public void setTempTable(boolean isTempTable) { @@ -238,17 +250,17 @@ } Object getProperty(Object key) { - if (this.localCache != null) { - return this.localCache.get(key); + if (this.getTableData().localCache != null) { + return this.getTableData().localCache.get(key); } return null; } Object setProperty(Object key, Object value) { - if (this.localCache == null) { - this.localCache = new LRUCache<Object, Object>(LOCAL_CACHE_SIZE); + if (this.getTableData().localCache == null) { + this.getTableData().localCache = new LRUCache<Object, Object>(LOCAL_CACHE_SIZE); } - return this.localCache.put(key, value); + return this.getTableData().localCache.put(key, value); } public boolean isScalarGroup() { @@ -260,11 +272,11 @@ } public List<TempMetadataID> getPrimaryKey() { - return primaryKey; + return getTableData().primaryKey; } public void setPrimaryKey(List<TempMetadataID> primaryKey) { - this.primaryKey = primaryKey; + this.getTableData().primaryKey = primaryKey; } public int getPosition() { @@ -276,19 +288,48 @@ } public QueryNode getQueryNode() { - return queryNode; + return getTableData().queryNode; } public void setQueryNode(QueryNode queryNode) { - this.queryNode = queryNode; + this.getTableData().queryNode = queryNode; } public CacheHint getCacheHint() { - return cacheHint; + return getTableData().cacheHint; } public void setCacheHint(CacheHint cacheHint) { - this.cacheHint = cacheHint; + this.getTableData().cacheHint = cacheHint; } + + public List<List<TempMetadataID>> getIndexes() { + return getTableData().indexes; + } + + public void addIndex(List<TempMetadataID> index) { + if (this.getTableData().indexes == null) { + this.getTableData().indexes = new LinkedList<List<TempMetadataID>>(); + } + this.getTableData().indexes.add(index); + } + + public List<List<TempMetadataID>> getUniqueKeys() { + return getTableData().keys; + } + + public void addUniqueKey(List<TempMetadataID> key) { + if (this.getTableData().keys == null) { + this.getTableData().keys = new LinkedList<List<TempMetadataID>>(); + } + this.getTableData().keys.add(key); + } + + private TableData getTableData() { + if (data == null) { + return DUMMY_DATA; + } + return data; + } } Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -490,12 +490,12 @@ if (container.areResultsCachable() && Query.areResultsCachable(container.getProcedureParameters().keySet()) && context.isResultSetCacheEnabled()) { container.getGroup().setGlobalTable(true); container.setCacheHint(c.getCacheHint()); - recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.LOW, "SimpleQueryResolver.procedure_cache_used", container.getGroup()); //$NON-NLS-1$ + recordAnnotation(analysisRecord, Annotation.CACHED_PROCEDURE, Priority.LOW, "SimpleQueryResolver.procedure_cache_used", container.getGroup()); //$NON-NLS-1$ return; } - recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.MEDIUM, "SimpleQueryResolver.procedure_cache_not_usable", container.getGroup()); //$NON-NLS-1$ + recordAnnotation(analysisRecord, Annotation.CACHED_PROCEDURE, Priority.MEDIUM, "SimpleQueryResolver.procedure_cache_not_usable", container.getGroup()); //$NON-NLS-1$ } else { - recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.LOW, "SimpleQueryResolver.procedure_cache_not_used", container.getGroup()); //$NON-NLS-1$ + recordAnnotation(analysisRecord, Annotation.CACHED_PROCEDURE, Priority.LOW, "SimpleQueryResolver.procedure_cache_not_used", container.getGroup()); //$NON-NLS-1$ } } } @@ -984,16 +984,18 @@ id.setCardinality(metadata.getCardinality(table.getMetadataID())); Object pk = metadata.getPrimaryKey(table.getMetadataID()); - //primary key if (pk != null) { - List cols = metadata.getElementIDsInKey(pk); - ArrayList<TempMetadataID> primaryKey = new ArrayList<TempMetadataID>(cols.size()); - for (Object coldId : cols) { - int pos = metadata.getPosition(coldId) - 1; - primaryKey.add(id.getElements().get(pos)); - } + ArrayList<TempMetadataID> primaryKey = resolveIndex(metadata, id, pk); id.setPrimaryKey(primaryKey); } + Collection keys = metadata.getUniqueKeysInGroup(table.getMetadataID()); + for (Object key : keys) { + id.addUniqueKey(resolveIndex(metadata, id, key)); + } + Collection indexes = metadata.getIndexesInGroup(table.getMetadataID()); + for (Object index : indexes) { + id.addIndex(resolveIndex(metadata, id, index)); + } Command c = getCommand(table, metadata.getVirtualPlan(table.getMetadataID()), SQLConstants.Reserved.SELECT, metadata, analysisRecord); CacheHint hint = c.getCacheHint(); if (hint != null) { @@ -1008,6 +1010,18 @@ return id; } + private static ArrayList<TempMetadataID> resolveIndex( + QueryMetadataInterface metadata, TempMetadataID id, Object pk) + throws TeiidComponentException, QueryMetadataException { + List cols = metadata.getElementIDsInKey(pk); + ArrayList<TempMetadataID> primaryKey = new ArrayList<TempMetadataID>(cols.size()); + for (Object coldId : cols) { + int pos = metadata.getPosition(coldId) - 1; + primaryKey.add(id.getElements().get(pos)); + } + return primaryKey; + } + private static Command getCommand(GroupSymbol virtualGroup, QueryNode qnode, String cacheString, QueryMetadataInterface qmi, AnalysisRecord analysisRecord) throws TeiidComponentException, QueryMetadataException, QueryResolverException, Deleted: branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexCondition.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexCondition.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexCondition.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -1,152 +0,0 @@ -/* - * JBoss, Home of Professional Open Source. - * See the COPYRIGHT.txt file distributed with this work for information - * regarding copyright ownership. Some portions may be licensed - * to Red Hat, Inc. under one or more contributor license agreements. - * - * This library is free software; you can redistribute it and/or - * modify it under the terms of the GNU Lesser General Public - * License as published by the Free Software Foundation; either - * version 2.1 of the License, or (at your option) any later version. - * - * This library is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU - * Lesser General Public License for more details. - * - * You should have received a copy of the GNU Lesser General Public - * License along with this library; if not, write to the Free Software - * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA - * 02110-1301 USA. - */ - -package org.teiid.query.tempdata; - -import java.util.Iterator; -import java.util.List; -import java.util.TreeSet; - -import org.teiid.query.sql.lang.CompareCriteria; -import org.teiid.query.sql.lang.Criteria; -import org.teiid.query.sql.lang.IsNullCriteria; -import org.teiid.query.sql.lang.MatchCriteria; -import org.teiid.query.sql.lang.SetCriteria; -import org.teiid.query.sql.symbol.Constant; -import org.teiid.query.sql.symbol.ElementSymbol; - -/** - * Accumulates information from criteria about a specific index column. - */ -class IndexCondition { - - static IndexCondition[] getIndexConditions(Criteria condition, List<ElementSymbol> keyColumns) { - List<Criteria> crits = Criteria.separateCriteriaByAnd(condition); - IndexCondition[] conditions = new IndexCondition[keyColumns.size()]; - for (int i = 0; i < conditions.length; i++) { - conditions[i] = new IndexCondition(); - ElementSymbol keyColumn = keyColumns.get(i); - for (Iterator<Criteria> critIter = crits.iterator(); critIter.hasNext();) { - Criteria criteria = critIter.next(); - if (criteria instanceof CompareCriteria) { - CompareCriteria cc = (CompareCriteria)criteria; - if (cc.getOperator() == CompareCriteria.NE - || !(cc.getRightExpression() instanceof Constant)) { - critIter.remove(); - continue; - } - if (!cc.getLeftExpression().equals(keyColumn)) { - continue; - } - conditions[i].addCondition((Constant)cc.getRightExpression(), cc.getOperator()); - critIter.remove(); - } else if (criteria instanceof IsNullCriteria) { - IsNullCriteria inc = (IsNullCriteria)criteria; - if (inc.isNegated() || !inc.getExpression().equals(keyColumn)) { - continue; - } - conditions[i].addCondition(new Constant(null), CompareCriteria.EQ); - critIter.remove(); - } else { - if (i > 0) { - critIter.remove(); - continue; - } - if (criteria instanceof MatchCriteria) { - MatchCriteria matchCriteria = (MatchCriteria)criteria; - if (matchCriteria.isNegated() || !matchCriteria.getLeftExpression().equals(keyColumn) || !(matchCriteria.getRightExpression() instanceof Constant)) { - continue; - } - Constant value = (Constant)matchCriteria.getRightExpression(); - String pattern = (String)value.getValue(); - boolean escaped = false; - StringBuilder prefix = new StringBuilder(); - for (int j = 0; i < pattern.length(); j++) { - char character = pattern.charAt(j); - - if (character == matchCriteria.getEscapeChar() && character != MatchCriteria.NULL_ESCAPE_CHAR) { - if (escaped) { - prefix.append(character); - escaped = false; - } else { - escaped = true; - } - } else if (character == MatchCriteria.WILDCARD_CHAR || character == MatchCriteria.MATCH_CHAR) { - break; - } else { - prefix.append(character); - } - } - if (prefix.length() > 0) { - conditions[i].addCondition(new Constant(prefix.toString()), CompareCriteria.GE); - } - } else if (criteria instanceof SetCriteria) { - SetCriteria setCriteria = (SetCriteria)criteria; - if (!setCriteria.getExpression().equals(keyColumn) || !setCriteria.isAllConstants()) { - continue; - } - TreeSet<Constant> values = (TreeSet<Constant>) setCriteria.getValues(); - conditions[i].addSet(values); - } - } - } - } - return conditions; - } - - Constant lower = null; - Constant upper = null; - TreeSet<Constant> valueSet = new TreeSet<Constant>(); - - void addCondition(Constant value, int comparisionMode) { - switch (comparisionMode) { - case CompareCriteria.EQ: - valueSet.clear(); - valueSet.add(value); - lower = null; - upper = null; - break; - case CompareCriteria.GE: - case CompareCriteria.GT: - if (valueSet.isEmpty()) { - lower = value; - } - break; - case CompareCriteria.LE: - case CompareCriteria.LT: - if (valueSet.isEmpty()) { - upper = value; - } - break; - } - } - - void addSet(TreeSet<Constant> values) { - if (!valueSet.isEmpty()) { - return; - } - lower = null; - upper = null; - valueSet.addAll(values); - } - -} \ No newline at end of file Copied: branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java (from rev 2478, branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexCondition.java) =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java (rev 0) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -0,0 +1,263 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ + +package org.teiid.query.tempdata; + +import java.util.ArrayList; +import java.util.Iterator; +import java.util.List; +import java.util.ListIterator; +import java.util.TreeSet; + +import org.teiid.common.buffer.TupleBrowser; +import org.teiid.common.buffer.TupleSource; +import org.teiid.core.TeiidComponentException; +import org.teiid.query.processor.CollectionTupleSource; +import org.teiid.query.processor.relational.RelationalNode; +import org.teiid.query.sql.lang.CompareCriteria; +import org.teiid.query.sql.lang.Criteria; +import org.teiid.query.sql.lang.IsNullCriteria; +import org.teiid.query.sql.lang.MatchCriteria; +import org.teiid.query.sql.lang.OrderBy; +import org.teiid.query.sql.lang.OrderByItem; +import org.teiid.query.sql.lang.SetCriteria; +import org.teiid.query.sql.symbol.Constant; +import org.teiid.query.sql.symbol.ElementSymbol; +import org.teiid.query.sql.symbol.SingleElementSymbol; + +/** + * Accumulates information about index usage. + */ +class IndexInfo { + + List<Object> lower = null; + List<Object> upper = null; + ArrayList<List<Object>> valueSet = new ArrayList<List<Object>>(); + TempTable table; + Boolean ordering; + boolean covering; + TupleSource valueTs; + + public IndexInfo(TempTable table, final List<? extends SingleElementSymbol> projectedCols, final Criteria condition, OrderBy orderBy, boolean primary) { + this.table = table; + if (primary || this.table.getColumnMap().keySet().containsAll(projectedCols)) { + covering = true; + } + if (table.getPkLength() > 0) { + processCriteria(condition); + if (orderBy != null && (covering || this.table.getColumnMap().keySet().containsAll(orderBy.getSortKeys()))) { + ordering = useIndexForOrderBy(orderBy); + } + } + } + + private void processCriteria(Criteria condition) { + List<Criteria> crits = Criteria.separateCriteriaByAnd(condition); + for (int i = 0; i < table.getPkLength(); i++) { + ElementSymbol keyColumn = table.getColumns().get(i); + for (Iterator<Criteria> critIter = crits.iterator(); critIter.hasNext();) { + Criteria criteria = critIter.next(); + if (criteria instanceof CompareCriteria) { + CompareCriteria cc = (CompareCriteria)criteria; + if (cc.getOperator() == CompareCriteria.NE + || !(cc.getRightExpression() instanceof Constant)) { + critIter.remove(); + continue; + } + if (!cc.getLeftExpression().equals(keyColumn)) { + continue; + } + this.addCondition(i, (Constant)cc.getRightExpression(), cc.getOperator()); + critIter.remove(); + } else if (criteria instanceof IsNullCriteria) { + IsNullCriteria inc = (IsNullCriteria)criteria; + if (inc.isNegated() || !inc.getExpression().equals(keyColumn)) { + continue; + } + this.addCondition(i, new Constant(null), CompareCriteria.EQ); + critIter.remove(); + } else { + if (i > 0) { + critIter.remove(); + continue; + } + if (criteria instanceof MatchCriteria) { + MatchCriteria matchCriteria = (MatchCriteria)criteria; + if (matchCriteria.isNegated() || !matchCriteria.getLeftExpression().equals(keyColumn) || !(matchCriteria.getRightExpression() instanceof Constant)) { + continue; + } + Constant value = (Constant)matchCriteria.getRightExpression(); + String pattern = (String)value.getValue(); + boolean escaped = false; + StringBuilder prefix = new StringBuilder(); + for (int j = 0; i < pattern.length(); j++) { + char character = pattern.charAt(j); + + if (character == matchCriteria.getEscapeChar() && character != MatchCriteria.NULL_ESCAPE_CHAR) { + if (escaped) { + prefix.append(character); + escaped = false; + } else { + escaped = true; + } + } else if (character == MatchCriteria.WILDCARD_CHAR || character == MatchCriteria.MATCH_CHAR) { + break; + } else { + prefix.append(character); + } + } + if (prefix.length() > 0) { + this.addCondition(i, new Constant(prefix.toString()), CompareCriteria.GE); + } + } else if (criteria instanceof SetCriteria) { + SetCriteria setCriteria = (SetCriteria)criteria; + if (!setCriteria.getExpression().equals(keyColumn) || !setCriteria.isAllConstants()) { + continue; + } + TreeSet<Constant> values = (TreeSet<Constant>) setCriteria.getValues(); + this.addSet(i, values); + } + } + } + } + } + + void addCondition(int i, Constant value, int comparisionMode) { + switch (comparisionMode) { + case CompareCriteria.EQ: + if (i == 0) { + valueSet.clear(); + valueSet.add(new ArrayList<Object>(table.getPkLength())); + } + if (valueSet.size() == 1) { + valueSet.get(0).add(value.getValue()); + } + lower = null; + upper = null; + break; + case CompareCriteria.GE: + case CompareCriteria.GT: + if (valueSet.isEmpty()) { + if (i == 0) { + lower = new ArrayList<Object>(table.getPkLength()); + lower.add(value.getValue()); + } if (lower != null && lower.size() == i) { + lower.add(value.getValue()); + } + } + break; + case CompareCriteria.LE: + case CompareCriteria.LT: + if (valueSet.isEmpty()) { + if (i == 0) { + upper = new ArrayList<Object>(table.getPkLength()); + upper.add(value.getValue()); + } else if (upper != null && upper.size() == i) { + upper.add(value.getValue()); + } + } + break; + } + } + + void addSet(int i, TreeSet<Constant> values) { + if (!valueSet.isEmpty()) { + return; + } + if (i == 0) { + for (Constant constant : values) { + List<Object> value = new ArrayList<Object>(table.getPkLength()); + value.add(constant.getValue()); + valueSet.add(value); + } + lower = null; + upper = null; + } + } + + /** + * Return a non-null direction if the index can be used, otherwise null. + * @param orderBy + * @return + */ + private Boolean useIndexForOrderBy(OrderBy orderBy) { + Boolean direction = null; + int[] orderByIndexes = RelationalNode.getProjectionIndexes(this.table.getColumnMap(), orderBy.getSortKeys()); + for (int i = 0; i < table.getPkLength(); i++) { + if (orderByIndexes.length <= i) { + break; + } + if (orderByIndexes[i] != i) { + return null; + } + } + for (OrderByItem item : orderBy.getOrderByItems()) { + if (item.getNullOrdering() != null) { + return null; + } + if (item.isAscending()) { + if (direction == null) { + direction = OrderBy.ASC; + } else if (direction != OrderBy.ASC) { + return null; + } + } else if (direction == null) { + direction = OrderBy.DESC; + } else if (direction != OrderBy.DESC) { + return null; + } + } + return direction; + } + + TupleBrowser createTupleBrowser() throws TeiidComponentException { + boolean direction = ordering == null ? OrderBy.ASC : ordering; + if (valueTs != null) { + return new TupleBrowser(this.table.getTree(), valueTs, direction); + } + if (!valueSet.isEmpty()) { + CollectionTupleSource cts = null; + if (direction == OrderBy.ASC) { + cts = new CollectionTupleSource(valueSet.iterator()); + } else { + cts = new CollectionTupleSource(new Iterator<List<Object>>() { + ListIterator<List<Object>> iter = valueSet.listIterator(valueSet.size()); + @Override + public boolean hasNext() { + return iter.hasPrevious(); + } + @Override + public List<Object> next() { + return iter.previous(); + } + @Override + public void remove() { + throw new UnsupportedOperationException(); + } + }); + } + return new TupleBrowser(this.table.getTree(), cts, direction); + } + return new TupleBrowser(this.table.getTree(), lower, upper, direction); + } + +} \ No newline at end of file Property changes on: branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTable.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTable.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTable.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -23,7 +23,9 @@ package org.teiid.query.tempdata; import java.util.ArrayList; +import java.util.Collections; import java.util.HashSet; +import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Set; @@ -56,9 +58,7 @@ import org.teiid.query.sql.lang.CacheHint; import org.teiid.query.sql.lang.Criteria; import org.teiid.query.sql.lang.OrderBy; -import org.teiid.query.sql.lang.OrderByItem; import org.teiid.query.sql.lang.SetClauseList; -import org.teiid.query.sql.symbol.Constant; import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.sql.symbol.Expression; import org.teiid.query.sql.symbol.SingleElementSymbol; @@ -70,6 +70,8 @@ */ class TempTable { + private static final double LN_2 = Math.log(2); + private final class InsertUpdateProcessor extends UpdateProcessor { private boolean addRowId; @@ -258,13 +260,16 @@ private TempMetadataID tid; private ReentrantReadWriteLock lock = new ReentrantReadWriteLock(); private boolean updatable = true; + private LinkedHashMap<List<ElementSymbol>, TempTable> indexTables; private int keyBatchSize; private int leafBatchSize; + private Map columnMap; TempTable(TempMetadataID tid, BufferManager bm, List<ElementSymbol> columns, int primaryKeyLength, String sessionID) { this.tid = tid; this.bm = bm; + this.columnMap = RelationalNode.createLookupMap(columns); if (primaryKeyLength == 0) { ElementSymbol id = new ElementSymbol("rowId"); //$NON-NLS-1$ id.setType(DataTypeManager.DefaultDataClasses.INTEGER); @@ -280,64 +285,82 @@ this.leafBatchSize = bm.getSchemaSize(columns.subList(0, primaryKeyLength)); } + void addIndex(List<ElementSymbol> indexColumns) throws TeiidComponentException, TeiidProcessingException { + List<ElementSymbol> keyColumns = columns.subList(0, tree.getKeyLength()); + if (keyColumns.equals(indexColumns) || (indexTables != null && indexTables.containsKey(indexColumns))) { + return; + } + List<ElementSymbol> allColumns = new ArrayList<ElementSymbol>(indexColumns); + for (ElementSymbol elementSymbol : keyColumns) { + if (allColumns.indexOf(elementSymbol) < 0) { + allColumns.add(elementSymbol); + } + } + TempTable indexTable = new TempTable(new TempMetadataID("idx", Collections.EMPTY_LIST), this.bm, allColumns, indexColumns.size(), this.sessionID); //$NON-NLS-1$ + indexTable.setPreferMemory(this.tree.isPreferMemory()); + if (indexTables == null) { + indexTables = new LinkedHashMap<List<ElementSymbol>, TempTable>(); + indexTables.put(indexColumns, indexTable); + } + //TODO: ordered insert optimization + TupleSource ts = createTupleSource(allColumns, null, null); + indexTable.insert(ts, allColumns); + } private int reserveBuffers() { return bm.reserveBuffers(leafBatchSize + (tree.getHeight() - 1)*keyBatchSize, BufferReserveMode.WAIT); } public TupleSource createTupleSource(final List<? extends SingleElementSymbol> projectedCols, final Criteria condition, OrderBy orderBy) throws TeiidComponentException, TeiidProcessingException { - Map map = RelationalNode.createLookupMap(getColumns()); - - Boolean direction = null; - boolean orderByUsingIndex = false; - if (orderBy != null && rowId == null) { - int[] orderByIndexes = RelationalNode.getProjectionIndexes(map, orderBy.getSortKeys()); - orderByUsingIndex = true; - for (int i = 0; i < tree.getKeyLength(); i++) { - if (orderByIndexes.length <= i) { - break; + IndexInfo primary = new IndexInfo(this, projectedCols, condition, orderBy, true); + IndexInfo ii = primary; + if (indexTables != null && (condition != null || orderBy != null) && ii.valueSet.size() != 1) { + int rowCost = this.tree.getRowCount(); + int bestCost = estimateCost(orderBy, ii, rowCost); + for (TempTable table : this.indexTables.values()) { + IndexInfo secondary = new IndexInfo(table, projectedCols, condition, orderBy, false); + int cost = estimateCost(orderBy, secondary, rowCost); + if (cost < bestCost) { + ii = secondary; + bestCost = cost; } - if (orderByIndexes[i] != i) { - orderByUsingIndex = false; - break; - } } - if (orderByUsingIndex) { - for (OrderByItem item : orderBy.getOrderByItems()) { - if (item.getNullOrdering() != null) { - orderByUsingIndex = false; - break; - } - if (item.isAscending()) { - if (direction == null) { - direction = OrderBy.ASC; - } else if (direction != OrderBy.ASC) { - orderByUsingIndex = false; - break; - } - } else if (direction == null) { - direction = OrderBy.DESC; - } else if (direction != OrderBy.DESC) { - orderByUsingIndex = false; - break; - } - } + if (ii.covering) { + return ii.table.createTupleSource(projectedCols, condition, orderBy, ii); } + List<ElementSymbol> pkColumns = this.columns.subList(0, this.getPkLength()); + if (ii.ordering != null) { + //use order and join + primary.valueTs = ii.table.createTupleSource(pkColumns, condition, orderBy, ii); + primary.ordering = null; + return createTupleSource(projectedCols, condition, null, primary); + } + //order by pk to localize lookup costs, then join + OrderBy pkOrderBy = new OrderBy(); + for (ElementSymbol elementSymbol : pkColumns) { + pkOrderBy.addVariable(elementSymbol); + } + primary.valueTs = ii.table.createTupleSource(pkColumns, condition, pkOrderBy, ii); + return createTupleSource(projectedCols, condition, orderBy, primary); } - if (!orderByUsingIndex) { - direction = OrderBy.ASC; - } - TupleBrowser browser = createTupleBrower(condition, direction); - TupleSource ts = new QueryTupleSource(browser, map, projectedCols, condition); + return createTupleSource(projectedCols, condition, orderBy, ii); + } + + private TupleSource createTupleSource( + final List<? extends SingleElementSymbol> projectedCols, + final Criteria condition, OrderBy orderBy, IndexInfo ii) + throws TeiidComponentException, TeiidProcessingException { + TupleBrowser browser = ii.createTupleBrowser(); + TupleSource ts = new QueryTupleSource(browser, columnMap, projectedCols, condition); boolean usingQueryTupleSource = false; try { TupleBuffer tb = null; - if (!orderByUsingIndex && orderBy != null) { + if (ii.ordering == null && orderBy != null) { SortUtility sort = new SortUtility(ts, orderBy.getOrderByItems(), Mode.SORT, bm, sessionID, projectedCols); tb = sort.sort(); - } else if (!updatable) { + } else if (updatable) { tb = bm.createTupleBuffer(projectedCols, sessionID, TupleSourceType.PROCESSOR); List<?> next = null; while ((next = ts.nextTuple()) != null) { @@ -357,49 +380,32 @@ } } - private TupleBrowser createTupleBrower(Criteria condition, boolean direction) throws TeiidComponentException { - List<Object> lower = null; - List<Object> upper = null; - List<List<Object>> values = null; - if (condition != null && rowId == null) { - IndexCondition[] indexConditions = IndexCondition.getIndexConditions(condition, columns.subList(0, tree.getKeyLength())); - for (int i = 0; i < indexConditions.length; i++) { - IndexCondition indexCondition = indexConditions[i]; - if (indexCondition.lower != null) { - if (i == 0) { - lower = new ArrayList<Object>(tree.getKeyLength()); - lower.add(indexCondition.lower.getValue()); - } if (lower != null && lower.size() == i) { - lower.add(indexCondition.lower.getValue()); - } - } - if (indexCondition.upper != null) { - if (i == 0) { - upper = new ArrayList<Object>(tree.getKeyLength()); - upper.add(indexCondition.upper.getValue()); - } else if (upper != null && upper.size() == i) { - upper.add(indexCondition.upper.getValue()); - } - } - if (!indexCondition.valueSet.isEmpty()) { - if (i == 0) { - values = new ArrayList<List<Object>>(); - for (Constant constant : indexCondition.valueSet) { - List<Object> value = new ArrayList<Object>(tree.getKeyLength()); - value.add(constant.getValue()); - values.add(value); - } - } else if (values != null && values.size() == 1 && values.iterator().next().size() == i && indexCondition.valueSet.size() == 1) { - values.iterator().next().add(indexCondition.valueSet.first().getValue()); - } - } - } + /** + * TODO: this could easily use statistics - the tree level 1 would be an ideal place + * to compute them, since it minimizes page loads, and is a random sample. + * @return + */ + private int estimateCost(OrderBy orderBy, IndexInfo ii, int rowCost) { + if (ii.valueSet.size() != 0) { + int length = ii.valueSet.get(0).size(); + rowCost = Math.min(rowCost, ii.valueSet.size() * 1 << (ii.table.getPkLength() - length)); + } else if (ii.upper != null) { + rowCost /= 3; + } else if (ii.lower != null) { + rowCost /= 3; } - if (values != null) { - return new TupleBrowser(this.tree, values, direction); + int cost = Math.max(1, rowCost); + if (!ii.covering || (orderBy != null && ii.ordering == null)) { + cost = (int)(cost * Math.log(cost)/LN_2); } - return new TupleBrowser(this.tree, lower, upper, direction); + return cost; } + + private TupleBrowser createTupleBrower(Criteria condition, boolean direction) throws TeiidComponentException { + IndexInfo ii = new IndexInfo(this, null, condition, null, true); + ii.ordering = direction; + return ii.createTupleBrowser(); + } public int getRowCount() { return tree.getRowCount(); @@ -411,6 +417,11 @@ public void remove() { tree.remove(); + if (this.indexTables != null) { + for (TempTable indexTable : this.indexTables.values()) { + indexTable.remove(); + } + } } public List<ElementSymbol> getColumns() { @@ -547,9 +558,24 @@ try { lock.writeLock().lock(); if (remove) { - return tree.remove(tuple); + List<?> result = tree.remove(tuple); + if (result == null) { + return null; + } + if (indexTables != null) { + //remove from each index table + /*for (TempTable index : this.indexTables.values()) { + index.tree + }*/ + } + return result; } - return tree.insert(tuple, InsertMode.UPDATE); + List<?> result = tree.insert(tuple, InsertMode.UPDATE); + if (indexTables != null) { + //update each index table + + } + return result; } finally { lock.writeLock().unlock(); } @@ -567,6 +593,11 @@ void setUpdatable(boolean updatable) { this.updatable = updatable; + if (this.indexTables != null) { + for (TempTable index : this.indexTables.values()) { + index.setUpdatable(updatable); + } + } } CacheHint getCacheHint() { @@ -583,5 +614,18 @@ public boolean isUpdatable() { return updatable; } + + @Override + public String toString() { + return tid.getID() + " (" + columns + ")\n"; //$NON-NLS-1$ //$NON-NLS-2$ + } + Map getColumnMap() { + return this.columnMap; + } + + STree getTree() { + return tree; + } + } \ No newline at end of file Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -24,6 +24,7 @@ import java.util.ArrayList; import java.util.Arrays; +import java.util.Collection; import java.util.LinkedList; import java.util.List; import java.util.concurrent.Callable; @@ -113,14 +114,8 @@ }, new SessionAwareCache<CachedResults>()); } - /** - * Constructor takes the "real" ProcessorDataManager that this object will be a proxy to, - * and will pass most calls through to transparently. Only when a request is registered for - * a temp group will this proxy do it's thing. - * @param processorDataManager the real ProcessorDataManager that this object is a proxy to - * @param cache - */ - public TempTableDataManager(ProcessorDataManager processorDataManager, BufferManager bufferManager, Executor executor, SessionAwareCache<CachedResults> cache){ + public TempTableDataManager(ProcessorDataManager processorDataManager, BufferManager bufferManager, + Executor executor, SessionAwareCache<CachedResults> cache){ this.processorDataManager = processorDataManager; this.bufferManager = bufferManager; this.executor = executor; @@ -159,42 +154,9 @@ return result; } } else if (proc.getGroup().isGlobalTable()) { - String fullName = context.getMetadata().getFullName(proc.getProcedureID()); - LinkedList<Object> vals = new LinkedList<Object>(); - for (SPParameter param : proc.getInputParameters()) { - vals.add(((Constant)param.getExpression()).getValue()); - } - //collapse the hash to single byte for the key to restrict the possible results to 256 - int hash = vals.hashCode(); - hash |= (hash >>> 16); - hash |= (hash >>> 8); - hash &= 0x000000ff; - CacheID cid = new CacheID(new ParseInfo(), fullName + hash, context.getVdbName(), - context.getVdbVersion(), context.getConnectionID(), context.getUserName()); - cid.setParameters(vals); - CachedResults results = cache.get(cid); - if (results != null) { - TupleBuffer buffer = results.getResults(); - return buffer.createIndexedTupleSource(); - } - CacheHint hint = proc.getCacheHint(); - proc.setCacheHint(null); - Option option = new Option(); - option.setNoCache(true); - option.addNoCacheGroup(fullName); - proc.setOption(option); - int determinismLevel = context.resetDeterminismLevel(); - QueryProcessor qp = context.getQueryProcessorFactory().createQueryProcessor(proc.toString(), fullName.toUpperCase(), context); - qp.setNonBlocking(true); - BatchCollector bc = qp.createBatchCollector(); - TupleBuffer tb = bc.collectTuples(); - CachedResults cr = new CachedResults(); - cr.setResults(tb); - cr.setHint(hint); - cache.put(cid, context.getDeterminismLevel(), cr, hint != null?hint.getTtl():null); - context.setDeterminismLevel(determinismLevel); - return tb.createIndexedTupleSource(); + return handleCachedProcedure(context, proc); } + return null; //it's not a stored procedure we want to handle } GroupSymbol group = ((ProcedureContainer)command).getGroup(); @@ -248,6 +210,50 @@ return null; } + private TupleSource handleCachedProcedure(CommandContext context, + StoredProcedure proc) throws TeiidComponentException, + QueryMetadataException, TeiidProcessingException { + String fullName = context.getMetadata().getFullName(proc.getProcedureID()); + LogManager.logDetail(LogConstants.CTX_DQP, "processing cached procedure request for", fullName); //$NON-NLS-1$ + LinkedList<Object> vals = new LinkedList<Object>(); + for (SPParameter param : proc.getInputParameters()) { + vals.add(((Constant)param.getExpression()).getValue()); + } + //collapse the hash to single byte for the key to restrict the possible results to 256 + int hash = vals.hashCode(); + hash |= (hash >>> 16); + hash |= (hash >>> 8); + hash &= 0x000000ff; + CacheID cid = new CacheID(new ParseInfo(), fullName + hash, context.getVdbName(), + context.getVdbVersion(), context.getConnectionID(), context.getUserName()); + cid.setParameters(vals); + CachedResults results = cache.get(cid); + if (results != null) { + TupleBuffer buffer = results.getResults(); + return buffer.createIndexedTupleSource(); + } + //construct a query with a no cache hint + //note that it's safe to use the stringified form of the parameters because + //it's not possible to use xml/clob/blob/object + CacheHint hint = proc.getCacheHint(); + proc.setCacheHint(null); + Option option = new Option(); + option.setNoCache(true); + option.addNoCacheGroup(fullName); + proc.setOption(option); + int determinismLevel = context.resetDeterminismLevel(); + QueryProcessor qp = context.getQueryProcessorFactory().createQueryProcessor(proc.toString(), fullName.toUpperCase(), context); + qp.setNonBlocking(true); + BatchCollector bc = qp.createBatchCollector(); + TupleBuffer tb = bc.collectTuples(); + CachedResults cr = new CachedResults(); + cr.setResults(tb); + cr.setHint(hint); + cache.put(cid, context.getDeterminismLevel(), cr, hint != null?hint.getTtl():null); + context.setDeterminismLevel(determinismLevel); + return tb.createIndexedTupleSource(); + } + private TupleSource handleSystemProcedures(CommandContext context, StoredProcedure proc) throws TeiidComponentException, QueryMetadataException, QueryProcessingException, QueryResolverException, @@ -259,6 +265,7 @@ Object groupID = validateMatView(metadata, proc); String matViewName = metadata.getFullName(groupID); String matTableName = RelationalPlanner.MAT_PREFIX+matViewName.toUpperCase(); + LogManager.logDetail(LogConstants.CTX_MATVIEWS, "processing refreshmatview for", matViewName); //$NON-NLS-1$ MatTableInfo info = globalStore.getMatTableInfo(matTableName); boolean invalidate = Boolean.TRUE.equals(((Constant)proc.getParameter(1).getExpression()).getValue()); MatState oldState = info.setState(MatState.NEEDS_LOADING, invalidate?Boolean.FALSE:null); @@ -387,23 +394,24 @@ QueryMetadataInterface metadata = context.getMetadata(); Create create = new Create(); create.setTable(group); - create.setColumns(ResolverUtil.resolveElementsInGroup(group, metadata)); + List<ElementSymbol> allColumns = ResolverUtil.resolveElementsInGroup(group, metadata); + create.setColumns(allColumns); Object pk = metadata.getPrimaryKey(group.getMetadataID()); if (pk != null) { - for (Object col : metadata.getElementIDsInKey(pk)) { - create.getPrimaryKey().add(create.getColumns().get(metadata.getPosition(col)-1)); - } + List<ElementSymbol> pkColumns = resolveIndex(metadata, allColumns, pk); + create.getPrimaryKey().addAll(pkColumns); } TempTable table = globalStore.addTempTable(tableName, create, bufferManager, false); table.setUpdatable(false); CacheHint hint = table.getCacheHint(); + boolean updatable = false; if (hint != null) { table.setPreferMemory(hint.getPrefersMemory()); if (hint.getTtl() != null) { info.setTtl(table.getCacheHint().getTtl()); } if (pk != null) { - table.setUpdatable(hint.isUpdatable()); + updatable = hint.isUpdatable(); } } int rowCount = -1; @@ -418,6 +426,16 @@ //TODO: if this insert fails, it's unnecessary to do the undo processing table.insert(ts, table.getColumns()); rowCount = table.getRowCount(); + //TODO: could pre-process indexes to remove overlap + for (Object index : metadata.getIndexesInGroup(group.getMetadataID())) { + List<ElementSymbol> columns = resolveIndex(metadata, allColumns, index); + table.addIndex(columns); + } + for (Object key : metadata.getUniqueKeysInGroup(group.getMetadataID())) { + List<ElementSymbol> columns = resolveIndex(metadata, allColumns, key); + table.addIndex(columns); + } + table.setUpdatable(updatable); } catch (TeiidComponentException e) { LogManager.logError(LogConstants.CTX_MATVIEWS, e, QueryExecPlugin.Util.getString("TempTableDataManager.failed_load", tableName)); //$NON-NLS-1$ throw e; @@ -436,6 +454,20 @@ return rowCount; } + /** + * Return a list of ElementSymbols for the given index/key object + */ + private List<ElementSymbol> resolveIndex(QueryMetadataInterface metadata, + List<ElementSymbol> allColumns, Object pk) + throws TeiidComponentException, QueryMetadataException { + Collection<?> pkIds = metadata.getElementIDsInKey(pk); + List<ElementSymbol> pkColumns = new ArrayList<ElementSymbol>(pkIds.size()); + for (Object col : pkIds) { + pkColumns.add(allColumns.get(metadata.getPosition(col)-1)); + } + return pkColumns; + } + public Object lookupCodeValue(CommandContext context, String codeTableName, String returnElementName, String keyElementName, Object keyValue) throws BlockedException, TeiidComponentException, Modified: branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestMaterialization.java =================================================================== --- branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestMaterialization.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestMaterialization.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -62,7 +62,7 @@ dataManager = new TempTableDataManager(hdm, BufferManagerFactory.getStandaloneBufferManager()); } - private void execute(String sql, List... expectedResults) throws Exception { + private void execute(String sql, List<?>... expectedResults) throws Exception { CommandContext cc = TestProcessor.createCommandContext(); cc.setTempTableStore(tempStore); cc.setGlobalTableStore(globalStore); @@ -114,5 +114,15 @@ execute("call sp1(null)"); assertEquals(3, hdm.getCommandHistory().size()); } + + @Test public void testCoveringSecondaryIndex() throws Exception { + execute("SELECT * from vgroup3 where y in ('zne', 'zwo') order by y desc", Arrays.asList("two", "zwo"), Arrays.asList("one", "zne")); + execute("SELECT * from vgroup3 where y is null", Arrays.asList((String)null, (String)null)); + } + + @Test public void testNonCoveringSecondaryIndex() throws Exception { + execute("SELECT * from vgroup5 where y in ('zne', 'zwo') order by y desc", Arrays.asList("two", "zwo", 1), Arrays.asList("one", "zne", 1)); + execute("SELECT * from vgroup5 where y is null", Arrays.asList((String)null, (String)null, 1)); + } } Modified: branches/7.1.x/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java =================================================================== --- branches/7.1.x/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java 2010-08-27 18:51:58 UTC (rev 2496) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java 2010-08-27 19:00:01 UTC (rev 2497) @@ -343,6 +343,7 @@ new String[] { "x" }, //$NON-NLS-1$ new String[] { DataTypeManager.DefaultDataTypes.STRING}); + //covering index QueryNode vTrans3 = new QueryNode("VGroup3", "SELECT x, 'z' || substring(x, 2) as y FROM matsrc"); //$NON-NLS-1$ //$NON-NLS-2$ Table vGroup3 = createVirtualGroup("VGroup3", virtModel, vTrans3); //$NON-NLS-1$ vGroup3.setMaterialized(true); @@ -350,7 +351,8 @@ new String[] { "x", "y" }, //$NON-NLS-1$ new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING}); - createKey("pk", vGroup3, vElements3.subList(0, 1)); + createKey(KeyRecord.Type.Primary, "pk", vGroup3, vElements3.subList(0, 1)); + createKey(KeyRecord.Type.Index, "idx", vGroup3, vElements3.subList(1, 2)); QueryNode vTrans4 = new QueryNode("VGroup4", "/*+ cache(ttl:100) */ SELECT x FROM matsrc"); //$NON-NLS-1$ //$NON-NLS-2$ Table vGroup4 = createVirtualGroup("VGroup4", virtModel, vTrans4); //$NON-NLS-1$ @@ -359,6 +361,17 @@ new String[] { "x" }, //$NON-NLS-1$ new String[] { DataTypeManager.DefaultDataTypes.STRING}); + //non-covering index + QueryNode vTrans5 = new QueryNode("VGroup5", "SELECT x, 'z' || substring(x, 2) as y, 1 as z FROM matsrc"); //$NON-NLS-1$ //$NON-NLS-2$ + Table vGroup5 = createVirtualGroup("VGroup5", virtModel, vTrans5); //$NON-NLS-1$ + vGroup5.setMaterialized(true); + List<Column> vElements5 = createElements(vGroup5, + new String[] { "x", "y", "z" }, //$NON-NLS-1$ + new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER}); + + createKey(KeyRecord.Type.Primary, "pk", vGroup5, vElements5.subList(0, 1)); + createKey(KeyRecord.Type.Index, "idx", vGroup5, vElements5.subList(1, 2)); + Schema sp = createVirtualModel("sp", metadataStore); //$NON-NLS-1$ ColumnSet<Procedure> rs = createResultSet("sp1.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING }); //$NON-NLS-1$ //$NON-NLS-2$ ProcedureParameter param = createParameter("param1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING); //$NON-NLS-1$ @@ -378,13 +391,22 @@ * metadata IDs) * @return key metadata object */ - public static KeyRecord createKey(String name, Table group, List<Column> elements) { - KeyRecord key = new KeyRecord(org.teiid.metadata.KeyRecord.Type.Primary); + public static KeyRecord createKey(KeyRecord.Type type, String name, Table group, List<Column> elements) { + KeyRecord key = new KeyRecord(type); key.setName(name); for (Column column : elements) { key.addColumn(column); } - group.setPrimaryKey(key); + switch (type) { + case Primary: + group.setPrimaryKey(key); + break; + case Index: + group.getIndexes().add(key); + break; + default: + throw new AssertionError("TODO"); + } return key; }