Author: shawkins Date: 2010-08-29 23:21:37 -0400 (Sun, 29 Aug 2010) New Revision: 2501 Modified: branches/7.1.x/api/src/main/java/org/teiid/metadata/KeyRecord.java branches/7.1.x/api/src/main/java/org/teiid/metadata/MetadataFactory.java branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TransformationMetadata.java branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTable.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 secondary index support to internal materialized views Modified: branches/7.1.x/api/src/main/java/org/teiid/metadata/KeyRecord.java =================================================================== --- branches/7.1.x/api/src/main/java/org/teiid/metadata/KeyRecord.java 2010-08-29 14:03:30 UTC (rev 2500) +++ branches/7.1.x/api/src/main/java/org/teiid/metadata/KeyRecord.java 2010-08-30 03:21:37 UTC (rev 2501) @@ -30,6 +30,7 @@ Primary, Foreign, Unique, //constraint + @Deprecated NonUnique, AccessPattern, Index, @@ -42,6 +43,9 @@ } public Type getType() { + if (type == Type.NonUnique) { + type = Type.Index; + } return type; } Modified: branches/7.1.x/api/src/main/java/org/teiid/metadata/MetadataFactory.java =================================================================== --- branches/7.1.x/api/src/main/java/org/teiid/metadata/MetadataFactory.java 2010-08-29 14:03:30 UTC (rev 2500) +++ branches/7.1.x/api/src/main/java/org/teiid/metadata/MetadataFactory.java 2010-08-30 03:21:37 UTC (rev 2501) @@ -170,7 +170,7 @@ * @throws TranslatorException */ public KeyRecord addIndex(String name, boolean nonUnique, List<String> columnNames, Table table) throws TranslatorException { - KeyRecord index = new KeyRecord(nonUnique?KeyRecord.Type.NonUnique:KeyRecord.Type.Index); + KeyRecord index = new KeyRecord(nonUnique?KeyRecord.Type.Index:KeyRecord.Type.Unique); index.setParent(table); index.setColumns(new ArrayList<Column>(columnNames.size())); index.setName(name); Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TransformationMetadata.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TransformationMetadata.java 2010-08-29 14:03:30 UTC (rev 2500) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/metadata/TransformationMetadata.java 2010-08-30 03:21:37 UTC (rev 2501) @@ -614,7 +614,7 @@ result.add(tableRecordImpl.getPrimaryKey()); } for (KeyRecord key : tableRecordImpl.getIndexes()) { - if (key.getType() == KeyRecord.Type.Index) { + if (key.getType() == KeyRecord.Type.Unique) { result.add(key); } } Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java 2010-08-29 14:03:30 UTC (rev 2500) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java 2010-08-30 03:21:37 UTC (rev 2501) @@ -24,6 +24,7 @@ import java.util.ArrayList; import java.util.Iterator; +import java.util.LinkedList; import java.util.List; import java.util.ListIterator; import java.util.TreeSet; @@ -43,6 +44,7 @@ import org.teiid.query.sql.symbol.Constant; import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.visitor.ElementCollectorVisitor; /** * Accumulates information about index usage. @@ -56,6 +58,8 @@ Boolean ordering; boolean covering; TupleSource valueTs; + List<Criteria> nonCoveredCriteria = new LinkedList<Criteria>(); + List<Criteria> coveredCriteria = new LinkedList<Criteria>(); public IndexInfo(TempTable table, final List<? extends SingleElementSymbol> projectedCols, final Criteria condition, OrderBy orderBy, boolean primary) { this.table = table; @@ -72,6 +76,16 @@ private void processCriteria(Criteria condition) { List<Criteria> crits = Criteria.separateCriteriaByAnd(condition); + for (Iterator<Criteria> critIter = crits.iterator(); critIter.hasNext();) { + Criteria criteria = critIter.next(); + if (table.getColumnMap().keySet().containsAll(ElementCollectorVisitor.getElements(criteria, false))) { + coveredCriteria.add(criteria); + } else { + covering = false; + nonCoveredCriteria.add(criteria); + critIter.remove(); + } + } for (int i = 0; i < table.getPkLength(); i++) { ElementSymbol keyColumn = table.getColumns().get(i); for (Iterator<Criteria> critIter = crits.iterator(); critIter.hasNext();) { 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-29 14:03:30 UTC (rev 2500) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTable.java 2010-08-30 03:21:37 UTC (rev 2501) @@ -70,8 +70,6 @@ */ class TempTable { - private static final double LN_2 = Math.log(2); - private final class InsertUpdateProcessor extends UpdateProcessor { private boolean addRowId; @@ -131,7 +129,9 @@ this.condition = condition; this.project = shouldProject(); this.reserved = reserveBuffers(); - lock.readLock().lock(); + if (updatable) { + lock.readLock().lock(); + } } @Override @@ -144,9 +144,6 @@ reserved = 0; return null; } - if (rowId != null) { - next = next.subList(1, next.size()); - } if (condition != null && !eval.evaluate(condition, next)) { continue; } @@ -159,7 +156,9 @@ @Override public void closeSource() { - lock.readLock().unlock(); + if (updatable) { + lock.readLock().unlock(); + } bm.releaseBuffers(reserved); reserved = 0; browser.closeSource(); @@ -269,7 +268,6 @@ 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); @@ -279,6 +277,7 @@ } else { tree = bm.createSTree(columns, sessionID, primaryKeyLength); } + this.columnMap = RelationalNode.createLookupMap(columns); this.columns = columns; this.sessionID = sessionID; this.keyBatchSize = bm.getSchemaSize(columns); @@ -296,8 +295,9 @@ allColumns.add(elementSymbol); } } - TempTable indexTable = new TempTable(new TempMetadataID("idx", Collections.EMPTY_LIST), this.bm, allColumns, indexColumns.size(), this.sessionID); //$NON-NLS-1$ + TempTable indexTable = new TempTable(new TempMetadataID("idx", Collections.EMPTY_LIST), this.bm, allColumns, allColumns.size(), this.sessionID); //$NON-NLS-1$ indexTable.setPreferMemory(this.tree.isPreferMemory()); + indexTable.lock = this.lock; if (indexTables == null) { indexTables = new LinkedHashMap<List<ElementSymbol>, TempTable>(); indexTables.put(indexColumns, indexTable); @@ -328,20 +328,22 @@ if (ii.covering) { return ii.table.createTupleSource(projectedCols, condition, orderBy, ii); } - List<ElementSymbol> pkColumns = this.columns.subList(0, this.getPkLength()); + List<ElementSymbol> pkColumns = this.columns.subList(0, this.tree.getKeyLength()); if (ii.ordering != null) { //use order and join - primary.valueTs = ii.table.createTupleSource(pkColumns, condition, orderBy, ii); + primary.valueTs = ii.table.createTupleSource(pkColumns, + Criteria.combineCriteria(ii.coveredCriteria), orderBy, ii); primary.ordering = null; - return createTupleSource(projectedCols, condition, null, primary); + return createTupleSource(projectedCols, Criteria.combineCriteria(ii.nonCoveredCriteria), 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); + primary.valueTs = ii.table.createTupleSource(pkColumns, + Criteria.combineCriteria(ii.coveredCriteria), pkOrderBy, ii); + return createTupleSource(projectedCols, Criteria.combineCriteria(ii.nonCoveredCriteria), orderBy, primary); } return createTupleSource(projectedCols, condition, orderBy, ii); @@ -383,20 +385,20 @@ /** * 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 + * TODO: this should also factor in the block size */ 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)); + rowCost = ii.valueSet.size() * (ii.table.getPkLength() - length + 1); } else if (ii.upper != null) { rowCost /= 3; } else if (ii.lower != null) { rowCost /= 3; } int cost = Math.max(1, rowCost); - if (!ii.covering || (orderBy != null && ii.ordering == null)) { - cost = (int)(cost * Math.log(cost)/LN_2); + if (cost > 1 && (!ii.covering || (orderBy != null && ii.ordering == null))) { + cost *= (32 - Integer.numberOfLeadingZeros(cost - 1)); } return cost; } @@ -542,7 +544,7 @@ return CollectionTupleSource.createUpdateCountTupleSource(updateCount); } - private void insertTuple(List<Object> list, boolean ordered) throws TeiidComponentException, TeiidProcessingException { + private void insertTuple(List<?> list, boolean ordered) throws TeiidComponentException, TeiidProcessingException { if (tree.insert(list, ordered?InsertMode.ORDERED:InsertMode.NEW) != null) { throw new TeiidProcessingException(QueryPlugin.Util.getString("TempTable.duplicate_key")); //$NON-NLS-1$ } @@ -563,17 +565,19 @@ return null; } if (indexTables != null) { - //remove from each index table - /*for (TempTable index : this.indexTables.values()) { - index.tree - }*/ + for (TempTable index : this.indexTables.values()) { + tuple = RelationalNode.projectTuple(RelationalNode.getProjectionIndexes(index.getColumnMap(), index.columns), result); + index.tree.remove(tuple); + } } return result; } List<?> result = tree.insert(tuple, InsertMode.UPDATE); if (indexTables != null) { - //update each index table - + for (TempTable index : this.indexTables.values()) { + tuple = RelationalNode.projectTuple(RelationalNode.getProjectionIndexes(index.getColumnMap(), index.columns), tuple); + index.tree.insert(tuple, InsertMode.UPDATE); + } } return result; } finally { 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-29 14:03:30 UTC (rev 2500) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestMaterialization.java 2010-08-30 03:21:37 UTC (rev 2501) @@ -123,6 +123,12 @@ @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)); + execute("SELECT * from vgroup5 where y is null and z = 2"); } + + @Test public void testNonCoveringSecondaryIndexWithoutPrimaryKey() throws Exception { + execute("SELECT * from vgroup6 where y in ('zne', 'zwo') order by y desc", Arrays.asList("two", "zwo"), Arrays.asList("one", "zne")); + execute("SELECT * from vgroup6 where y is null", Arrays.asList((String)null, (String)null)); + } } 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-29 14:03:30 UTC (rev 2500) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java 2010-08-30 03:21:37 UTC (rev 2501) @@ -372,6 +372,16 @@ createKey(KeyRecord.Type.Primary, "pk", vGroup5, vElements5.subList(0, 1)); createKey(KeyRecord.Type.Index, "idx", vGroup5, vElements5.subList(1, 2)); + //no pk + QueryNode vTrans6 = new QueryNode("VGroup6", "SELECT x, 'z' || substring(x, 2) as y FROM matsrc"); //$NON-NLS-1$ //$NON-NLS-2$ + Table vGroup6 = createVirtualGroup("VGroup6", virtModel, vTrans6); //$NON-NLS-1$ + vGroup6.setMaterialized(true); + List<Column> vElements6 = createElements(vGroup6, + new String[] { "x", "y" }, //$NON-NLS-1$ + new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING}); + + createKey(KeyRecord.Type.Index, "idx", vGroup6, vElements6.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$