[
https://issues.apache.org/jira/browse/HIVE-21336?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Naveen Gangam updated HIVE-21336:
---------------------------------
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
{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}
was:
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}
> 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
>
> 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)
