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';

Reply via email to