Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00600 durring merge on IOT (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
ORA-00600 durring merge on IOT [message #348693] Wed, 17 September 2008 11:40 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I am getting an ORA-00600 error in executing a merge statement on an index-organized table. I know this is an internal Oracle error and Oracle support if my most likely route for resolution, but I figured it was worth a try on the off chance someone has experienced the same problem, also I have never actually used merge before in practice so there is also the possibility I am doing something I shouldn't be.

Here is the sample set-up:

--first test on normal table
CREATE       TABLE ngram_norm_test
      (ngram VARCHAR2(3), ngram_count NUMBER);

INSERT INTO ngram_norm_test
   SELECT DISTINCT    SUBSTR ('1FUYDZYB4RH497157', ROWNUM, 3)
                   || CASE
                         WHEN 18 - ROWNUM < 3
                            THEN SUBSTR ('1FUYDZYB4RH497157',
                                         1,
                                         3 - (18 - ROWNUM))
                      END ngram,
                   ROWNUM
              FROM DUAL
        CONNECT BY ROWNUM <= 17;

--then on an IOT
CREATE       TABLE ngram_iot_test
      (ngram VARCHAR2(3), ngram_count NUMBER, CONSTRAINT pk_ngram_test PRIMARY KEY (ngram))
      ORGANIZATION INDEX;


INSERT INTO ngram_iot_test
   SELECT DISTINCT    SUBSTR ('1FUYDZYB4RH497157', ROWNUM, 3)
                   || CASE
                         WHEN 18 - ROWNUM < 3
                            THEN SUBSTR ('1FUYDZYB4RH497157',
                                         1,
                                         3 - (18 - ROWNUM))
                      END ngram,
                   ROWNUM
              FROM DUAL
        CONNECT BY ROWNUM <= 17;


And now the merge statements

--works fine on regular table
MERGE INTO ngram_norm_test d
   USING (SELECT DISTINCT    SUBSTR ('1FUYDZYB4RH497157', ROWNUM, 3)
                          || CASE
                                WHEN 18 - ROWNUM < 3
                                   THEN SUBSTR ('1FUYDZYB4RH497157',
                                                1,
                                                3 - (18 - ROWNUM))
                             END ngram
                     FROM DUAL
               CONNECT BY ROWNUM <= 17) s
   ON (s.ngram = d.ngram)
   WHEN MATCHED THEN
      UPDATE
         SET d.ngram_count = d.ngram_count - 1
      DELETE
         WHERE d.ngram_count = 0
   WHEN NOT MATCHED THEN
      INSERT (d.ngram, d.ngram_count)
      VALUES (s.ngram, 1);

--on an IOT I get an error
MERGE INTO ngram_iot_test d
   USING (SELECT DISTINCT    SUBSTR ('1FUYDZYB4RH497157', ROWNUM, 3)
                          || CASE
                                WHEN 18 - ROWNUM < 3
                                   THEN SUBSTR ('1FUYDZYB4RH497157',
                                                1,
                                                3 - (18 - ROWNUM))
                             END ngram
                     FROM DUAL
               CONNECT BY ROWNUM <= 17) s
   ON (s.ngram = d.ngram)
   WHEN MATCHED THEN
      UPDATE
         SET d.ngram_count = d.ngram_count - 1
      DELETE
         WHERE d.ngram_count = 0
   WHEN NOT MATCHED THEN
      INSERT (d.ngram, d.ngram_count)
      VALUES (s.ngram, 1);


The full error is:

ORA-00600: internal error code, arguments: [kcbgcur_9], [405909861], [1], [4294967250], [2], [], [], []


The error appears to come from the DELETE portion of the merge, if I take this out the statement executes fine. So the workaround would be to do the delete as a separate step. I would like to avoid that if possible because right now the count column is not indexed and there really is zero reason besides this statement that it would need to be indexed.

Thanks for any help,
Andrew
Re: ORA-00600 durring merge on IOT [message #348695 is a reply to message #348693] Wed, 17 September 2008 11:43 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Out of interest - you committed after the insert?
Re: ORA-00600 durring merge on IOT [message #348696 is a reply to message #348695] Wed, 17 September 2008 11:50 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
andrew again wrote on Wed, 17 September 2008 11:43
Out of interest - you committed after the insert?


Good question...honestly I do not remember so I went back and tried it with and without a commit and got the same results.
Re: ORA-00600 durring merge on IOT [message #348697 is a reply to message #348693] Wed, 17 September 2008 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

Regards
Michel
Re: ORA-00600 durring merge on IOT [message #348699 is a reply to message #348697] Wed, 17 September 2008 11:55 Go to previous message
annagel
Messages: 220
Registered: April 2006
Senior Member
Michel Cadot wrote on Wed, 17 September 2008 11:52
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support


I am aware, as I stated I just figured I would try here first to see if anyone else had seen something similar....or as I have not used merge before to see if perhaps what I was doing was actually unsupported and it only worked on the normal table by chance.
Previous Topic: Return Associative Arrays in a Stored Proc (Oracle)
Next Topic: Select into problem
Goto Forum:
  


Current Time: Sat Dec 03 22:32:09 CST 2016

Total time taken to generate the page: 0.07717 seconds