[
https://issues.apache.org/jira/browse/HIVE-22271?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
wenjun ma reassigned HIVE-22271:
--------------------------------
Assignee: wenjun ma
> Create index on the TBL_COL_PRIVS table for the columns COLUMN_NAME,
> PRINCIPAL_NAME, PRINCIPAL_TYPE and TBL_ID
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-22271
> URL: https://issues.apache.org/jira/browse/HIVE-22271
> Project: Hive
> Issue Type: Bug
> Components: Metastore
> Reporter: Marta Kuczora
> Assignee: wenjun ma
> Priority: Major
>
> In one of the escalations for HDP-3.1.0 we found that the table privilege
> checks could be very slow and these checks could be speed up by defining an
> INDEX on the TBL_COL_PRIVS table for the following columns:
> COLUMN_NAME,PRINCIPAL_NAME,PRINCIPAL_TYPE,TBL_ID
> In the MYSQL slow query log, we found that the following query is executed
> slowly:
> {noformat}
> SELECT DISTINCT
> 'org.apache.hadoop.hive.metastore.model.MTableColumnPrivilege' AS
> `NUCLEUS_TYPE`,`A0`.`AUTHORIZER`,`A0`.`COLUMN_NAME`,`A0`.`CREATE_TIME`,`A0`.`GRANT_OPTION`,`A0`.`GRANTOR`,`A0`.`GRANTOR_TYPE`,`A0`.`PRINCIPAL_NAME`,`A0`.`PRINCIPAL_TYPE`,`A0`.`TBL_COL_PRIV`,`A0`.`TBL_COLUMN_GRANT_ID`
> FROM `TBL_COL_PRIVS` `A0` LEFT OUTER JOIN `TBLS` `B0` ON `A0`.`TBL_ID` =
> `B0`.`TBL_ID` LEFT OUTER JOIN `DBS` `C0` ON `B0`.`DB_ID` = `C0`.`DB_ID` WHERE
> `A0`.`PRINCIPAL_NAME` = 'xxx' AND `A0`.`PRINCIPAL_TYPE` = 'GROUP' AND
> `B0`.`TBL_NAME` = 'xxxx' AND `C0`.`NAME` = 'xxx' AND `C0`.`CTLG_NAME` = 'xxx'
> AND `A0`.`COLUMN_NAME` = 'xxx'
> {noformat}
> When checked the explain plan of the this query, it could be seen that the
> index defined on the TBL_COL_PRIVS table is not used. In the slow query, the
> COLUMN_NAME, PRINCIPAL_NAME, PRINCIPAL_TYPE and TBL_ID columns were used, and
> after creating an index on these columns only, we saw significant performance
> improvement.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
