Metacat

/**

 * Below given statement takes a lot of time to excute

 *

 * DELETE FROM xml_nodes WHERE rootnodeid NOT IN

 * (SELECT rootnodeid from xml_documents where rootnodeid is not NULL);

 *

 * Hence.....

 */

/**

* Drop the xml_nodes primark key....

*/

ALTER TABLE xml_nodes DROP CONSTRAINT xml_nodes_pk CASCADE;

/** rename xml_nodes to xml_nodes_2 */

ALTER TABLE xml_nodes RENAME TO xml_nodes_2;

/** create a new xml_nodes table with new primary and foreign keys*/

CREATE TABLE xml_nodes (

       nodeid          NUMBER(20),     -- the unique node id (pk)

       nodeindex       NUMBER(10),     -- order of nodes within parent

       nodetype        VARCHAR2(20),   -- type (DOCUMENT, COMMENT, PI,

                                       -- ELEMENT, ATTRIBUTE, TEXT)

       nodename        VARCHAR2(250),  -- the name of an element or attribute

       nodeprefix      VARCHAR2(50),   -- the namespace prefix of an element

                                       -- or attribute

       nodedata        VARCHAR2(4000), -- the data for this node (e.g.,

                                       -- for TEXT it is the content)

       parentnodeid    NUMBER(20),     -- index of the parent of this node

       rootnodeid      NUMBER(20),     -- index of the root node of this tree

       docid           VARCHAR2(250),  -- index to the document id

       date_created    DATE,

       date_updated    DATE,

       nodedatanumerical NUMBER,       -- the data for this node if

                                       -- it is a number

  CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),

  CONSTRAINT xml_nodes_root_fk

               FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,

  CONSTRAINT xml_nodes_parent_fk

               FOREIGN KEY (parentnodeid) REFERENCES xml_nodes

);

/** copy nodes from xml_nodes_2  to xml_nodes */

INSERT INTO xml_nodes (nodeid, nodeindex, nodetype, nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, date_created, date_updated, nodedatanumerical) SELECT n.nodeid, n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, n.nodedatanumerical FROM xml_nodes_2 n, xml_nodes_revisions r WHERE n.rootnodeid = r.rootnodeid(+) AND r.rootnodeid is NULL;

/** Drop old indexes **/

DROP INDEX xml_nodes_idx1;

DROP INDEX xml_nodes_idx2;

DROP INDEX xml_nodes_idx3;

/** Create new indexes **/

CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);

CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);

CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);

/** Add constaints which were deleted before moving xml_nodes to xml_nodes_2 */

ALTER TABLE xml_documents ADD CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;

ALTER TABLE xml_index ADD CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;

/** Drop xml_nodes_2 table */

DROP TABLE xml_nodes_2;

/** Done */