ORA-01410 on index organized table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ORA-01410 on index organized table

  1. #1
    Join Date
    Jun 2013
    Posts
    5

    ORA-01410 on index organized table

    On an Oracle RAC system (11.2.0.1.0)

    I have an index organized table:



    Code:
    CREATE TABLE ROLLUP_NWS_RAINFALL
    (
      TIMESTAMP  DATE,
      X          INTEGER,
      Y          INTEGER,
      VAL        BINARY_DOUBLE, 
      CONSTRAINT ROLLUP_NWS_RAINFALL$PK1
     PRIMARY KEY
     (TIMESTAMP, X, Y)
    )
    ORGANIZATION INDEX
    LOGGING
    TABLESPACE MAAP_DATA02
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          16M
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    COMPRESS 1
    PCTUSED    0
    STORAGE    (
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOLOGGING 
    PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
    MONITORING;
    I populate this table with this PL/SQL block:

    Code:
    declare
        x rollup_nws_rainfall_pkg.coordinateArrayType;
        y rollup_nws_rainfall_pkg.coordinateArrayType;
        val rollup_nws_rainfall_pkg.valArrayType;
        i integer;
    begin
        DBMS_OUTPUT.ENABLE(1000000);
        for i in 1..152
        loop
            x(i) := i;
            y(i) := 0;
            val(i) := 0;
        end loop;
        rollup_nws_rainfall_pkg.INSERTORUPDATERECORDSCNT(:sysdate, x, y, val, 100);
    end;
    It works as intended when I try to insert 151 records, but I get this error when I try to insert 152 (see for-loop)

    ORA-01410: invalid ROWID - line 49 (line with the cursor fetch statement)

    All records with matching timestamp are deleted first when more than 151 x,y,val records are inserted, when they should just be updated if different.

    Is there some limitation with PL/SQL tables that I can adjust, or some alternate collection type I can use to get around ORA-01410?

    Thanks


    PL/SQL block depends on the following types & packages:

    Code:
    CREATE OR REPLACE
    TYPE                MAAP.ROLLUP_NWS_RAINFALL_REC AS OBJECT
    (
      TIMESTAMP DATE,
      X INTEGER,
      Y INTEGER,
      VAL BINARY_DOUBLE
    )
    /
    
    CREATE OR REPLACE
    TYPE                  MAAP.ROLLUP_NWS_RAINFALL_TABLE AS table of ROLLUP_NWS_RAINFALL_REC
    /
    
    
    
    CREATE OR REPLACE package MAAP.ROLLUP_NWS_RAINFALL_PKG as
        type coordinateArrayType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
        type valArrayType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
        function MakeTableFromArrays(timestamp DATE, xArr coordinateArrayType, yArr coordinateArrayType, valArr valArrayType) return ROLLUP_NWS_RAINFALL_TABLE;
        procedure InsertOrUpdateRecordsCnt(argTimeStamp in DATE, argX in coordinateArrayType, argY in coordinateArrayType, argVals in valArrayType, argCount in NUMBER);
    end ROLLUP_NWS_RAINFALL_PKG;
    
    CREATE OR REPLACE package body MAAP.ROLLUP_NWS_RAINFALL_PKG as
    
    function MakeTableFromArrays(timestamp DATE, xArr coordinateArrayType, yArr coordinateArrayType, valArr valArrayType) return ROLLUP_NWS_RAINFALL_TABLE
    IS
        ttable ROLLUP_NWS_RAINFALL_TABLE;
        curRecIdx binary_integer;
        i binary_integer;
    BEGIN
        i := 1;
        ttable := ROLLUP_NWS_RAINFALL_TABLE();
        ttable.extend(xArr.count);
        curRecIdx := xArr.first;
        while curRecIdx IS NOT NULL
        LOOP
            ttable(i) := ROLLUP_NWS_RAINFALL_REC(timestamp, xArr(curRecIdx), yArr(curRecIdx), valArr(curRecIdx));
            curRecIdx := xArr.next(curRecIdx);
            i := i + 1;
        END LOOP;
        RETURN ttable;
    END MakeTableFromArrays;
    
    
    
    procedure InsertOrUpdateRecordsCnt(argTimeStamp in DATE, argX in coordinateArrayType, argY in coordinateArrayType, argVals in valArrayType, argCount in NUMBER)
    is
        ttable ROLLUP_NWS_RAINFALL_TABLE;
        ttablecnt integer;
        rtablecnt integer;
        deletecnt integer;
        ri varchar2(128);
        ts date;
        x integer;
        y integer;
        val binary_double;
        cursor c is 
            SELECT rowidtochar(r.rowid), r.timestamp, r.x, r.y, r.val FROM 
                (SELECT timestamp, x, y FROM TABLE (ttable) group by timestamp, x, y) t INNER JOIN 
                ROLLUP_NWS_RAINFALL r ON (t.timestamp = r.timestamp AND t.x = r.x AND t.y = r.y); 
    begin
        ttable := MakeTableFromArrays(argTimeStamp, argX, argY, argVals);
        SELECT COUNT(*) INTO ttablecnt FROM (SELECT timestamp, x, y FROM TABLE (ttable) group by timestamp, x, y);
        SELECT COUNT(*) INTO rtablecnt FROM ROLLUP_NWS_RAINFALL WHERE TIMESTAMP = argTimestamp;
        SELECT COUNT(*) INTO deletecnt FROM ROLLUP_NWS_RAINFALL N WHERE TIMESTAMP = argTimestamp AND NOT (N.ROWID IN (SELECT R.ROWID FROM 
            (SELECT timestamp, x, y FROM TABLE (ttable) group by timestamp, x, y) t INNER JOIN ROLLUP_NWS_RAINFALL r ON (t.timestamp = r.timestamp AND t.x = r.x AND t.y = r.y)));
        DBMS_OUTPUT.PUT_LINE('ttablecnt = ' || ttablecnt || ' rtablecnt = ' || rtablecnt || ' deletecnt = ' || deletecnt);
    
        open c;
        LOOP
            fetch c into ri, ts, x, y, val;
            exit when c%notfound; 
            DBMS_OUTPUT.PUT_LINE('ri '|| ri || ' timestamp ' || ts || ' x ' || x || ' y ' || y || ' val ' || val);
        END LOOP;
                      
        DELETE ROLLUP_NWS_RAINFALL WHERE TIMESTAMP = argTimestamp AND ROWID NOT IN (SELECT R.ROWID FROM 
            (SELECT timestamp, x, y FROM TABLE (ttable) group by timestamp, x, y) t INNER JOIN ROLLUP_NWS_RAINFALL r ON (t.timestamp = r.timestamp AND t.x = r.x AND t.y = r.y));
        IF argCount > 0 THEN
            MERGE INTO ROLLUP_NWS_RAINFALL r USING
            (SELECT timestamp, x, y, max(val) val FROM TABLE (ttable) group by timestamp, x, y) ri
            ON (r.timestamp = ri.timestamp AND r.x = ri.x AND r.y = ri.y)
            WHEN MATCHED THEN UPDATE SET
                r.val = ri.val
                WHERE r.val <> ri.val
            WHEN NOT MATCHED THEN INSERT
                (r.timestamp, r.x, r.y, r.val) VALUES (ri.timestamp, ri.x, ri.y, ri.val);
        END IF;       
    end InsertOrUpdateRecordsCnt;
    
    
    END ROLLUP_NWS_RAINFALL_PKG;
    /

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,977
    This is your issue. Do not use rowid to join or filter rows.
    It does not work. Join based on a max value or primary key.
    Rowid's are not guaranteed to stay the same, they can and will change.

    Code:
    AND ROWID NOT IN (SELECT R.ROWID FROM
    You should now smack your hand with a ruler three times!
    this space intentionally left blank

  3. #3
    Join Date
    Jun 2013
    Posts
    5
    The example I started with is from an authoritative source - uses rowids and analytics to trim out duplicate rows. I'm doing something a little different, *removing* all rows with matching timestamps that don't have a matching timestamp, X, and Y in the PL/SQL table constructed from the input parameter arrays, but using the same idea.

    http://asktom.oracle.com/pls/asktom/...15258974323143

  4. #4
    Join Date
    Jun 2013
    Posts
    5
    Problem solved with GTT (sigh)

    Code:
    CREATE GLOBAL TEMPORARY TABLE ROLLUP_NWS_RAINFALL_T
    (
      TIMESTAMP  DATE,
      X          INTEGER,
      Y          INTEGER,
      VAL        BINARY_DOUBLE 
    )
    ON COMMIT DELETE ROWS;
    and changed the procedure to dump records into the GTT and query that instead of querying the PL/SQL table directly seems to get around this umm... bug.

    Code:
    procedure InsertOrUpdateRecords(argTimeStamp in DATE, argX in coordinateArrayType, argY in coordinateArrayType, argVals in valArrayType)
    is
        ttable ROLLUP_NWS_RAINFALL_TABLE;
    --    cursor c is 
    --        SELECT r.timestamp, r.x, r.y FROM 
    --            (SELECT timestamp, x, y FROM TABLE (ttable) group by timestamp, x, y) t INNER JOIN 
    --            ROLLUP_NWS_RAINFALL r ON (t.timestamp = r.timestamp AND t.x = r.x AND t.y = r.y); 
    begin
        -- Added JPM 8/18/11 to trim out invalid records later removed by NCRFC
        -- Changed delete statement to remove only records without x,y match in ttable - JPM 6/13
        ttable := MakeTableFromArrays(argTimeStamp, argX, argY, argVals);
    
        INSERT INTO ROLLUP_NWS_RAINFALL_T (SELECT timestamp, x, y, MAX(val) FROM TABLE (ttable) group by timestamp, x, y); 
                      
        DELETE ROLLUP_NWS_RAINFALL WHERE TIMESTAMP = argTimestamp AND ROWID NOT IN (SELECT R.ROWID FROM 
            (SELECT timestamp, x, y FROM ROLLUP_NWS_RAINFALL_T) t INNER JOIN ROLLUP_NWS_RAINFALL r ON (t.timestamp = r.timestamp AND t.x = r.x AND t.y = r.y));
        MERGE INTO ROLLUP_NWS_RAINFALL r USING
        (SELECT timestamp, x, y, max(val) val FROM TABLE (ttable) group by timestamp, x, y) ri
        ON (r.timestamp = ri.timestamp AND r.x = ri.x AND r.y = ri.y)
        WHEN MATCHED THEN UPDATE SET
            r.val = ri.val
            WHERE r.val <> ri.val
        WHEN NOT MATCHED THEN INSERT
            (r.timestamp, r.x, r.y, r.val) VALUES (ri.timestamp, ri.x, ri.y, ri.val);
    end InsertOrUpdateRecords;

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by mannsjp View Post
    ...seems to get around this umm... bug.
    This is like saying a hammer is faulty 'cause you cannot take a picture with it!
    It is NOT a bug, please read what ROWID is and how it works.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jun 2013
    Posts
    5
    The ROWID trick came from an authoritative source:

    http://asktom.oracle.com/pls/asktom/...15258974323143

    I'm doing almost the same thing, but instead of removing duplicates, I'm removing pre-existing records that don't match the contents of the PL/SQL table.
    Last edited by mannsjp; 06-21-2013 at 01:19 PM.

  7. #7
    Join Date
    Jun 2013
    Posts
    5
    Oops, wrong link - here's the right one.

    http://asktom.oracle.com/pls/asktom/...15258974323143

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,977
    Quote Originally Posted by mannsjp View Post
    Oops, wrong link - here's the right one.

    http://asktom.oracle.com/pls/asktom/...15258974323143
    That link is from January 2004. He was probably using 8i. Therefore using rowid might have been less of an
    issue then. Given that you can have row migration turned on in a database, his advice might be different today.
    Personally, I would not do it that way. There is probably a better way to do this than rowid.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width