Repository: incubator-ranger Updated Branches: refs/heads/ranger-0.5 082d0f282 -> 9075f8937
RANGER-843: Add indexes to improve DB query performance Project: http://git-wip-us.apache.org/repos/asf/incubator-ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ranger/commit/9075f893 Tree: http://git-wip-us.apache.org/repos/asf/incubator-ranger/tree/9075f893 Diff: http://git-wip-us.apache.org/repos/asf/incubator-ranger/diff/9075f893 Branch: refs/heads/ranger-0.5 Commit: 9075f893726214232a2945c87941615b018398ac Parents: 082d0f2 Author: Gautam Borad <[email protected]> Authored: Tue Feb 23 11:02:59 2016 +0530 Committer: Gautam Borad <[email protected]> Committed: Tue Feb 23 11:20:32 2016 +0530 ---------------------------------------------------------------------- .../db/mysql/patches/019-create-indexes.sql | 38 ++++++ .../db/oracle/patches/019-create-indexes.sql | 38 ++++++ .../db/postgres/patches/019-create-indexes.sql | 42 ++++++ .../sqlanywhere/patches/019-create-indexes.sql | 56 ++++++++ .../db/sqlserver/patches/019-create-indexes.sql | 136 +++++++++++++++++++ 5 files changed, 310 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9075f893/security-admin/db/mysql/patches/019-create-indexes.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/019-create-indexes.sql b/security-admin/db/mysql/patches/019-create-indexes.sql new file mode 100644 index 0000000..5d497d9 --- /dev/null +++ b/security-admin/db/mysql/patches/019-create-indexes.sql @@ -0,0 +1,38 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +CREATE INDEX x_service_config_def_IDX_def_id ON x_service_config_def(def_id); +CREATE INDEX x_resource_def_IDX_def_id ON x_resource_def(def_id); +CREATE INDEX x_access_type_def_IDX_def_id ON x_access_type_def(def_id); +CREATE INDEX x_access_type_def_IDX_grants_atd_id ON x_access_type_def_grants(atd_id); +CREATE INDEX x_context_enricher_def_IDX_def_id ON x_context_enricher_def(def_id); +CREATE INDEX x_enum_def_IDX_def_id ON x_enum_def(def_id); +CREATE INDEX x_enum_element_def_IDX_enum_def_id ON x_enum_element_def(enum_def_id); + +CREATE INDEX x_service_config_map_IDX_service ON x_service_config_map(service); + +CREATE INDEX x_policy_resource_IDX_policy_id ON x_policy_resource(policy_id); +CREATE INDEX x_policy_resource_IDX_res_def_id ON x_policy_resource(res_def_id); +CREATE INDEX x_policy_resource_map_IDX_resource_id ON x_policy_resource_map(resource_id); + +CREATE INDEX x_policy_item_IDX_policy_id ON x_policy_item(policy_id); +CREATE INDEX x_policy_item_access_IDX_policy_item_id ON x_policy_item_access(policy_item_id); +CREATE INDEX x_policy_item_access_IDX_type ON x_policy_item_access(type); +CREATE INDEX x_policy_item_condition_IDX_policy_item_id ON x_policy_item_condition(policy_item_id); +CREATE INDEX x_policy_item_condition_IDX_type ON x_policy_item_condition(type); +CREATE INDEX x_policy_item_user_perm_IDX_policy_item_id ON x_policy_item_user_perm(policy_item_id); +CREATE INDEX x_policy_item_user_perm_IDX_user_id ON x_policy_item_user_perm(user_id); +CREATE INDEX x_policy_item_group_perm_IDX_policy_item_id ON x_policy_item_group_perm(policy_item_id); +CREATE INDEX x_policy_item_group_perm_IDX_group_id ON x_policy_item_group_perm(group_id); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9075f893/security-admin/db/oracle/patches/019-create-indexes.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/019-create-indexes.sql b/security-admin/db/oracle/patches/019-create-indexes.sql new file mode 100644 index 0000000..356cd19 --- /dev/null +++ b/security-admin/db/oracle/patches/019-create-indexes.sql @@ -0,0 +1,38 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +/ +CREATE INDEX x_service_conf_def_IDX_defid ON x_service_config_def(def_id); +CREATE INDEX x_resource_def_IDX_def_id ON x_resource_def(def_id); +CREATE INDEX x_access_type_def_IDX_def_id ON x_access_type_def(def_id); +CREATE INDEX x_atd_grants_IDX_atdid ON x_access_type_def_grants(atd_id); +CREATE INDEX x_cont_enr_def_IDX_defid ON x_context_enricher_def(def_id); +CREATE INDEX x_enum_def_IDX_def_id ON x_enum_def(def_id); +CREATE INDEX x_enum_element_def_IDX_defid ON x_enum_element_def(enum_def_id); + +CREATE INDEX x_service_conf_map_IDX_service ON x_service_config_map(service); + +CREATE INDEX x_policy_res_IDX_policy_id ON x_policy_resource(policy_id); +CREATE INDEX x_policy_res_IDX_res_def_id ON x_policy_resource(res_def_id); +CREATE INDEX x_policy_res_map_IDX_res_id ON x_policy_resource_map(resource_id); + +CREATE INDEX x_policy_item_IDX_policy_id ON x_policy_item(policy_id); +CREATE INDEX x_plc_item_access_IDX_pi_id ON x_policy_item_access(policy_item_id); +CREATE INDEX x_plc_item_access_IDX_type ON x_policy_item_access(type); +CREATE INDEX x_plc_item_cond_IDX_pi_id ON x_policy_item_condition(policy_item_id); +CREATE INDEX x_plc_item_cond_IDX_type ON x_policy_item_condition(type); +CREATE INDEX x_plc_itm_usr_perm_IDX_pi_id ON x_policy_item_user_perm(policy_item_id); +CREATE INDEX x_plc_itm_usr_perm_IDX_user_id ON x_policy_item_user_perm(user_id); +CREATE INDEX x_plc_itm_grp_perm_IDX_pi_id ON x_policy_item_group_perm(policy_item_id); +CREATE INDEX x_plc_itm_grp_perm_IDX_grp_id ON x_policy_item_group_perm(group_id); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9075f893/security-admin/db/postgres/patches/019-create-indexes.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/patches/019-create-indexes.sql b/security-admin/db/postgres/patches/019-create-indexes.sql new file mode 100644 index 0000000..22b3dc8 --- /dev/null +++ b/security-admin/db/postgres/patches/019-create-indexes.sql @@ -0,0 +1,42 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +CREATE INDEX x_service_config_def_IDX_def_id ON x_service_config_def(def_id); +CREATE INDEX x_resource_def_IDX_def_id ON x_resource_def(def_id); +CREATE INDEX x_access_type_def_IDX_def_id ON x_access_type_def(def_id); +CREATE INDEX x_access_type_def_IDX_grants_atd_id ON x_access_type_def_grants(atd_id); +CREATE INDEX x_context_enricher_def_IDX_def_id ON x_context_enricher_def(def_id); +CREATE INDEX x_enum_def_IDX_def_id ON x_enum_def(def_id); +CREATE INDEX x_enum_element_def_IDX_enum_def_id ON x_enum_element_def(enum_def_id); + +CREATE INDEX x_service_config_map_IDX_service ON x_service_config_map(service); + +CREATE INDEX x_policy_resource_IDX_policy_id ON x_policy_resource(policy_id); +CREATE INDEX x_policy_resource_IDX_res_def_id ON x_policy_resource(res_def_id); +CREATE INDEX x_policy_resource_map_IDX_resource_id ON x_policy_resource_map(resource_id); + +CREATE INDEX x_policy_item_IDX_policy_id ON x_policy_item(policy_id); +CREATE INDEX x_policy_item_access_IDX_policy_item_id ON x_policy_item_access(policy_item_id); +CREATE INDEX x_policy_item_access_IDX_type ON x_policy_item_access(type); +CREATE INDEX x_policy_item_condition_IDX_policy_item_id ON x_policy_item_condition(policy_item_id); +CREATE INDEX x_policy_item_condition_IDX_type ON x_policy_item_condition(type); +CREATE INDEX x_policy_item_user_perm_IDX_policy_item_id ON x_policy_item_user_perm(policy_item_id); +CREATE INDEX x_policy_item_user_perm_IDX_user_id ON x_policy_item_user_perm(user_id); +CREATE INDEX x_policy_item_group_perm_IDX_policy_item_id ON x_policy_item_group_perm(policy_item_id); +CREATE INDEX x_policy_item_group_perm_IDX_group_id ON x_policy_item_group_perm(group_id); + +CREATE INDEX x_service_resource_IDX_service_id ON x_service_resource(service_id); +CREATE INDEX x_service_resource_element_IDX_res_id ON x_service_resource_element(res_id); +CREATE INDEX x_service_resource_element_IDX_res_def_id ON x_service_resource_element(res_def_id); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9075f893/security-admin/db/sqlanywhere/patches/019-create-indexes.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlanywhere/patches/019-create-indexes.sql b/security-admin/db/sqlanywhere/patches/019-create-indexes.sql new file mode 100644 index 0000000..128fa7d --- /dev/null +++ b/security-admin/db/sqlanywhere/patches/019-create-indexes.sql @@ -0,0 +1,56 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +CREATE NONCLUSTERED INDEX x_service_config_def_IDX_def_id ON dbo.x_service_config_def(def_id ASC) +GO +CREATE NONCLUSTERED INDEX x_resource_def_IDX_def_id ON dbo.x_resource_def(def_id ASC) +GO +CREATE NONCLUSTERED INDEX x_access_type_def_IDX_def_id ON dbo.x_access_type_def(def_id ASC) +GO +CREATE NONCLUSTERED INDEX x_access_type_def_IDX_grants_atd_id ON dbo.x_access_type_def_grants(atd_id ASC) +GO +CREATE NONCLUSTERED INDEX x_context_enricher_def_IDX_def_id ON dbo.x_context_enricher_def(def_id ASC) +GO +CREATE NONCLUSTERED INDEX x_enum_def_IDX_def_id ON dbo.x_enum_def(def_id ASC) +GO +CREATE NONCLUSTERED INDEX x_enum_element_def_IDX_enum_def_id ON dbo.x_enum_element_def(enum_def_id ASC) +GO +CREATE NONCLUSTERED INDEX x_service_config_map_IDX_service ON dbo.x_service_config_map(service ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_resource_IDX_policy_id ON dbo.x_policy_resource(policy_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_resource_IDX_res_def_id ON dbo.x_policy_resource(res_def_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_resource_map_IDX_resource_id ON dbo.x_policy_resource_map(resource_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_IDX_policy_id ON dbo.x_policy_item(policy_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_access_IDX_policy_item_id ON dbo.x_policy_item_access(policy_item_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_access_IDX_type ON dbo.x_policy_item_access(type ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_condition_IDX_policy_item_id ON dbo.x_policy_item_condition(policy_item_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_condition_IDX_type ON dbo.x_policy_item_condition(type ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_user_perm_IDX_policy_item_id ON dbo.x_policy_item_user_perm(policy_item_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_user_perm_IDX_user_id ON dbo.x_policy_item_user_perm(user_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_group_perm_IDX_policy_item_id ON dbo.x_policy_item_group_perm(policy_item_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_group_perm_IDX_group_id ON dbo.x_policy_item_group_perm(group_id ASC) +GO +exit \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/9075f893/security-admin/db/sqlserver/patches/019-create-indexes.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlserver/patches/019-create-indexes.sql b/security-admin/db/sqlserver/patches/019-create-indexes.sql new file mode 100644 index 0000000..6ace2ce --- /dev/null +++ b/security-admin/db/sqlserver/patches/019-create-indexes.sql @@ -0,0 +1,136 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +GO +CREATE NONCLUSTERED INDEX [x_service_config_def_IDX_def_id] ON [x_service_config_def] +( + [def_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_resource_def_IDX_def_id] ON [x_resource_def] +( + [def_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_access_type_def_IDX_def_id] ON [x_access_type_def] +( + [def_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_access_type_def_IDX_grants_atd_id] ON [x_access_type_def_grants] +( + [atd_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_context_enricher_def_IDX_def_id] ON [x_context_enricher_def] +( + [def_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_enum_def_IDX_def_id] ON [x_enum_def] +( + [def_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_enum_element_def_IDX_enum_def_id] ON [x_enum_element_def] +( + [enum_def_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_service_config_map_IDX_service] ON [x_service_config_map] +( + [service] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_resource_IDX_policy_id] ON [x_policy_resource] +( + [policy_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_resource_IDX_res_def_id] ON [x_policy_resource] +( + [res_def_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_resource_map_IDX_resource_id] ON [x_policy_resource_map] +( + [resource_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_IDX_policy_id] ON [x_policy_item] +( + [policy_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_access_IDX_policy_item_id] ON [x_policy_item_access] +( + [policy_item_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_access_IDX_type] ON [x_policy_item_access] +( + [type] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_condition_IDX_policy_item_id] ON [x_policy_item_condition] +( + [policy_item_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_condition_IDX_type] ON [x_policy_item_condition] +( + [type] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_user_perm_IDX_policy_item_id] ON [x_policy_item_user_perm] +( + [policy_item_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_user_perm_IDX_user_id] ON [x_policy_item_user_perm] +( + [user_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_group_perm_IDX_policy_item_id] ON [x_policy_item_group_perm] +( + [policy_item_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_group_perm_IDX_group_id] ON [x_policy_item_group_perm] +( + [group_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +exit \ No newline at end of file
