Project

General

Profile

« Previous | Next » 

Revision 6952

Added by ben leinfelder about 12 years ago

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

View differences:

src/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
  
    
/**


  		












  

    
      src/upgrade-db-to-2.0.0-postgres.sql
    
  





  73
  73
  
    
 * Generate GUIDs for docid.rev


  		




  74
  74
  
    
 */


  		




  75
  75
  
    
INSERT INTO identifier (docid, rev, guid) 


  		




  76
  
  
    
	SELECT docid, rev, docid || '.' || rev FROM xml_documents;


  		




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


  		




  
  77
  
    
	UNION	


  		




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


  		




  
  79
  
    
--INSERT 0 156644


  		




  77
  80
  
    

  		




  78
  
  
    
INSERT INTO identifier (docid, rev, guid)


  		




  79
  
  
    
	SELECT docid, revisionid, docid || '.' || revisionid 


  		




  80
  
  
    
	FROM xml_revisions


  		




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


  		




  82
  
  
    

  		




  83
  81
  
    
/**


  		




  84
  82
  
    
 *  Add guid in xml_access table


  		




  85
  83
  
    
 */


  		




  ......




  102
  100
  
    
		xa.docid, xa.accessfileid, xa.begin_time, xa.end_time, xa.ticket_count, xa.subtreeid, xa.startnodeid, xa.endnodeid


  		




  103
  101
  
    
	FROM identifier id, xml_access xa


  		




  104
  102
  
    
	WHERE id.docid = xa.docid;


  		




  
  103
  
    
--INSERT 0 311224


  		




  105
  104
  
    

  		




  106
  105
  
    
/**


  		




  107
  106
  
    
 * Include inline data access rows -- they have a special guid: 'scope.docid.rev.index'


  		




  ......




  115
  114
  
    
		docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid


  		




  116
  115
  
    
	FROM xml_access


  		




  117
  116
  
    
	WHERE docid like '%.%.%.%';


  		




  
  117
  
    
--INSERT 0 18


  		




  118
  118
  
    

  		




  119
  119
  
    

  		




  120
  120
  
    
/**


  		




  ......




  130
  130
  
    
SELECT docid, MAX(rev), docid || '.' || MAX(rev)


  		




  131
  131
  
    
FROM identifier


  		




  132
  132
  
    
GROUP BY docid;


  		




  
  133
  
    
--INSERT 0 57841


  		




  133
  134
  
    

  		




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


  		




  135
  
  
    
UPDATE xml_access


  		




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


  		




  
  136
  
    
CREATE INDEX maxid_docid_index ON max_identifier (docid);


  		




  
  137
  
    
CREATE INDEX maxid_guid_index ON max_identifier (docid);


  		




  
  138
  
    
CREATE INDEX accessfileid_index on xml_access (accessfileid);


  		




  
  139
  
    
CREATE INDEX xml_access_guid_index on xml_access (guid);


  		




  
  140
  
    

  		




  
  141
  
    
UPDATE xml_access xa


  		




  136
  142
  
    
SET accessfileid = maxid.guid


  		




  137
  
  
    
FROM max_identifier maxid, xml_access xa


  		




  
  143
  
    
FROM max_identifier maxid


  		




  138
  144
  
    
WHERE xa.accessfileid = maxid.docid


  		




  139
  145
  
    
AND xa.guid IS NOT null;


  		




  
  146
  
    
--UPDATE 310427


  		




  140
  147
  
    

  		




  
  148
  
    
DROP INDEX maxid_docid_index;


  		




  
  149
  
    
DROP INDEX maxid_guid_index;


  		




  
  150
  
    
DROP INDEX accessfileid_index;


  		




  
  151
  
    
DROP INDEX xml_access_guid_index;


  		




  
  152
  
    

  		




  141
  153
  
    
DROP TABLE max_identifier;


  		




  142
  154
  
    

  		




  143
  155
  
    
/**


  		




  144
  156
  
    
 * Remove old access rules


  		




  145
  157
  
    
 */


  		




  146
  158
  
    
DELETE FROM xml_access WHERE guid is null;


  		




  
  159
  
    
--DELETE 105432


  		




  147
  160
  
    

  		




  148
  161
  
    
/**


  		




  149
  162
  
    
 * clean up the xml_access table


  		












Also available in:  Unified diff