[HIVE-21336] HMS Index PCS_STATS_IDX too long for Oracle when NLS_LENGTH_SEMANTICS=char

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 3.2.0, 4.0.0-alpha-1
• Component/s: Metastore
• Assignee: Naveen Gangam
• Reporter: Naveen Gangam
• Created: 27/Feb/19 19:22
• Updated: 17/Nov/22 08:51
• Resolved: 13/Mar/19 18:56

Description

CREATE INDEX PCS_STATS_IDX ON PAR T_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME)
Error: ORA-01450: maximum key length (6398) exceeded (state=72000,code=1450)

Customer tried the same DDL in SQLDevloper, and got the same error. This could be a result of combination of DB level settings like the db_block_size, limiting the maximum key length, as per below doc:
http://www.dba-oracle.com/t_ora_01450_maximum_key_length_exceeded.htm

Also NLS_LENGTH_SEMANTICS is by default BYTE, but users can set this at the session level to CHAR, thus reducing the max size of the index length. We have increased the size of the COLUMN_NAME from 128 to 767 (used to be at 1000) and TABLE_NAME from 128 to 256. This by setting

 
CREATE TABLE PART_COL_STATS ( 
CS_ID NUMBER NOT NULL, 
DB_NAME VARCHAR2(128) NOT NULL, 
TABLE_NAME VARCHAR2(256) NOT NULL, 
PARTITION_NAME VARCHAR2(767) NOT NULL, 
COLUMN_NAME VARCHAR2(767) NOT NULL, .... 

CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME); 

Reproducer:

 
SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 27 11:02:16 2019 Copyright (c) 1982, 2011, Oracle. All rights reserved. 
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production 

SQL> select * from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS'; 
PARAMETER 
---------------------------------------------------------------- 
VALUE 
---------------------------------------------------------------- 
NLS_LENGTH_SEMANTICS 
BYTE 

SQL> alter session set NLS_LENGTH_SEMANTICS=CHAR; Session altered. 

SQL> commit; Commit complete. 

SQL> select * from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS'; PARAMETER 
---------------------------------------------------------------- 
VALUE 
---------------------------------------------------------------- 
NLS_LENGTH_SEMANTICS 
CHAR 

SQL> CREATE TABLE PART_COL_STATS (CS_ID NUMBER NOT NULL, DB_NAME VARCHAR2(128) NOT NULL, TABLE_NAME VARCHAR2(256) NOT NULL, PARTITION_NAME VARCHAR2(767) NOT NULL, COLUMN_NAME VARCHAR2(767) NOT NULL); 
Table created. 

SQL> CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME); 

CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME) 
* ERROR at line 1: ORA-01450: maximum key length (6398) exceeded 

SQL> alter session set NLS_LENGTH_SEMANTICS=BYTE; 
Session altered. 

SQL> commit; 
Commit complete. 

SQL> drop table PART_COL_STATS; 
Table dropped. 

SQL> commit; 
Commit complete. 

SQL> CREATE TABLE PART_COL_STATS (CS_ID NUMBER NOT NULL, DB_NAME VARCHAR2(128) NOT NULL, TABLE_NAME VARCHAR2(256) NOT NULL, PARTITION_NAME VARCHAR2(767) NOT NULL, COLUMN_NAME VARCHAR2(767) NOT NULL); 
Table created. 

SQL> CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME); 
Index created. 

SQL> commit; 
Commit complete. 

SQL>