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

["Naveen Gangam (JIRA)" <ji...@apache.org>]

Naveen Gangam created HIVE-21336:
------------------------------------

             Summary: HMS Index PCS_STATS_IDX too long for Oracle when NLS_LENGTH_SEMANTICS=char
                 Key: HIVE-21336
                 URL: https://issues.apache.org/jira/browse/HIVE-21336
             Project: Hive
          Issue Type: Improvement
          Components: Metastore
    Affects Versions: 3.0.0
            Reporter: Naveen Gangam
            Assignee: Naveen Gangam


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 

{code} 
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); 
{code} 

Reproducer: 

{code} 
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> 
{code}



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