hive-issues mailing list archives

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

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

Hive QA commented on HIVE-21336:
--------------------------------

| (/) *{color:green}+1 overall{color}* |
\\
\\
|| Vote || Subsystem || Runtime || Comment ||
|| || || || {color:brown} Prechecks {color} ||
| {color:green}+1{color} | {color:green} @author {color} | {color:green}  0m  0s{color} |
{color:green} The patch does not contain any @author tags. {color} |
|| || || || {color:brown} master Compile Tests {color} ||
| {color:green}+1{color} | {color:green} mvninstall {color} | {color:green}  9m 36s{color}
| {color:green} master passed {color} |
| {color:green}+1{color} | {color:green} javadoc {color} | {color:green}  0m 22s{color} |
{color:green} master passed {color} |
|| || || || {color:brown} Patch Compile Tests {color} ||
| {color:green}+1{color} | {color:green} mvninstall {color} | {color:green}  0m 35s{color}
| {color:green} the patch passed {color} |
| {color:green}+1{color} | {color:green} whitespace {color} | {color:green}  0m  0s{color}
| {color:green} The patch has no whitespace issues. {color} |
| {color:green}+1{color} | {color:green} javadoc {color} | {color:green}  0m 23s{color} |
{color:green} the patch passed {color} |
|| || || || {color:brown} Other Tests {color} ||
| {color:green}+1{color} | {color:green} asflicense {color} | {color:green}  0m 15s{color}
| {color:green} The patch does not generate ASF License warnings. {color} |
| {color:black}{color} | {color:black} {color} | {color:black} 11m 31s{color} | {color:black}
{color} |
\\
\\
|| Subsystem || Report/Notes ||
| Optional Tests |  asflicense  javac  javadoc  |
| uname | Linux hiveptest-server-upstream 3.16.0-4-amd64 #1 SMP Debian 3.16.36-1+deb8u1 (2016-09-03)
x86_64 GNU/Linux |
| Build tool | maven |
| Personality | /data/hiveptest/working/yetus_PreCommit-HIVE-Build-16356/dev-support/hive-personality.sh
|
| git revision | master / 9dc28db |
| Default Java | 1.8.0_111 |
| modules | C: standalone-metastore/metastore-server U: standalone-metastore/metastore-server
|
| Console output | http://104.198.109.242/logs//PreCommit-HIVE-Build-16356/yetus.txt |
| Powered by | Apache Yetus    http://yetus.apache.org |


This message was automatically generated.



> 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.2.patch, HIVE-21336.3.patch, 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