public QueryPlan optimize(PhoenixStatement statement, QueryPlan dataPlan) throws SQLException { if (dataPlan.getTableRef() == null) { return dataPlan; } return optimize(dataPlan, statement, Collections.<PColumn>emptyList(), null); }
public ServerCache createServerCache(byte[] cacheId, QueryPlan delegate) throws SQLException, IOException { PTable cacheUsingTable = delegate.getTableRef().getTable(); ConnectionQueryServices services = delegate.getContext().getConnection().getQueryServices(); List<HRegionLocation> locations = services.getAllTableRegions( cacheUsingTable.getPhysicalName().getBytes()); int nRegions = locations.size(); Set<HRegionLocation> servers = new HashSet<>(nRegions); cacheUsingTableMap.put(Bytes.mapKey(cacheId), cacheUsingTable); return new ServerCache(cacheId, servers, new ImmutableBytesWritable( new byte[]{}), services, false); }
@Test public void testChooseIndexWithLongestRowKey() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx1 ON t(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX idx2 ON t(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("IDX2", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromDoubleQuotedHint() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx1 ON t(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX idx2 ON t(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT /*+ INDEX(t \"IDX1\") INDEX(t idx3) */ k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("IDX1", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testIgnoreIndexesBasedOnHint() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx1 ON t(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX idx2 ON t(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT /*+NO_INDEX*/ k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChoosePointLookupOverOrderByDesc() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY DESC, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1, k)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE k = 30 ORDER BY v1, k LIMIT 5"); assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexEvenWithSelectionStar() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1) INCLUDE (v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM t WHERE v1 = 'bar'"); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromOrderBy() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE k > 30 ORDER BY v1 LIMIT 5"); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testRVCAllColsForTableWithSecondaryIndexBasic() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE T (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX IDX ON T(v1, v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); String query = "select * from t where (k, v1, v2) <= ('3', '1', '2')"; QueryPlan plan = stmt.optimizeQuery(query); assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexOverTable() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE v1 = 'bar'"); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseTableForSelection() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT v1,v2 FROM t WHERE v1 = 'bar'"); // Choose T because v2 is not in index assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseTableForDynCols() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t(v3 VARCHAR) WHERE v1 = 'bar'"); assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChoosePointLookupOverOrderByRemoval() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE k = 30 ORDER BY v1 LIMIT 5"); // Prefer assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromOrderByAsc() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY DESC, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1, k)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE k > 30 ORDER BY v1, k LIMIT 5"); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testRVCForTableWithSecondaryIndexBasic() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE T (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX IDX ON T(v1, v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); String query = "select * from t where (v1, v2) <= ('1', '2')"; QueryPlan plan = stmt.optimizeQuery(query); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testRVCUsingPkColsReturnedByPlanShouldUseIndex() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE T (k VARCHAR NOT NULL PRIMARY KEY, v1 CHAR(15), v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX IDX ON T(v1, v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); String query = "select * from t where (v1, v2, k) > ('1', '2', '3')"; QueryPlan plan = stmt.optimizeQuery(query); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromOrderByDesc() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY DESC, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE k > 30 ORDER BY v1, k DESC LIMIT 5"); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromHint() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx1 ON t(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX idx2 ON t(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT /*+ INDEX(t idx1) */ k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("IDX1", plan.getTableRef().getTable().getTableName().getString()); plan = stmt.optimizeQuery("SELECT k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("IDX2", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromCaseSensitiveHint2() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE \"t\" (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX \"idx1\" ON \"t\"(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX \"idx2\" ON \"t\"(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT /*+ INDEX(\"t\" \"idx1\") */ k FROM \"t\" WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("idx1", plan.getTableRef().getTable().getTableName().getString()); plan = stmt.optimizeQuery("SELECT k FROM \"t\" WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("idx2", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testDistinctPrefixOnIntIndex() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 INTEGER, v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT COUNT(DISTINCT v1) FROM t"); assertTrue(plan.getGroupBy().isOrderPreserving()); assertFalse(plan.getGroupBy().getKeyExpressions().isEmpty()); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }