[jira] [Commented] (SENTRY-2211) Review the index creation for foreign keys

["Na Li (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SENTRY-2211?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16578613#comment-16578613 ] 

Na Li commented on SENTRY-2211:
-------------------------------

The following foreign keys do not have index
{code}
ALTER TABLE "SENTRY_ROLE_DB_PRIVILEGE_MAP"
  ADD CONSTRAINT "SEN_RLE_DB_PRV_MAP_SN_RLE_FK"
  FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") INITIALLY DEFERRED;

ALTER TABLE "SENTRY_ROLE_DB_PRIVILEGE_MAP"
  ADD CONSTRAINT "SEN_RL_DB_PRV_MAP_SN_DB_PRV_FK"
  FOREIGN KEY ("DB_PRIVILEGE_ID") REFERENCES "SENTRY_DB_PRIVILEGE"("DB_PRIVILEGE_ID") INITIALLY DEFERRED;

ALTER TABLE "SENTRY_ROLE_GROUP_MAP"
  ADD CONSTRAINT "SEN_ROLE_GROUP_MAP_SEN_ROLE_FK"
  FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") INITIALLY DEFERRED;

ALTER TABLE "SENTRY_ROLE_GROUP_MAP"
  ADD CONSTRAINT "SEN_ROLE_GROUP_MAP_SEN_GRP_FK"
  FOREIGN KEY ("GROUP_ID") REFERENCES "SENTRY_GROUP"("GROUP_ID") INITIALLY DEFERRED;

ALTER TABLE "SENTRY_ROLE_GM_PRIVILEGE_MAP"
  ADD CONSTRAINT "SEN_RLE_GM_PRV_MAP_SN_RLE_FK"
  FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") INITIALLY DEFERRED;

ALTER TABLE "SENTRY_ROLE_GM_PRIVILEGE_MAP"
  ADD CONSTRAINT "SEN_RL_GM_PRV_MAP_SN_DB_PRV_FK"
  FOREIGN KEY ("GM_PRIVILEGE_ID") REFERENCES "SENTRY_GM_PRIVILEGE"("GM_PRIVILEGE_ID") INITIALLY DEFERRED;

ALTER TABLE "SENTRY_ROLE_USER_MAP"
  ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_ROLE_FK"
  FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") INITIALLY DEFERRED;

ALTER TABLE "SENTRY_ROLE_USER_MAP"
  ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_USER_FK"
  FOREIGN KEY ("USER_ID") REFERENCES "SENTRY_USER"("USER_ID") INITIALLY DEFERRED;
{code}

[~spena][~kkalyan] Do you agree to add index for those foreign keys?

> Review the index creation for foreign keys
> ------------------------------------------
>
>                 Key: SENTRY-2211
>                 URL: https://issues.apache.org/jira/browse/SENTRY-2211
>             Project: Sentry
>          Issue Type: Task
>          Components: Sentry
>    Affects Versions: 2.1.0
>            Reporter: Na Li
>            Assignee: Na Li
>            Priority: Major
>
> It is generally recommended to create an index which leads on the foreign key column(s), to support not only joins between the primary and foreign keys, but also updates and deletes.
> We should review the foreign keys and check if there is an index defined, if not, should we create index.
> More details on The Benefits of Indexing Foreign Keys
> https://sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys
> Indexes on foreign keys
> https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)