-
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;
/
-
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!
-
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
-
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;
-
Originally Posted by mannsjp
...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.
-
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.
-
-
Originally Posted by mannsjp
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|