Project

General

Profile

« Previous | Next » 

Revision 6952

Added by ben leinfelder about 11 years ago

eliminate the cross product that occurred when updating xml_access with a join

View differences:

upgrade-db-to-2.0.0-oracle.sql
69 69 
 * Generate GUIDs for docid.rev
70 70 
 */
71 71 
INSERT INTO identifier (docid, rev, guid)
72 
	SELECT docid, rev, docid || '.' || rev FROM xml_documents;
73 

  		




  74
  
  
    
INSERT INTO identifier (docid, rev, guid)


  		




  75
  
  
    
	SELECT docid, revisionid, docid || '.' || revisionid 


  		




  76
  
  
    
	FROM xml_revisions


  		




  77
  
  
    
	WHERE docid || '.' || revisionid NOT IN (SELECT guid from identifier);


  		




  78
  
  
    

  		




  
  72
  
    
	SELECT docid, rev, docid || '.' || rev FROM xml_documents


  		




  
  73
  
    
	UNION	


  		




  
  74
  
    
	SELECT docid, rev, docid || '.' || rev FROM xml_revisions;


  		




  79
  75
  
    
/*


  		




  80
  76
  
    
 *  Allow guid in xml_access table (for system metadata)


  		




  81
  77
  
    
*/


  		




  ......




  125
  121
  
    
FROM identifier


  		




  126
  122
  
    
GROUP BY docid;


  		




  127
  123
  
    

  		




  
  124
  
    
/** create some indexes to speed up the join **/


  		




  
  125
  
    
CREATE INDEX maxid_docid_index ON max_identifier (docid);


  		




  
  126
  
    
CREATE INDEX maxid_guid_index ON max_identifier (docid);


  		




  
  127
  
    
CREATE INDEX accessfileid_index on xml_access (accessfileid);


  		




  
  128
  
    
CREATE INDEX xml_access_guid_index on xml_access (guid);


  		




  
  129
  
    

  		




  128
  130
  
    
/** only need to join on docid since there is a single row for the max docid **/


  		




  129
  
  
    
UPDATE xml_access


  		




  
  131
  
    
UPDATE xml_access xa


  		




  130
  132
  
    
SET accessfileid = maxid.guid


  		




  131
  
  
    
FROM max_identifier maxid, xml_access xa


  		




  
  133
  
    
FROM max_identifier maxid


  		




  132
  134
  
    
WHERE xa.accessfileid = maxid.docid


  		




  133
  135
  
    
AND xa.guid IS NOT null;


  		




  134
  136
  
    

  		




  
  137
  
    
DROP INDEX maxid_docid_index;


  		




  
  138
  
    
DROP INDEX maxid_guid_index;


  		




  
  139
  
    
DROP INDEX accessfileid_index;


  		




  
  140
  
    
DROP INDEX xml_access_guid_index;


  		




  
  141
  
    

  		




  135
  142
  
    
DROP TABLE max_identifier;


  		




  136
  143
  
    

  		




  137
  144
  
    
/**


  		












Also available in:  Unified diff