In the very very first release of AIA, the cress-ref table did not have a unique/primary key. This could lead to duplicate records.
The following SQL script shows how to remove duplicate values from this table, it will save the 'double' records into a seperate table.
SET define OFF
SET echo OFF
SET termout ON
COLUMN XREF_TABLE_NAME FORMAT A20;
COLUMN XREF_COLUMN_NAME FORMAT A20;
COLUMN ROW_NUMBER FORMAT A20;
COLUMN VALUE FORMAT A30;
COLUMN IS_DELETED FORMAT A2;
COLUMN LAST_MODIFIED FORMAT A16;
COLUMN LAST_ACCESSED FORMAT A16;
COLUMN XREF_DATA_ROWID FORMAT A32;
PROMPT DROP OLD TABLE XREF_DATA_CLEANUP
DROP TABLE xref_data_cleanup;
PROMPT CREATE XREF_DATA_CLEANUP TABLE WITH DOUBLE VALUES;
CREATE TABLE XREF_DATA_CLEANUP AS
SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, ROW_NUMBER
, VALUE
, IS_DELETED
, LAST_MODIFIED
, LAST_ACCESSED
, ROWID XREF_DATA_ROWID
FROM XREF_DATA
WHERE EXISTS
(
SELECT ''
FROM XREF_DATA X
WHERE X.VALUE = XREF_DATA.VALUE
AND X.XREF_TABLE_NAME = XREF_DATA.XREF_TABLE_NAME
HAVING COUNT(*) > 1
GROUP BY X.VALUE
);
PROMPT COUNT DOUBLE VALUES RECORDS
SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, VALUE
, IS_DELETED
, COUNT(1)
FROM XREF_DATA
HAVING COUNT(1) > 1
GROUP BY
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, VALUE
, IS_DELETED;
PROMPT LIST DOUBLE VALUE RECORDS
SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, ROW_NUMBER
, VALUE
, IS_DELETED
, TO_CHAR((TO_DATE('01/01/1970','mm/dd/yyyy')
+ LAST_MODIFIED/86400000), 'dd-mm-yyyy hh24:mi:ss')
LAST_MODIFIED
, TO_CHAR((TO_DATE'01/01/1970','mm/dd/yyyy')
+ LAST_ACCESSED/86400000), 'dd-mm-yyyy hh24:mi:ss')
LAST_ACCESSED
, XREF_DATA_ROWID
FROM XREF_DATA_CLEANUP
ORDER BY
ROW_NUMBER
, XREF_TABLE_NAME
, XREF_COLUMN_NAME;
PROMPT LIST UNDOUBLED VALUE RECORDS
SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, ROW_NUMBER
, VALUE
, IS_DELETED
, TO_CHAR((TO_DATE('01/01/1970','mm/dd/yyyy')
+ LAST_MODIFIED/86400000), 'dd-mm-yyyy hh24:mi:ss')
LAST_MODIFIED
, TO_CHAR((TO_DATE('01/01/1970','mm/dd/yyyy')
+ LAST_ACCESSED/86400000), 'dd-mm-yyyy hh24:mi:ss')
LAST_ACCESSED
FROM XREF_DATA X
WHERE X.ROWID > ANY
(
SELECT XX.ROWID
FROM XREF_DATA XX
WHERE XX.VALUE = X.VALUE
AND XX.XREF_TABLE_NAME = X.XREF_TABLE_NAME
)
ORDER BY
ROW_NUMBER
, XREF_TABLE_NAME
, XREF_COLUMN_NAME;
PROMPT DELETE DOUBLE RECORDS IN ORGINALE XREF-DATA TABLE.
DELETE
FROM xref_data x
WHERE X.ROWID > ANY
(
SELECT XX.ROWID
FROM XREF_DATA XX
WHERE XX.VALUE = X.VALUE
AND XX.XREF_TABLE_NAME = X.XREF_TABLE_NAME
);
PROMPT CHECK IF DUPLICATED VALUES ARE CORRECT DELETED.
PROMPT THE OUTCOME SHOULD BE 0 ROWS SELECTED.
SELECT XREF_TABLE_NAME
, XREF_COLUMN_NAME
, VALUE
, IS_DELETED
, COUNT(1)
FROM AIA.XREF_DATA
HAVING COUNT(1) > 1
GROUP BY
XREF_TABLE_NAME
, XREF_COLUMN_NAME
VALUE
IS_DELETED;
PROMPT CHECK THAT NO RECORDS ARE LOSSED.
PROMPT CHECK THAT THE AMOUNT OF ROWS ARE EQUAL
PROMPT AS THE RESULTS OF PROMTS COUNT DOUBLE VALUES RECORDS.
SELECT XRF.*
FROM XREF_DATA XRF
, (
SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, VALUE
, IS_DELETED
, COUNT(1)
FROM XREF_DATA_CLEANUP
HAVING COUNT(1) > 1
GROUP BY XREF_TABLE_NAME
, XREF_COLUMN_NAME
,VALUE
IS_DELETED
) DUB
WHERE XRF.XREF_TABLE_NAME = DUB.XREF_TABLE_NAME
AND XRF.XREF_COLUMN_NAME = DUB.XREF_COLUMN_NAME
AND XRF.VALUE = DUB.VALUE;
