Cleanup AIA Cross Reference Table

Due to various reasons, the cross referenece could be filled with data that you do not expect. For example, you have duplicate values in your table.

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;