Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-21336

HMS Index PCS_STATS_IDX too long for Oracle when NLS_LENGTH_SEMANTICS=char

Log workAgile BoardRank to TopRank to BottomVotersWatch issueWatchersCreate sub-taskConvert to sub-taskMoveLinkCloneLabelsUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 3.0.0
    • Fix Version/s: 3.2.0, 4.0.0
    • Component/s: Metastore
    • Labels:
      None

      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> 
      

        Attachments

        1. HIVE-21336.2.patch
          1 kB
          Naveen Gangam
        2. HIVE-21336.3.patch
          2 kB
          Naveen Gangam
        3. HIVE-21336.4.patch
          5 kB
          Naveen Gangam
        4. HIVE-21336.patch
          0.8 kB
          Naveen Gangam

          Activity

          $i18n.getText('security.level.explanation', $currentSelection) Viewable by All Users
          Cancel

            People

            • Assignee:
              ngangam Naveen Gangam Assign to me
              Reporter:
              ngangam Naveen Gangam

              Dates

              • Created:
                Updated:
                Resolved:

                Issue deployment