@Override public ExplainPlan getExplainPlan() throws SQLException { List<String> steps = new ArrayList<String>(); steps.add("UNION ALL OVER " + this.plans.size() + " QUERIES"); ResultIterator iterator = iterator(); iterator.explain(steps); // Indent plans steps nested under union, except last client-side merge/concat step (if there is one) int offset = !orderBy.getOrderByExpressions().isEmpty() && limit != null ? 2 : limit != null ? 1 : 0; for (int i = 1 ; i < steps.size()-offset; i++) { steps.set(i, " " + steps.get(i)); } return new ExplainPlan(steps); }
/** * Whether or not the query plan has any order by expressions. * @param plan * @return */ public static boolean hasOrderBy(QueryPlan plan) { checkNotNull(plan); List<OrderByExpression> orderBys = plan.getOrderBy().getOrderByExpressions(); return orderBys != null && !orderBys.isEmpty(); }
private static boolean isOffsetPossibleOnServer(StatementContext context, OrderBy orderBy, Integer offset, boolean isSalted, IndexType indexType) { return offset != null && orderBy.getOrderByExpressions().isEmpty() && !((isSalted || indexType == IndexType.LOCAL) && ScanUtil.shouldRowsBeInRowKeyOrder(orderBy, context)); }
/** * The method validates the statement passed to the query plan. List of conditions are * <ol> * <li>Is a SELECT statement</li> * <li>doesn't contain ORDER BY expression</li> * <li>doesn't contain LIMIT</li> * <li>doesn't contain GROUP BY expression</li> * <li>doesn't contain DISTINCT</li> * <li>doesn't contain AGGREGATE functions</li> * </ol> * @param queryPlan * @return */ private boolean isValidStatement(final QueryPlan queryPlan) { if(queryPlan.getStatement().getOperation() != PhoenixStatement.Operation.QUERY) { throw new IllegalArgumentException("Query passed isn't a SELECT statement"); } if(!queryPlan.getOrderBy().getOrderByExpressions().isEmpty() || queryPlan.getLimit() != null || (queryPlan.getGroupBy() != null && !queryPlan.getGroupBy().isEmpty()) || queryPlan.getStatement().isDistinct() || queryPlan.getStatement().isAggregate()) { throw new IllegalArgumentException("SELECT statement shouldn't contain DISTINCT or ORDER BY or LIMIT or GROUP BY expressions"); } return true; }
@Override public ResultIterator iterator(ParallelScanGrouper scanGrouper, Scan scan) throws SQLException { ResultIterator iterator = delegate.iterator(scanGrouper, scan); if (where != null) { iterator = new FilterResultIterator(iterator, where); } if (!orderBy.getOrderByExpressions().isEmpty()) { // TopN int thresholdBytes = context.getConnection().getQueryServices().getProps().getInt( QueryServices.SPOOL_THRESHOLD_BYTES_ATTRIB, QueryServicesOptions.DEFAULT_SPOOL_THRESHOLD_BYTES); iterator = new OrderedResultIterator(iterator, orderBy.getOrderByExpressions(), thresholdBytes, limit, offset, projector.getEstimatedRowByteSize()); } else { if (offset != null) { iterator = new OffsetResultIterator(iterator, offset); } if (limit != null) { iterator = new LimitingResultIterator(iterator, limit); } } if (context.getSequenceManager().getSequenceCount() > 0) { iterator = new SequenceResultIterator(iterator, context.getSequenceManager()); } return iterator; }
@Test public void testNotOrderByOrderPreserving() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k1 date not null, k2 varchar, k3 varchar, v varchar, constraint pk primary key(k1,k2,k3 desc))"); String[] queries = { "SELECT * FROM T ORDER BY k1,k2 NULLS LAST", "SELECT * FROM T ORDER BY k1,k2, k3 NULLS LAST", "SELECT * FROM T ORDER BY k1,k3", "SELECT * FROM T ORDER BY SUBSTR(TO_CHAR(k1),1,4)", "SELECT * FROM T ORDER BY k2", "SELECT * FROM T ORDER BY INVERT(k1),k3", "SELECT * FROM T ORDER BY CASE WHEN k1 = CURRENT_DATE() THEN 0 ELSE 1 END", "SELECT * FROM T ORDER BY TO_CHAR(k1)", }; String query; for (int i = 0; i < queries.length; i++) { query = queries[i]; QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertFalse("Expected order by not to be compiled out: " + query, plan.getOrderBy().getOrderByExpressions().isEmpty()); } }
@Override public Cost getCost() { Long byteCount = null; try { byteCount = getEstimatedBytesToScan(); } catch (SQLException e) { // ignored. } Double outputBytes = this.accept(new ByteCountVisitor()); if (byteCount == null || outputBytes == null) { return Cost.UNKNOWN; } int parallelLevel = CostUtil.estimateParallelLevel( true, context.getConnection().getQueryServices()); Cost cost = new Cost(0, 0, byteCount); if (!orderBy.getOrderByExpressions().isEmpty()) { Cost orderByCost = CostUtil.estimateOrderByCost(byteCount, outputBytes, parallelLevel); cost = cost.plus(orderByCost); } return cost; }
@Test public void testQueryViewStatementOptimization() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); String fullTableName = SchemaUtil.getTableName(SCHEMA1, generateUniqueName()); String fullViewName1 = SchemaUtil.getTableName(SCHEMA2, generateUniqueName()); String fullViewName2 = SchemaUtil.getTableName(SCHEMA3, generateUniqueName()); String sql = "CREATE TABLE " + fullTableName + " (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, v1 DECIMAL, CONSTRAINT pk PRIMARY KEY (k1, k2))" + tableDDLOptions; conn.createStatement().execute(sql); sql = "CREATE VIEW " + fullViewName1 + " AS SELECT * FROM " + fullTableName; conn.createStatement().execute(sql); sql = "CREATE VIEW " + fullViewName2 + " AS SELECT * FROM " + fullTableName + " WHERE k1 = 1.0"; conn.createStatement().execute(sql); sql = "SELECT * FROM " + fullViewName1 + " order by k1, k2"; PreparedStatement stmt = conn.prepareStatement(sql); QueryPlan plan = PhoenixRuntime.getOptimizedQueryPlan(stmt); assertEquals(0, plan.getOrderBy().getOrderByExpressions().size()); sql = "SELECT * FROM " + fullViewName2 + " order by k1, k2"; stmt = conn.prepareStatement(sql); plan = PhoenixRuntime.getOptimizedQueryPlan(stmt); assertEquals(0, plan.getOrderBy().getOrderByExpressions().size()); }
@SuppressWarnings("deprecation") private static ParallelIteratorFactory buildResultIteratorFactory(StatementContext context, FilterableStatement statement, TableRef tableRef, OrderBy orderBy, Integer limit,Integer offset, boolean allowPageFilter) throws SQLException { if ((isSerial(context, statement, tableRef, orderBy, getEstimateOfDataSizeToScanIfWithinThreshold(context, tableRef.getTable(), QueryUtil.getOffsetLimit(limit, offset)) != null) || isRoundRobinPossible(orderBy, context) || isPacingScannersPossible(context))) { return ParallelIteratorFactory.NOOP_FACTORY; } ParallelIteratorFactory spoolingResultIteratorFactory = new SpoolingResultIterator.SpoolingResultIteratorFactory( context.getConnection().getQueryServices()); // If we're doing an order by then we need the full result before we can do anything, // so we don't bother chunking it. If we're just doing a simple scan then we chunk // the scan to have a quicker initial response. if (!orderBy.getOrderByExpressions().isEmpty()) { return spoolingResultIteratorFactory; } else { return new ChunkedResultIterator.ChunkedResultIteratorFactory( spoolingResultIteratorFactory, context.getConnection().getMutationState(), tableRef); } }
@Override public final ResultIterator iterator(ParallelScanGrouper scanGrouper, Scan scan) throws SQLException { this.iterators = new UnionResultIterators(plans, parentContext); ResultIterator scanner; boolean isOrdered = !orderBy.getOrderByExpressions().isEmpty(); if (isOrdered) { // TopN scanner = new MergeSortTopNResultIterator(iterators, limit, offset, orderBy.getOrderByExpressions()); } else { scanner = new ConcatResultIterator(iterators); if (offset != null) { scanner = new OffsetResultIterator(scanner, offset); } if (limit != null) { scanner = new LimitingResultIterator(scanner, limit); } } return scanner; }
@Override public Cost getCost() { Double inputBytes = this.getDelegate().accept(new ByteCountVisitor()); Double outputBytes = this.accept(new ByteCountVisitor()); if (inputBytes == null || outputBytes == null) { return Cost.UNKNOWN; } int parallelLevel = CostUtil.estimateParallelLevel( false, context.getConnection().getQueryServices()); Cost cost = new Cost(0, 0, 0); if (!orderBy.getOrderByExpressions().isEmpty()) { Cost orderByCost = CostUtil.estimateOrderByCost(inputBytes, outputBytes, parallelLevel); cost = cost.plus(orderByCost); } return super.getCost().plus(cost); }
public static final boolean canQueryBeExecutedSerially(PTable table, OrderBy orderBy, StatementContext context) { /* * If ordering by columns not on the PK axis, we can't execute a query serially because we * need to do a merge sort across all the scans which isn't possible with SerialIterators. * Similar reasoning follows for salted and local index tables when ordering rows in a row * key order. Serial execution is OK in other cases since SerialIterators will execute scans * in the correct order. */ if (!orderBy.getOrderByExpressions().isEmpty() || ((table.getBucketNum() != null || table.getIndexType() == IndexType.LOCAL) && shouldRowsBeInRowKeyOrder( orderBy, context))) { return false; } return true; }
@Override public boolean isRowKeyOrdered() { return groupBy.isEmpty() ? orderBy.getOrderByExpressions().isEmpty() : groupBy.isOrderPreserving(); }
@Test public void testNotOrderByOrderPreservingForAggregation() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE IF NOT EXISTS VA_TEST(ID VARCHAR NOT NULL PRIMARY KEY, VAL1 VARCHAR, VAL2 INTEGER)"); String[] queries = { "select distinct ID, VAL1, VAL2 from VA_TEST where \"ID\" in ('ABC','ABD','ABE','ABF','ABG','ABH','AAA', 'AAB', 'AAC','AAD','AAE','AAF') order by VAL1 ASC" }; String query; for (int i = 0; i < queries.length; i++) { query = queries[i]; QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertFalse("Expected order by not to be compiled out: " + query, plan.getOrderBy().getOrderByExpressions().isEmpty()); } }
@Test public void testOrderByNotDropped() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE foo (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY v"); assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty()); }
private void assertOrderByHasBeenOptimizedOut(Connection conn, String sql) throws SQLException { PreparedStatement stmt = conn.prepareStatement(sql); QueryPlan plan = PhoenixRuntime.getOptimizedQueryPlan(stmt); assertEquals(0, plan.getOrderBy().getOrderByExpressions().size()); }
@Override public boolean isRowKeyOrdered() { return groupBy.isEmpty() ? orderBy.getOrderByExpressions().isEmpty() : groupBy.isOrderPreserving(); }
/** * Selecting underlying scanners in a round-robin fashion is possible if there is no ordering of * rows needed, not even row key order. Also no point doing round robin of scanners if fetch * size is 1. */ public static boolean isRoundRobinPossible(OrderBy orderBy, StatementContext context) throws SQLException { int fetchSize = context.getStatement().getFetchSize(); return fetchSize > 1 && !shouldRowsBeInRowKeyOrder(orderBy, context) && orderBy.getOrderByExpressions().isEmpty(); }
@Test public void testOrderByDropped() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); try{ conn.createStatement().execute("CREATE TABLE foo (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY 'a','b','c'"); assertTrue(plan.getOrderBy().getOrderByExpressions().isEmpty()); } finally { conn.close(); } }
@Test public void testOrderByNotDroppedCompositeKey() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE foo (j INTEGER NOT NULL, k BIGINT NOT NULL, v VARCHAR CONSTRAINT pk PRIMARY KEY (j,k)) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY k,j"); assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty()); }