Forum Stats

  • 3,767,860 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

Categories

How do I insert in a table with composite hash list partition with one of unknown column value

nparab
nparab Member Posts: 6 Blue Ribbon
in SQL & PL/SQL

Hello I need to insert a row in a partitioned table but I am getting error , I have 2 known column values and third value of the column I am generating by a sequence.

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

I am a doing below

Step 1:

CREATE TABLE "SALES"."ACC1_SID"

  ( "ACC1_ID" CHAR(15 BYTE) NOT NULL ENABLE,

  "SCAL_ID" VARCHAR2(15) ,

  "DELETED" CHAR(1 BYTE) DEFAULT '0' NOT NULL ENABLE

) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

 STORAGE(

 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

 TABLESPACE "D01"

 PARTITION BY HASH ("ACC1_ID")

 SUBPARTITION BY LIST ("SCAL_ID")

 (PARTITION "ACC101"

 TABLESPACE "D01"

 ( SUBPARTITION "SP_P1X1" VALUES ('1')

 TABLESPACE "D01"

 NOCOMPRESS) ,

 PARTITION "ACC102"

 TABLESPACE "D02"

 ( SUBPARTITION "SP_P2X2" VALUES ('2')

 TABLESPACE "D02"

 NOCOMPRESS) ,

 PARTITION "ACC103"

 TABLESPACE "D03"

 ( SUBPARTITION "SP_P3X3" VALUES ('3')

 TABLESPACE "D03"

 NOCOMPRESS ),

 PARTITION "ACC104"

 TABLESPACE "D04"

 ( SUBPARTITION "SP_P4X4" VALUES ('4')

 TABLESPACE "D04"

 NOCOMPRESS) ,

 PARTITION "ACC105"

 TABLESPACE "D05"

 ( SUBPARTITION "SP_P5X5" VALUES ('5')

 TABLESPACE "D05"

 NOCOMPRESS) ,

 PARTITION "ACC106"

 TABLESPACE "D06"

 ( SUBPARTITION "SP_P6X6" VALUES ('6')

 TABLESPACE "D06"

 NOCOMPRESS ),

 PARTITION "ACC107"

 TABLESPACE "D07"

 ( SUBPARTITION "SP_P7X7" VALUES ('7')

 TABLESPACE "D07"

 NOCOMPRESS) ,

 PARTITION "ACC108"

 TABLESPACE "D08"

 ( SUBPARTITION "SP_P8X8" VALUES ('8')

 TABLESPACE "D08"

 NOCOMPRESS) ,

 PARTITION "ACC109"

 TABLESPACE "D09"

 ( SUBPARTITION "SP_P9X9" VALUES ('9')

 TABLESPACE "D09"

 NOCOMPRESS ),

 PARTITION "ACC110"

 TABLESPACE "D10"

 ( SUBPARTITION "SP_P10X10" VALUES ('10')

 TABLESPACE "D10"

 NOCOMPRESS ),

 PARTITION "ACC111"

 TABLESPACE "D11"

 ( SUBPARTITION "SP_P11X11" VALUES ('11')

 TABLESPACE "D11"

 NOCOMPRESS) ,

 PARTITION "ACC112"

 TABLESPACE "D12"

 ( SUBPARTITION "SP_P12X12" VALUES ('12')

 TABLESPACE "D12"

 NOCOMPRESS) ,

 PARTITION "ACC113"

 TABLESPACE "D13"

 ( SUBPARTITION "SP_P13X13" VALUES ('13')

 TABLESPACE "D13"

 NOCOMPRESS ),

 PARTITION "ACC114"

 TABLESPACE "D14"

 ( SUBPARTITION "SP_P14X14" VALUES ('14')

 TABLESPACE "D14"

 NOCOMPRESS) ,

 PARTITION "ACC115"

 TABLESPACE "D15"

 ( SUBPARTITION "SP_P15X15" VALUES ('15')

 TABLESPACE "D15"

 NOCOMPRESS) ,

 PARTITION "ACC116"

 TABLESPACE "D16"

 ( SUBPARTITION "SP_P16X16" VALUES ('16')

 TABLESPACE "D16"

 NOCOMPRESS ),

 PARTITION "ACC117"

 TABLESPACE "D17"

 ( SUBPARTITION "SP_P17X17" VALUES ('17')

 TABLESPACE "D17"

 NOCOMPRESS) ,

 PARTITION "ACC118"

 TABLESPACE "D18"

 ( SUBPARTITION "SP_P18X18" VALUES ('18')

 TABLESPACE "D18"

 NOCOMPRESS) ,

 PARTITION "ACC119"

 TABLESPACE "D19"

 ( SUBPARTITION "SP_P19X19" VALUES ('19')

 TABLESPACE "D19"

 NOCOMPRESS ),

 PARTITION "ACC120"

 TABLESPACE "D20"

 ( SUBPARTITION "SP_P20X20" VALUES ('20')

 TABLESPACE "D20"

 NOCOMPRESS ),

 PARTITION "ACC121"

 TABLESPACE "D21"

 ( SUBPARTITION "SP_P21X21" VALUES ('21')

 TABLESPACE "D21"

 NOCOMPRESS) ,

 PARTITION "ACC122"

 TABLESPACE "D22"

 ( SUBPARTITION "SP_P22X22" VALUES ('22')

 TABLESPACE "D22"

 NOCOMPRESS) ,

 PARTITION "ACC123"

 TABLESPACE "D23"

 ( SUBPARTITION "SP_P23X23" VALUES ('23')

 TABLESPACE "D23"

 NOCOMPRESS ),

 PARTITION "ACC124"

 TABLESPACE "D24"

 ( SUBPARTITION "SP_P24X24" VALUES ('24')

 TABLESPACE "D24"

 NOCOMPRESS) ,

 PARTITION "ACC125"

 TABLESPACE "D25"

 ( SUBPARTITION "SP_P25X25" VALUES ('25')

 TABLESPACE "D25"

 NOCOMPRESS) ,

 PARTITION "ACC126"

 TABLESPACE "D26"

 ( SUBPARTITION "SP_P26X26" VALUES ('26')

 TABLESPACE "D26"

 NOCOMPRESS ),

 PARTITION "ACC127"

 TABLESPACE "D27"

 ( SUBPARTITION "SP_P27X27" VALUES ('27')

 TABLESPACE "D27"

 NOCOMPRESS) ,

 PARTITION "ACC128"

 TABLESPACE "D28"

 ( SUBPARTITION "SP_P28X28" VALUES ('28')

 TABLESPACE "D28"

 NOCOMPRESS) ,

 PARTITION "ACC129"

 TABLESPACE "D29"

 ( SUBPARTITION "SP_P29X29" VALUES ('29')

 TABLESPACE "D29"

 NOCOMPRESS ),

 PARTITION "ACC130"

 TABLESPACE "D30"

 ( SUBPARTITION "SP_P30X30" VALUES ('30')

 TABLESPACE "D30"

 NOCOMPRESS ),

 PARTITION "ACC131"

 TABLESPACE "D31"

 ( SUBPARTITION "SP_P31X31" VALUES ('31')

 TABLESPACE "D31"

 NOCOMPRESS ),

 PARTITION "ACC132"

 TABLESPACE "D32"

 ( SUBPARTITION "SP_P32X32" VALUES ('32')

 TABLESPACE "D32"

 NOCOMPRESS )

 );


Step 2: Creating a sequnce and sequnce generation function where in the value of column scal_id is generated by step 2

CREATE SEQUENCE ScalIdSeq

  START WITH 1

   INCREMENT BY 1;

 create or replace function calc_scal_id (seq_name in VARCHAR2) RETURN NUMBER IS

next_seq number;

 BEGIN

    execute immediate 'select ' || seq_name || '.nextval from dual' into next_seq;

    return next_seq;

 END calc_scal_id;

/


Step 3: calling this function from step 2 in a trigger


CREATE or REPLACE trigger gen_scal_id 

BEFORE INSERT ON sales.acc1_sid

FOR EACH ROW

DECLARE

BEGIN

IF INSERTING THEN

     :NEW.scal_id := calc_scal_id('ScalIdSeq');     

END IF;

END;

/


Step 4: trying to insert


  insert into sales.acc1_sid(ORGANIZATION_ID,DELETED) values ('0d123,'0');

          *

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

Ideally it shall insert the first row in SUBPARTITION "SP_P1X1" ,since the value of the sequence is 1.

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • User_H3J7U
    User_H3J7U Member Posts: 630 Silver Trophy
    Accepted Answer 
    create table t1 (
 key varchar2(10 char) not null,
 skey varchar2(10 char)
)
partition by hash(key)
subpartition by list(skey) (
partition t1p1 (subpartition t1p1s1 values ('1')),
partition t1p2 (subpartition t1p2s2 values ('2'))
);

select 'insert into t1 (key, skey) values ('''||key||''', '''||skey||''') /*'
    ||case when to_char(ora_hash(key, 2-1)+1)=skey then 'valid' else 'ORA-14400' end||'*/;' "Would be?"
from (
 select t1.column_value key, t2.column_value skey
 from      table(ku$_vcnt('a','b','c')) t1
 cross join table(ku$_vcnt('1','2')) t2
) t;

Would be?
------------------------------------------------------------
insert into t1 (key, skey) values ('a', '1') /*ORA-14400*/;
insert into t1 (key, skey) values ('a', '2') /*valid*/;
insert into t1 (key, skey) values ('b', '1') /*ORA-14400*/;
insert into t1 (key, skey) values ('b', '2') /*valid*/;
insert into t1 (key, skey) values ('c', '1') /*valid*/;
insert into t1 (key, skey) values ('c', '2') /*ORA-14400*/;


    nparab
    · Share on TwitterShare on Facebook

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 630 Silver Trophy

    You have one subpartition for each partition. So, subpart-key must match partition hash.

    There are still many mistakes in this code. If this nonsense is just an example, it should have been made simpler.

    · Share on TwitterShare on Facebook
  • User_H3J7U
    User_H3J7U Member Posts: 630 Silver Trophy
    Accepted Answer 
    create table t1 (
 key varchar2(10 char) not null,
 skey varchar2(10 char)
)
partition by hash(key)
subpartition by list(skey) (
partition t1p1 (subpartition t1p1s1 values ('1')),
partition t1p2 (subpartition t1p2s2 values ('2'))
);

select 'insert into t1 (key, skey) values ('''||key||''', '''||skey||''') /*'
    ||case when to_char(ora_hash(key, 2-1)+1)=skey then 'valid' else 'ORA-14400' end||'*/;' "Would be?"
from (
 select t1.column_value key, t2.column_value skey
 from      table(ku$_vcnt('a','b','c')) t1
 cross join table(ku$_vcnt('1','2')) t2
) t;

Would be?
------------------------------------------------------------
insert into t1 (key, skey) values ('a', '1') /*ORA-14400*/;
insert into t1 (key, skey) values ('a', '2') /*valid*/;
insert into t1 (key, skey) values ('b', '1') /*ORA-14400*/;
insert into t1 (key, skey) values ('b', '2') /*valid*/;
insert into t1 (key, skey) values ('c', '1') /*valid*/;
insert into t1 (key, skey) values ('c', '2') /*ORA-14400*/;


    nparab
    · Share on TwitterShare on Facebook
  • nparab
    nparab Member Posts: 6 Blue Ribbon

    @User_H3J7U , Can you please help with "subpart-key must match partition hash" by a simpler example if you have handy, much appreciated.

    · Share on TwitterShare on Facebook
  • nparab
    nparab Member Posts: 6 Blue Ribbon

    @User_H3J7U ..many thanks

    · Share on TwitterShare on Facebook