This is an automated email from the ASF dual-hosted git repository.
dengzhhu653 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new 47d02b5f541 HIVE-27493: HMS optimizes the SQL query index of
getPartitionsByFilter method (#4474)
47d02b5f541 is described below
commit 47d02b5f541978f96ea745d10ac8972a3aef0f5b
Author: cxzl25 <[email protected]>
AuthorDate: Wed Oct 28 09:57:42 2026 +0800
HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter
method (#4474)
---
.../metastore-server/src/main/sql/derby/hive-schema-4.3.0.derby.sql | 2 ++
.../src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql | 3 +++
.../metastore-server/src/main/sql/mssql/hive-schema-4.3.0.mssql.sql | 2 ++
.../src/main/sql/mssql/upgrade-4.2.0-to-4.3.0.mssql.sql | 3 +++
.../metastore-server/src/main/sql/mysql/hive-schema-4.3.0.mysql.sql | 1 +
.../src/main/sql/mysql/upgrade-4.2.0-to-4.3.0.mysql.sql | 3 +++
.../metastore-server/src/main/sql/oracle/hive-schema-4.3.0.oracle.sql | 1 +
.../src/main/sql/oracle/upgrade-4.2.0-to-4.3.0.oracle.sql | 3 +++
.../src/main/sql/postgres/hive-schema-4.3.0.postgres.sql | 1 +
.../src/main/sql/postgres/upgrade-4.2.0-to-4.3.0.postgres.sql | 3 +++
10 files changed, 22 insertions(+)
diff --git
a/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.3.0.derby.sql
b/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.3.0.derby.sql
index 39d17cc88be..c360f412086 100644
---
a/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.3.0.derby.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.3.0.derby.sql
@@ -293,6 +293,8 @@ CREATE INDEX "APP"."CONSTRAINTS_PARENT_TBL_ID_INDEX" ON
"APP"."KEY_CONSTRAINTS"(
CREATE INDEX "APP"."CONSTRAINTS_CONSTRAINT_TYPE_INDEX" ON
"APP"."KEY_CONSTRAINTS"("CONSTRAINT_TYPE");
+CREATE INDEX "APP"."PARTITION_KEY_VALS_IDX" ON
"APP"."PARTITION_KEY_VALS"("PART_KEY_VAL");
+
CREATE UNIQUE INDEX "APP"."UNIQUE_WM_RESOURCEPLAN" ON "APP"."WM_RESOURCEPLAN"
("NS", "NAME");
CREATE UNIQUE INDEX "APP"."UNIQUE_WM_POOL" ON "APP"."WM_POOL" ("RP_ID",
"PATH");
diff --git
a/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql
b/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql
index 4aa07213452..37c1b6619d8 100644
---
a/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql
@@ -3,5 +3,8 @@ ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD COLUMN
MRL_CAT_NAME varchar(128) N
CREATE INDEX MIN_HISTORY_WRITE_ID_IDX ON MIN_HISTORY_WRITE_ID (MH_DATABASE,
MH_TABLE, MH_WRITEID);
+-- HIVE-27493
+CREATE INDEX "APP"."PARTITION_KEY_VALS_IDX" ON
"APP"."PARTITION_KEY_VALS"("PART_KEY_VAL");
+
-- This needs to be the last thing done. Insert any changes above this line.
UPDATE "APP".VERSION SET SCHEMA_VERSION='4.3.0', VERSION_COMMENT='Hive release
version 4.3.0' where VER_ID=1;
diff --git
a/standalone-metastore/metastore-server/src/main/sql/mssql/hive-schema-4.3.0.mssql.sql
b/standalone-metastore/metastore-server/src/main/sql/mssql/hive-schema-4.3.0.mssql.sql
index ad2a45fc02b..0fcee0a8984 100644
---
a/standalone-metastore/metastore-server/src/main/sql/mssql/hive-schema-4.3.0.mssql.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/mssql/hive-schema-4.3.0.mssql.sql
@@ -494,6 +494,8 @@ CREATE TABLE PARTITION_KEY_VALS
ALTER TABLE PARTITION_KEY_VALS ADD CONSTRAINT PARTITION_KEY_VALS_PK PRIMARY
KEY (PART_ID,INTEGER_IDX);
+CREATE INDEX PARTITION_KEY_VALS_IDX ON PARTITION_KEY_VALS (PART_KEY_VAL);
+
-- Table PARTITION_KEYS for join relationship
CREATE TABLE PARTITION_KEYS
(
diff --git
a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-4.2.0-to-4.3.0.mssql.sql
b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-4.2.0-to-4.3.0.mssql.sql
index 58a884a52c7..a69be7a4541 100644
---
a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-4.2.0-to-4.3.0.mssql.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-4.2.0-to-4.3.0.mssql.sql
@@ -5,6 +5,9 @@ ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD MRL_CAT_NAME
nvarchar(128) NOT NUL
CREATE INDEX MIN_HISTORY_WRITE_ID_IDX ON MIN_HISTORY_WRITE_ID (MH_DATABASE,
MH_TABLE, MH_WRITEID);
+-- HIVE-27493
+CREATE INDEX PARTITION_KEY_VALS_IDX ON PARTITION_KEY_VALS (PART_KEY_VAL);
+
-- These lines need to be last. Insert any changes above.
UPDATE VERSION SET SCHEMA_VERSION='4.3.0', VERSION_COMMENT='Hive release
version 4.3.0' where VER_ID=1;
SELECT 'Finished upgrading MetaStore schema from 4.2.0 to 4.3.0' AS MESSAGE;
diff --git
a/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.3.0.mysql.sql
b/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.3.0.mysql.sql
index 03e37811f6c..c7c19ef2470 100644
---
a/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.3.0.mysql.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.3.0.mysql.sql
@@ -247,6 +247,7 @@ CREATE TABLE IF NOT EXISTS `PARTITION_KEY_VALS` (
`INTEGER_IDX` int(11) NOT NULL,
PRIMARY KEY (`PART_ID`,`INTEGER_IDX`),
KEY `PARTITION_KEY_VALS_N49` (`PART_ID`),
+ KEY `PARTITION_KEY_VALS_N50` (`PART_KEY_VAL`),
CONSTRAINT `PARTITION_KEY_VALS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES
`PARTITIONS` (`PART_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
diff --git
a/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.2.0-to-4.3.0.mysql.sql
b/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.2.0-to-4.3.0.mysql.sql
index 5149dce33cf..29582081a38 100644
---
a/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.2.0-to-4.3.0.mysql.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.2.0-to-4.3.0.mysql.sql
@@ -5,6 +5,9 @@ ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD MRL_CAT_NAME
varchar(128) NOT NUL
CREATE INDEX MIN_HISTORY_WRITE_ID_IDX ON MIN_HISTORY_WRITE_ID (MH_DATABASE,
MH_TABLE, MH_WRITEID);
+-- HIVE-27493
+CREATE INDEX PARTITION_KEY_VALS_N50 ON PARTITION_KEY_VALS (PART_KEY_VAL) USING
BTREE;
+
-- These lines need to be last. Insert any changes above.
UPDATE VERSION SET SCHEMA_VERSION='4.3.0', VERSION_COMMENT='Hive release
version 4.3.0' where VER_ID=1;
SELECT 'Finished upgrading MetaStore schema from 4.2.0 to 4.3.0' AS MESSAGE;
diff --git
a/standalone-metastore/metastore-server/src/main/sql/oracle/hive-schema-4.3.0.oracle.sql
b/standalone-metastore/metastore-server/src/main/sql/oracle/hive-schema-4.3.0.oracle.sql
index 33958a17852..e697e668aeb 100644
---
a/standalone-metastore/metastore-server/src/main/sql/oracle/hive-schema-4.3.0.oracle.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/oracle/hive-schema-4.3.0.oracle.sql
@@ -717,6 +717,7 @@ CREATE INDEX COLUMNS_V2_N49 ON COLUMNS_V2 (CD_ID);
ALTER TABLE PARTITION_KEY_VALS ADD CONSTRAINT PARTITION_KEY_VALS_FK1 FOREIGN
KEY (PART_ID) REFERENCES PARTITIONS (PART_ID) INITIALLY DEFERRED ;
CREATE INDEX PARTITION_KEY_VALS_N49 ON PARTITION_KEY_VALS (PART_ID);
+CREATE INDEX PARTITION_KEY_VALS_N50 ON PARTITION_KEY_VALS (PART_KEY_VAL);
-- Constraints for table DBS for class(es)
[org.apache.hadoop.hive.metastore.model.MDatabase]
diff --git
a/standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.2.0-to-4.3.0.oracle.sql
b/standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.2.0-to-4.3.0.oracle.sql
index bb210a6623c..7eab44cea11 100644
---
a/standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.2.0-to-4.3.0.oracle.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.2.0-to-4.3.0.oracle.sql
@@ -5,6 +5,9 @@ ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD (MRL_CAT_NAME
VARCHAR2(128) DEFAUL
CREATE INDEX MIN_HISTORY_WRITE_ID_IDX ON MIN_HISTORY_WRITE_ID (MH_DATABASE,
MH_TABLE, MH_WRITEID);
+-- HIVE-27493
+CREATE INDEX PARTITION_KEY_VALS_N50 ON PARTITION_KEY_VALS (PART_KEY_VAL);
+
-- These lines need to be last. Insert any changes above.
UPDATE VERSION SET SCHEMA_VERSION='4.3.0', VERSION_COMMENT='Hive release
version 4.3.0' where VER_ID=1;
SELECT 'Finished upgrading MetaStore schema from 4.2.0 to 4.3.0' AS Status
from dual;
diff --git
a/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.3.0.postgres.sql
b/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.3.0.postgres.sql
index 10eb3892326..74a1675f908 100644
---
a/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.3.0.postgres.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.3.0.postgres.sql
@@ -1147,6 +1147,7 @@ CREATE INDEX "PARTITION_KEYS_N49" ON "PARTITION_KEYS"
USING btree ("TBL_ID");
--
CREATE INDEX "PARTITION_KEY_VALS_N49" ON "PARTITION_KEY_VALS" USING btree
("PART_ID");
+CREATE INDEX "PARTITION_KEY_VALS_N50" ON "PARTITION_KEY_VALS" USING btree
("PART_KEY_VAL");
--
diff --git
a/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-4.2.0-to-4.3.0.postgres.sql
b/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-4.2.0-to-4.3.0.postgres.sql
index bdd28ce2029..0b3b8e39191 100644
---
a/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-4.2.0-to-4.3.0.postgres.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-4.2.0-to-4.3.0.postgres.sql
@@ -5,6 +5,9 @@ ALTER TABLE "MATERIALIZATION_REBUILD_LOCKS" ADD COLUMN
"MRL_CAT_NAME" varchar(12
CREATE INDEX "MIN_HISTORY_WRITE_ID_IDX" ON "MIN_HISTORY_WRITE_ID"
("MH_DATABASE", "MH_TABLE", "MH_WRITEID");
+-- HIVE-27493
+CREATE INDEX "PARTITION_KEY_VALS_N50" ON "PARTITION_KEY_VALS" USING btree
("PART_KEY_VAL");
+
-- These lines need to be last. Insert any changes above.
UPDATE "VERSION" SET "SCHEMA_VERSION"='4.3.0', "VERSION_COMMENT"='Hive release
version 4.3.0' where "VER_ID"=1;
SELECT 'Finished upgrading MetaStore schema from 4.2.0 to 4.3.0';