public QueryPlan optimizeQuery() throws SQLException { throwIfUnboundParameters(); return optimizeQuery(statement); }
/** * Returns the opitmized query plan used by phoenix for executing the sql. * @param stmt to return the plan for * @throws SQLException */ public static QueryPlan getOptimizedQueryPlan(PreparedStatement stmt) throws SQLException { checkNotNull(stmt); QueryPlan plan = stmt.unwrap(PhoenixPreparedStatement.class).optimizeQuery(); return plan; }
public static QueryPlan getOptimizeQueryPlan(Connection conn,String sql) throws SQLException { PhoenixPreparedStatement statement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class); QueryPlan queryPlan = statement.optimizeQuery(sql); queryPlan.iterator(); return queryPlan; }
private static QueryPlan getQueryPlan(Connection conn,String sql) throws SQLException { PhoenixPreparedStatement statement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class); QueryPlan queryPlan = statement.optimizeQuery(sql); queryPlan.iterator(); return queryPlan; }
private QueryPlan getOptimizedQueryPlan(String query, List<Object> binds) throws SQLException { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { PhoenixPreparedStatement statement = conn.prepareStatement(query).unwrap(PhoenixPreparedStatement.class); for (Object bind : binds) { statement.setObject(1, bind); } QueryPlan plan = statement.optimizeQuery(query); return plan; } finally { conn.close(); } }
@Test public void testTypeMismatch() throws SQLException { String tenantId = "000000000000001"; String query = "select * from atable where organization_id='" + tenantId + "' and a_integer > 'foo'"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); try { pstmt.optimizeQuery(); fail(); } catch (SQLException e) { assertTrue(e.getMessage().contains("Type mismatch")); } }
@Test public void testDegenerateRowKeyFilter() throws SQLException { String keyPrefix = "foobar"; String query = "select * from atable where substr(entity_id,1,3)=?"; List<Object> binds = Arrays.<Object>asList(keyPrefix); PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); bindParams(pstmt, binds); QueryPlan plan = pstmt.optimizeQuery(); // Degenerate b/c "foobar" is more than 3 characters assertDegenerate(plan.getContext()); }
@Test public void testAndFalseFilter() throws SQLException { String tenantId = "000000000000001"; String query = "select * from atable where organization_id='" + tenantId + "' and a_integer=0 and 2=3"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); assertDegenerate(plan.getContext()); }
@Test public void testFalseFilter() throws SQLException { String tenantId = "000000000000001"; String query = "select * from atable where organization_id='" + tenantId + "' and 2=3"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); assertDegenerate(plan.getContext()); }
@Test public void testDegenerateBiggerThanMaxLengthVarchar() throws SQLException { byte[] tooBigValue = new byte[101]; Arrays.fill(tooBigValue, (byte)50); String aString = (String) PVarchar.INSTANCE.toObject(tooBigValue); String query = "select * from atable where a_string=?"; List<Object> binds = Arrays.<Object>asList(aString); PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); bindParams(pstmt, binds); QueryPlan plan = pstmt.optimizeQuery(); // Degenerate b/c a_string length is 100 assertDegenerate(plan.getContext()); }
@Test public void testScanCaching_Default() throws SQLException { String query = "select * from atable where a_integer=0"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); Configuration config = HBaseConfiguration.create(); int defaultScannerCacheSize = config.getInt(HConstants.HBASE_CLIENT_SCANNER_CACHING, HConstants.DEFAULT_HBASE_CLIENT_SCANNER_CACHING); assertEquals(defaultScannerCacheSize, pstmt.getFetchSize()); assertEquals(defaultScannerCacheSize, scan.getCaching()); }
@Test public void testPaddedStartStopKey() throws SQLException { String tenantId = "000000000000001"; String keyPrefix = "fo"; String query = "select * from atable where organization_id=? AND entity_id=?"; List<Object> binds = Arrays.<Object>asList(tenantId,keyPrefix); PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); bindParams(pstmt, binds); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); byte[] expectedStartRow = ByteUtil.concat(Bytes.toBytes(tenantId), StringUtil.padChar(Bytes.toBytes(keyPrefix), 15)); assertArrayEquals(expectedStartRow,scan.getStartRow()); assertArrayEquals(ByteUtil.nextKey(expectedStartRow),scan.getStopRow()); }
private void helpTestToNumberFilter(String toNumberClause, BigDecimal expectedDecimal) throws Exception { String tenantId = "000000000000001"; String query = "select * from atable where organization_id='" + tenantId + "' and x_decimal >= " + toNumberClause; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); Filter filter = scan.getFilter(); assertEquals( singleKVFilter(constantComparison( CompareOp.GREATER_OR_EQUAL, X_DECIMAL, expectedDecimal)), filter); }
@Test public void testScanCaching_CustomFetchSizeOnStatement() throws SQLException { String query = "select * from atable where a_integer=0"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); final int FETCH_SIZE = 25; pstmt.setFetchSize(FETCH_SIZE); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); assertEquals(FETCH_SIZE, pstmt.getFetchSize()); assertEquals(FETCH_SIZE, scan.getCaching()); } }
@Test public void testMultiColumnEqualFilter() throws SQLException { String tenantId = "000000000000001"; String query = "select * from atable where organization_id='" + tenantId + "' and a_string=b_string"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); Filter filter = scan.getFilter(); assertEquals( multiEncodedKVFilter(columnComparison( CompareOp.EQUAL, A_STRING, B_STRING), TWO_BYTE_QUALIFIERS), filter); }
@Test public void testRHSLiteral() throws SQLException { String tenantId = "000000000000001"; String query = "select * from atable where organization_id='" + tenantId + "' and 0 >= a_integer"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); Filter filter = scan.getFilter(); assertEquals( singleKVFilter(constantComparison( CompareOp.LESS_OR_EQUAL, A_INTEGER, 0)), filter); }
@Test public void testPaddedRowKeyFilter() throws SQLException { String keyPrefix = "fo"; String query = "select * from atable where entity_id=?"; List<Object> binds = Arrays.<Object>asList(keyPrefix); PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); bindParams(pstmt, binds); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); assertEquals(0,scan.getStartRow().length); assertEquals(0,scan.getStopRow().length); assertNotNull(scan.getFilter()); }
@Test public void testCollapseFunctionToNull() throws SQLException { String query = "select * from atable where substr(entity_id,null) = 'foo'"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); Filter filter = scan.getFilter(); assertNull(filter); assertArrayEquals(scan.getStartRow(),KeyRange.EMPTY_RANGE.getLowerRange()); assertArrayEquals(scan.getStopRow(),KeyRange.EMPTY_RANGE.getUpperRange()); }
@Test public void testTrueFilter() throws SQLException { String tenantId = "000000000000001"; String query = "select * from atable where organization_id='" + tenantId + "' and 2<=2"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); assertNull(scan.getFilter()); byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId); assertArrayEquals(startRow, scan.getStartRow()); byte[] stopRow = startRow; assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow()); }
@Test public void testOrTrueFilter() throws SQLException { String tenantId = "000000000000001"; String query = "select * from atable where organization_id='" + tenantId + "' and (a_integer=0 or 3>2)"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); Filter filter = scan.getFilter(); assertNull(filter); byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId); assertArrayEquals(startRow, scan.getStartRow()); byte[] stopRow = startRow; assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow()); }