3 Replies Latest reply: Nov 1, 2012 9:48 AM by mburazor RSS

    SQL Loader - "failed in the execution of the ODCIINDEXINSERT routine" error

    mburazor
      I’m trying to use SQL*Loader from 10.2 client to load millions of records into a 11.2 spatial table (points with longitude and latitude) and I’m getting the following error:
      SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 11 16:00:01 2012
      
      Copyright (c) 1982, 2005, Oracle.  All rights reserved.
      
      ORA-39776: fatal Direct Path API error loading table CISDWSYS.VEHICLE_LOCATION_FACT
      ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
      ORA-22054: underflow error
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 720
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 225
      SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
      
      Load completed - logical record count 45000.
      The table has a spatial index on VEHICLE_LOCATION column with MDSYS.SDO_GEOMETRY data type. Both table and spatial index are partitioned “monthly” by MESSAGE_DATETIME column. The database is 11.2 (11gR2), the client and SQL*Loader is version 10.2.
      My control file is:
      options (direct=yes, bindsize=20971520, readsize=20971520)
      Load data
      Append
      into table VEHICLE_LOCATION_FACT
      fields terminated by ","
      (VEHICLE_LOCATION_MESSAGE_ID  CONSTANT 222,
       MESSAGE_DATETIME Date 'YYYY-MM-DD HH24:MI:SS',
       ROUTE_NUMBER,RUN_NUMBER,VEHICLE_NUMBER,BADGE_NUMBER,
       TRIP_DIRECTION,GPS_LONGITUDE_NUMBER,GPS_LATITUDE_NUMBER,DIVISION_NUMBER,
       VEHICLE_LOCATION COLUMN OBJECT
          (
           SDO_GTYPE Integer EXTERNAL,
           SDO_SRID  CONSTANT 8265,
           SDO_POINT COLUMN OBJECT
             ( X     FLOAT EXTERNAL,
               Y     FLOAT EXTERNAL
             )
          )
      )
      I’m confused by this reported “underflow error” because all data in the data file are correct values. When I drop the spatial index and load the records by using the same control file, same data file and the same table, I don’t get any error and later I recreate the spatial index without errors. That should mean that the values in the data file are good, no “under” values.
      I want to avoid to have to drop the spatial index every morning before the data loading process, because it is a very large table and it takes 45-60 minutes to recreate that index partition, and that much time delay is not good.
      Can anyone please provide an advice or point to some link with examples of how to use “sqlldr” to load large number of records into a spatial table with spatial index?

      Thank you,
      Milan
        • 1. Re: SQL Loader - "failed in the execution of the ODCIINDEXINSERT routine" error
          damorgan
          The Oracle database's components are backward compatible ... not forward compatible.

          Use the 11gR2 tool with the 10g database ... not the other way around.

          This may not be the specific cause of your issue but it is certainly a starting point.

          PS: 10.2.0.1? What happened to patching during the last decade?
          • 2. Re: SQL Loader - "failed in the execution of the ODCIINDEXINSERT routine" error
            mburazor
            Thank you for your answer. I tried the same SQL*Loader data loading process from my PC with Oracle Client 11.2 and with the same data file, control file and the same database, and I got the same error:
            SQL*Loader: Release 11.2.0.1.0 - Production on Fri Oct 12 10:17:57 2012
            
            Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
            
            ORA-39776: fatal Direct Path API error loading table CISDWSYS.VEHICLE_LOCATION_FACT
            ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
            ORA-22054: underflow error
            ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 720
            ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 225
            SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
            
            Load completed - logical record count 45000.
            I noticed that number for "Load completed - logical record count" is not always same. For example, when data file size is around 300 MB the logical record count is 45000 and when the data file size is 500 MB then logical record count is 110000. The data in both files is of the same nature, just vehicles GPS locations (points).

            The control.log file is
            SQL*Loader: Release 11.2.0.1.0 - Production on Fri Oct 12 10:17:57 2012
            
            Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
            
            Control File:   Load_Vehicle_Locations_2.ctl
            Data File:      CIS_VehLoc_L05120603gps.txt
              Bad File:     CIS_VehLoc_L05120603gps.bad
              Discard File:  none specified
             
             (Allow all discards)
            
            Number to load: ALL
            Number to skip: 0
            Errors allowed: 50
            Continuation:    none specified
            Path used:      Direct
            
            Table VEHICLE_LOCATION_FACT, loaded from every logical record.
            Insert option in effect for this table: APPEND
            
               Column Name                  Position   Len  Term Encl Datatype
            ------------------------------ ---------- ----- ---- ---- ---------------------
            VEHICLE_LOCATION_MESSAGE_ID                               CONSTANT
                Value is '222'
            MESSAGE_DATETIME                    FIRST     *   ,       DATE YYYY-MM-DD HH24:MI:SS
            ROUTE_NUMBER                         NEXT     *   ,       CHARACTER            
            RUN_NUMBER                           NEXT     *   ,       CHARACTER            
            VEHICLE_NUMBER                       NEXT     *   ,       CHARACTER            
            BADGE_NUMBER                         NEXT     *   ,       CHARACTER            
            TRIP_DIRECTION                       NEXT     *   ,       CHARACTER            
            GPS_LONGITUDE_NUMBER                 NEXT     *   ,       CHARACTER            
            GPS_LATITUDE_NUMBER                  NEXT     *   ,       CHARACTER            
            DIVISION_NUMBER                      NEXT     *   ,       CHARACTER            
            VEHICLE_LOCATION                  DERIVED     *           COLUMN OBJECT        
            
            *** Fields in VEHICLE_LOCATION
            SDO_GTYPE                            NEXT     *   ,       CHARACTER            
            SDO_SRID                                                  CONSTANT
                Value is '8265'
            SDO_POINT                         DERIVED     *           COLUMN OBJECT        
            
            *** Fields in VEHICLE_LOCATION.SDO_POINT
            X                                    NEXT     *   ,       CHARACTER            
            Y                                    NEXT     *   ,       CHARACTER            
            *** End of fields in VEHICLE_LOCATION.SDO_POINT
            
            *** End of fields in VEHICLE_LOCATION
            
            
            ORA-39776: fatal Direct Path API error loading table CISDWSYS.VEHICLE_LOCATION_FACT
            ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
            ORA-22054: underflow error
            ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 720
            ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 225
            
            SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
            
            Table VEHICLE_LOCATION_FACT:
              0 Rows successfully loaded.
              0 Rows not loaded due to data errors.
              0 Rows not loaded because all WHEN clauses were failed.
              0 Rows not loaded because all fields were null.
            
              Date cache:
               Max Size:      1000
               Entries :       192
               Hits    :     44808
               Misses  :         0
            
            Bind array size not used in direct path.
            Column array  rows :    5000
            Stream buffer bytes:  256000
            Read   buffer bytes:20971520
            
            Total logical records skipped:          0
            Total logical records rejected:         0
            Total logical records discarded:        0
            Total stream buffers loaded by SQL*Loader main thread:        9
            Total stream buffers loaded by SQL*Loader load thread:        9
            
            Run began on Fri Oct 12 10:17:57 2012
            Run ended on Fri Oct 12 10:18:34 2012
            
            Elapsed time was:     00:00:37.16
            CPU time was:         00:00:00.32
            Version 10.2.0.1 is on the machine that I got to use to provide a proof-of-concept solution. Long story, I can't install or upgrade clients on the machine.

            I researched this on internet and I can't find anything on this combination of "failed ODCIINDEXINSERT routine" and "underflow error". The data values are good, without the spatial index present the SQL*Loader loads the same data ok and the spatial index is recreated without problems.

            Thank you,
            Milan
            • 3. Re: SQL Loader - "failed in the execution of the ODCIINDEXINSERT routine" error
              mburazor
              An Oracle document says that SQL*Loader direct path loading can't be used on a table with a spatial index (domain index), because spatial index is defined on a column with spatial data type and that is LOB type of column.

              The document is "Oracle® Database Data Cartridge Developer's Guide 11g Release 2 (11.2)" and the link is:

              http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/dom_idx.htm#ADDCI290

              Paragraph from this book about this data loading limitation is:

              >
              Domain Indexes and SQL Loader

              SQL*Loader conventional path loads and direct path loads are supported for tables on which domain indexes are defined, with two limitations:

              •The table must be heap-organized.

              •The domain index cannot be defined on a LOB column.

              To do a direct path load on a domain index defined on an IOT or on a LOB column, perform these tasks:

              1.Drop the domain index

              2.Do the direct path load in SQL*Loader.

              3.Re-create the domain indexes.
              >
              A solution in my case is to create daily partitions for this huge table with spatial (points) locations, since re-create spatial index partition in this case takes about 15 minutes and this is ok. That's much better than re-creating spatial index monthly partition which takes hours, or loading millions of records without direct path method (and no need to drop spatial index partition) which takes even more hours. Our process is based on daily data loading anyway, so it is ok for us.