[
https://issues.apache.org/jira/browse/PHOENIX-7258?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824542#comment-17824542
]
ASF GitHub Bot commented on PHOENIX-7258:
-----------------------------------------
virajjasani commented on code in PR #1851:
URL: https://github.com/apache/phoenix/pull/1851#discussion_r1516856703
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificTablesDDLIT.java:
##########
@@ -616,6 +621,52 @@ public void testTableMetadataScan() throws Exception {
conn.close();
}
}
+
+ @Test
+ public void testIndexHintWithTenantView() throws Exception {
+ String schemaName = generateUniqueName();
+ String dataTableName = generateUniqueName();
+ String fullDataTableName = SchemaUtil.getTableName(schemaName,
dataTableName);
+ String viewName = generateUniqueName();
+ String fullViewName = SchemaUtil.getTableName(schemaName, viewName);
+ String viewIndexName = generateUniqueName();
+ try(Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ String createDataTable = "create table " + fullDataTableName + "
(orgid varchar(10) not null, "
+ + "id1 varchar(10) not null, id2 varchar(10) not null, id3
integer not null, "
+ + "val1 varchar(10), val2 varchar(10) " +
+ "CONSTRAINT PK PRIMARY KEY (orgid, id1, id2, id3))
MULTI_TENANT=true";
+ stmt.execute(createDataTable);
+ stmt.execute("create view " + fullViewName + " as select * from "
+ fullDataTableName);
+ stmt.execute("create index " + viewIndexName + " on " +
fullViewName + "(id3, id2, id1) include (val1, val2)");
+ }
+ try(Connection conn =
DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL);
+ Statement stmt = conn.createStatement()) {
+ String grandChildViewName = generateUniqueName();
+ String fullGrandChildViewName =
SchemaUtil.getTableName(schemaName, grandChildViewName);
+ stmt.execute("create view " + fullGrandChildViewName + " as select
* from " + fullViewName);
+ PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class);
+ pconn.getTableNoCache(pconn.getTenantId(), fullGrandChildViewName);
+ stmt.execute("upsert into " + fullGrandChildViewName + " values
('a1', 'a2', 3, 'a4', 'a5')");
+ conn.commit();
+ stmt.execute("upsert into " + fullGrandChildViewName + " values
('b1', 'b2', 3, 'b4', 'b5')");
+ conn.commit();
+ String physicalViewIndexTableName =
MetaDataUtil.getViewIndexPhysicalName(fullDataTableName);
+ TableName viewIndexHBaseTable =
TableName.valueOf(physicalViewIndexTableName);
+ TestUtil.assertRawRowCount(conn, viewIndexHBaseTable, 2);
+ String sql = "SELECT /*+ INDEX(" + fullGrandChildViewName + " " +
viewIndexName + ")*/ "
+ + "val2, id2, val1, id3, id1 FROM " +
fullGrandChildViewName
+ + " WHERE id2 = 'a2' AND (id1 = 'a1' OR id1 = 'b1') AND
id3 = 3";
+ ResultSet rs = stmt.executeQuery("EXPLAIN " + sql);
+ String actualQueryPlan = QueryUtil.getExplainPlan(rs);
+ String expectedQueryPlan = "CLIENT PARALLEL 1-WAY POINT LOOKUP ON
2 KEYS OVER "
+ + physicalViewIndexTableName;
Review Comment:
Could you please also use the same query without hint and assert that the
Explain plan now uses 1-WAY POINT LOOKUP on the base table?
> Query Optimizer should pick Index hint even for point lookup queries
> --------------------------------------------------------------------
>
> Key: PHOENIX-7258
> URL: https://issues.apache.org/jira/browse/PHOENIX-7258
> Project: Phoenix
> Issue Type: Improvement
> Affects Versions: 5.2.0, 5.1.3
> Reporter: Viraj Jasani
> Assignee: Sanjeet Malhotra
> Priority: Major
>
> For better performance, user can create covered indexes such that the indexed
> columns are same as composite primary key of the data table, but with
> different order. For instance, create data table with columns PK1, PK2, PK3,
> C1, C2 columns with primary key being PK1, Pk2, PK3. In order to get better
> performance from HBase block caching, if the data with same PK3 values are
> going to reside as close to each other as possible, we can create an index on
> PK3, PK2, PK1 and also include columns C1 and C2.
> For point lookups on the data table, it might still be helpful to query index
> table depending on the usecase. We should allow using index hint to query the
> index table for point lookup.
> When the query optimizer identifies that the query is point lookup for the
> data table and if the "stop at best plan" is true, then it immediately
> returns without checking the hint. We should check for hint and if applicable
> index based hint plan is identified, use it.
>
> Assuming getHintedPlanIfApplicable() retrieves hinted plan, it can look
> something like:
> {code:java}
> if (dataPlan.getContext().getScanRanges().isPointLookup() && stopAtBestPlan
> && dataPlan.isApplicable()) {
> if (indexes.isEmpty() || select.getHint().getHint(Hint.INDEX) == null) {
> return Collections.singletonList(dataPlan);
> }
> QueryPlan hintedPlan = getHintedPlanIfApplicable(dataPlan, statement,
> targetColumns,
> parallelIteratorFactory, select, indexes);
> if (hintedPlan != null) {
> PTable index = hintedPlan.getTableRef().getTable();
> if (hintedPlan.isApplicable() && (index.getIndexWhere() == null
> || isPartialIndexUsable(select, dataPlan, index))) {
> return Collections.singletonList(hintedPlan);
> }
> }
> return Collections.singletonList(dataPlan);
> } {code}
> We still need to be optimal i.e. if the hinted index plan is not applicable
> or useful, we still need to immediately return the data plan of point lookup.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
