private static int getIndexPosition(List<PTable> indexes, String indexName) { for (int i = 0; i < indexes.size(); i++) { if (indexName.equals(indexes.get(i).getTableName().getString())) { return i; } } return -1; }
private static void syncUpdateCacheFreqForIndexesOfTable(PTable baseTable, PreparedStatement stmt) throws SQLException { for (PTable index : baseTable.getIndexes()) { if (index.getUpdateCacheFrequency() == baseTable.getUpdateCacheFrequency()) { continue; } stmt.setString(2, index.getSchemaName().getString()); stmt.setString(3, index.getTableName().getString()); stmt.setLong(4, baseTable.getUpdateCacheFrequency()); stmt.addBatch(); } }
@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 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()); }
public static byte[] getTableKey(PTable dataTable) { PName tenantId = dataTable.getTenantId(); PName schemaName = dataTable.getSchemaName(); return getTableKey(tenantId == null ? ByteUtil.EMPTY_BYTE_ARRAY : tenantId.getBytes(), schemaName == null ? ByteUtil.EMPTY_BYTE_ARRAY : schemaName.getBytes(), dataTable.getTableName().getBytes()); }
public SharedTableState(PTable table) { this.tenantId = table.getTenantId(); this.schemaName = table.getSchemaName(); this.tableName = table.getTableName(); this.columns = table.getColumns(); this.physicalNames = table.getPhysicalNames(); this.viewIndexIdType = table.getviewIndexIdType(); this.viewIndexId = table.getViewIndexId(); }
@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 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 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()); }