hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yongzhi Chen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-21336) HMS Index PCS_STATS_IDX too long for Oracle when NLS_LENGTH_SEMANTICS=char
Date Fri, 01 Mar 2019 00:53:00 GMT

    [ https://issues.apache.org/jira/browse/HIVE-21336?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16781143#comment-16781143
] 

Yongzhi Chen commented on HIVE-21336:
-------------------------------------

You are saying there is no use case to upgrade from a version with less column size to 4.0,
therefore, it is impossible in a cluster, one HMS with column size in Char, another column
size in byte? Then that part is not an issue. Just make sure all the upgrade scripts do not
have create table or add column statements, or inconsistence may happen. 

> 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
>            Priority: Major
>         Attachments: HIVE-21336.patch
>
>
> 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)

Mime
View raw message