private static void assertIndexUsed (Connection conn, String query, List<Object> binds, String indexName, boolean expectedToBeUsed) throws SQLException { PreparedStatement stmt = conn.prepareStatement("EXPLAIN " + query); for (int i = 0; i < binds.size(); i++) { stmt.setObject(i+1, binds.get(i)); } ResultSet rs = stmt.executeQuery(); String explainPlan = QueryUtil.getExplainPlan(rs); assertEquals(expectedToBeUsed, explainPlan.contains(" SCAN OVER " + indexName)); }
@Test public void testSelectForceRangeScanForEH() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("create table eh (organization_id char(15) not null,parent_id char(15) not null, created_date date not null, entity_history_id char(15) not null constraint pk primary key (organization_id, parent_id, created_date, entity_history_id))"); ResultSet rs = conn.createStatement().executeQuery("explain select /*+ RANGE_SCAN */ ORGANIZATION_ID, PARENT_ID, CREATED_DATE, ENTITY_HISTORY_ID from eh where ORGANIZATION_ID='111111111111111' and SUBSTR(PARENT_ID, 1, 3) = 'foo' and TO_DATE ('2012-0-1 00:00:00') <= CREATED_DATE and CREATED_DATE <= TO_DATE ('2012-11-31 00:00:00') order by ORGANIZATION_ID, PARENT_ID, CREATED_DATE DESC, ENTITY_HISTORY_ID limit 100"); assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER EH ['111111111111111','foo','2011-12-01 00:00:00.000'] - ['111111111111111','fop','2012-12-01 00:00:00.000']\n" + " SERVER FILTER BY (CREATED_DATE >= 2011-11-30 AND CREATED_DATE <= 2012-11-30)\n" + " SERVER TOP 100 ROWS SORTED BY [ORGANIZATION_ID, PARENT_ID, CREATED_DATE DESC, ENTITY_HISTORY_ID]\n" + "CLIENT MERGE SORT",QueryUtil.getExplainPlan(rs)); } }
@Test public void testExplainPlan() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); String query = "EXPLAIN SELECT s.supplier_id, order_id, c.name, i.name, quantity, o.date FROM " + JOIN_ORDER_TABLE + " o LEFT JOIN " + JOIN_CUSTOMER_TABLE + " c ON o.customer_id = c.customer_id AND c.name LIKE 'C%' LEFT JOIN " + JOIN_ITEM_TABLE + " i ON o.item_id = i.item_id RIGHT JOIN " + JOIN_SUPPLIER_TABLE + " s ON s.supplier_id = i.supplier_id WHERE i.name LIKE 'T%'"; ResultSet rs = conn.createStatement().executeQuery(query); assertEquals( "CLIENT PARALLEL 1-WAY FULL SCAN OVER JOIN_SUPPLIER_TABLE\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" + " BUILD HASH TABLE 0\n" + " CLIENT PARALLEL 1-WAY FULL SCAN OVER JOIN_ORDER_TABLE\n" + " PARALLEL EQUI-JOIN 2 HASH TABLES:\n" + " BUILD HASH TABLE 0\n" + " CLIENT PARALLEL 1-WAY FULL SCAN OVER JOIN_CUSTOMER_TABLE\n" + " SERVER FILTER BY NAME LIKE 'C%'\n" + " BUILD HASH TABLE 1\n" + " CLIENT PARALLEL 1-WAY FULL SCAN OVER JOIN_ITEM_TABLE\n" + " AFTER-JOIN SERVER FILTER BY I.NAME LIKE 'T%'", QueryUtil.getExplainPlan(rs)); }
@Test public void testIndexWithNullableDateCol() throws Exception { Properties props = new Properties(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); try { Date date = new Date(System.currentTimeMillis()); createTestTable(); populateTestTable(date); String ddl = "CREATE INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (date_col)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); String query = "SELECT int_pk from " + DATA_TABLE_FULL_NAME ; ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); } finally { conn.close(); } }
@Test public void testJoinWithSkipMergeOptimization() throws Exception { String query = "SELECT s.name FROM " + JOIN_ITEM_TABLE + " i JOIN " + JOIN_ORDER_TABLE + " o ON o.item_id = i.item_id AND quantity < 5000 JOIN " + JOIN_SUPPLIER_TABLE + " s ON i.supplier_id = s.supplier_id"; Properties props = new Properties(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); try { PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); assertEquals(rs.getString(1), "S1"); assertTrue (rs.next()); assertEquals(rs.getString(1), "S1"); assertTrue (rs.next()); assertEquals(rs.getString(1), "S6"); assertTrue (rs.next()); assertEquals(rs.getString(1), "S6"); assertFalse(rs.next()); rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertEquals(plans[2], QueryUtil.getExplainPlan(rs)); } finally { conn.close(); } }
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + DATA_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); rs = conn.createStatement().executeQuery(query); assertTrue(rs.next());
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST.IDX", QueryUtil.getExplainPlan(rs));
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " +INDEX_TABLE_FULL_NAME + " [~'1']\n" + " SERVER TOP -1 ROWS SORTED BY [V1]\n" + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
assertEquals(plans[0], QueryUtil.getExplainPlan(rs)); assertEquals(plans[1], QueryUtil.getExplainPlan(rs)); } finally { conn.close();
("CLIENT PARALLEL 4-WAY SKIP SCAN ON 4 KEYS OVER I [0,~'y'] - [3,~'y']\n" + "CLIENT MERGE SORT"); assertEquals(expectedPlan,QueryUtil.getExplainPlan(rs)); ("CLIENT PARALLEL 4-WAY SKIP SCAN ON 4 RANGES OVER I [0,*] - [3,~'x']\n" + "CLIENT MERGE SORT"); assertEquals(expectedPlan,QueryUtil.getExplainPlan(rs)); " SERVER TOP -1 ROWS SORTED BY [K]\n" + "CLIENT MERGE SORT"); assertEquals(expectedPlan,QueryUtil.getExplainPlan(rs)); "CLIENT MERGE SORT\n" + "CLIENT 2 ROW LIMIT"; assertEquals(expectedPlan,QueryUtil.getExplainPlan(rs));
ResultSet ers = upsertStmt.executeQuery(); assertTrue(ers.next()); String explainPlan = QueryUtil.getExplainPlan(ers); assertTrue(explainPlan.contains(" SCAN OVER " + indexName));
("CLIENT PARALLEL 4-WAY SKIP SCAN ON 4 KEYS OVER " + INDEX_TABLE_FULL_NAME + " [0,~'y'] - [3,~'y']\n" + "CLIENT MERGE SORT"); assertEquals(expectedPlan,QueryUtil.getExplainPlan(rs)); ("CLIENT PARALLEL 4-WAY SKIP SCAN ON 4 RANGES OVER " + INDEX_TABLE_FULL_NAME + " [0,*] - [3,~'x']\n" + "CLIENT MERGE SORT"); assertEquals(expectedPlan,QueryUtil.getExplainPlan(rs)); " SERVER TOP -1 ROWS SORTED BY [K]\n" + "CLIENT MERGE SORT"); assertEquals(expectedPlan,QueryUtil.getExplainPlan(rs)); "CLIENT MERGE SORT\n" + "CLIENT 2 ROW LIMIT"; assertEquals(expectedPlan,QueryUtil.getExplainPlan(rs));
"CLIENT MERGE SORT", QueryUtil.getExplainPlan(explainPlan)); ResultSet rs = statement.executeQuery();
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
ResultSet rs = statement.executeQuery("EXPLAIN " + query); assertEquals(query, plan, QueryUtil.getExplainPlan(rs)); } catch (Exception e) { throw new Exception(query + ": "+ e.getMessage(), e);