Project

General

Profile

« Previous | Next » 

Revision 7455

Added by ben leinfelder over 11 years ago

additional db indexes for pathquery performance
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5696

View differences:

src/xmltables-oracle.sql
238 238 
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
239 239 
CREATE INDEX xml_documents_idx2 ON xml_documents (lower(user_owner));
240 240 
CREATE INDEX xml_documents_idx3 ON xml_documents (rootnodeid);
241 
CREATE INDEX xml_documents_idx5 ON xml_documents (docid, rev);
241 242 

  		




  
  243
  
    

  		




  242
  244
  
    
/*


  		




  243
  245
  
    
 * Revised Documents -- table to store XML documents saved after an UPDATE


  		




  244
  246
  
    
 *                    or DELETE


  		




  ......




  300
  302
  
    
CREATE INDEX xml_access_idx3 ON xml_access (perm_type);


  		




  301
  303
  
    
CREATE INDEX xml_access_idx4 ON xml_access (perm_order);


  		




  302
  304
  
    
CREATE INDEX xml_access_idx5 ON xml_access (subtreeid);


  		




  
  305
  
    
CREATE INDEX xml_access_idx6 on xml_access(guid);


  		




  303
  306
  
    

  		




  304
  307
  
    

  		




  305
  308
  
    
/*


  		




  ......




  364
  367
  
    
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);


  		




  365
  368
  
    
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);


  		




  366
  369
  
    
CREATE INDEX xml_path_index_idx4 ON xml_path_index (nodedatadate);


  		




  
  370
  
    
CREATE INDEX xml_path_index_idx6 ON xml_path_index (docid);


  		




  367
  371
  
    

  		




  368
  372
  
    

  		




  369
  
  
    

  		




  370
  
  
    

  		




  371
  373
  
    
CREATE TABLE xml_relation (


  		




  372
  374
  
    
	relationid    NUMBER(20) PRIMARY KEY, -- unique id


  		




  373
  375
  
    
	docid         VARCHAR2(250),          -- the docid of the package file


  		




  ......




  451
  453
  
    
   docid  VARCHAR2(250),  -- the local document id #


  		




  452
  454
  
    
   rev    NUMBER(8)       -- the revision part of the local identifier


  		




  453
  455
  
    
);


  		




  
  456
  
    
CREATE INDEX identifier_guid on identifier(guid);


  		




  
  457
  
    
CREATE INDEX identifier_docid on identifier(docid);


  		




  
  458
  
    
CREATE INDEX identifier_rev on identifier(rev);


  		




  
  459
  
    
CREATE INDEX identifier_docid_rev on identifier(docid, rev);


  		




  454
  460
  
    

  		




  455
  461
  
    
/*


  		




  456
  462
  
    
 * accesssubtree -- table to store access subtree info


  		












  

    
      src/xmltables-postgres.sql
    
  





  170
  170
  
    
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);


  		




  171
  171
  
    
CREATE INDEX xml_documents_idx2 ON xml_documents (lower(user_owner));


  		




  172
  172
  
    
CREATE INDEX xml_documents_idx3 ON xml_documents (rootnodeid);


  		




  
  173
  
    
CREATE INDEX xml_documents_idx5 ON xml_documents (docid, rev);


  		




  173
  174
  
    

  		




  174
  175
  
    
/*


  		




  175
  176
  
    
 * Revised Documents -- table to store XML documents saved after an UPDATE


  		




  ......




  225
  226
  
    
CREATE INDEX xml_access_idx3 ON xml_access (perm_type);


  		




  226
  227
  
    
CREATE INDEX xml_access_idx4 ON xml_access (perm_order);


  		




  227
  228
  
    
CREATE INDEX xml_access_idx5 ON xml_access (subtreeid);


  		




  
  229
  
    
CREATE INDEX xml_access_idx6 on xml_access(guid);


  		




  228
  230
  
    
/*


  		




  229
  231
  
    
 * ALTER TABLE xml_access ADD COLUMN guid text;


  		




  230
  232
  
    
*/


  		




  ......




  283
  285
  
    
   rev    INT8,          -- the revision part of the local identifier


  		




  284
  286
  
    
   CONSTRAINT identifier_pk PRIMARY KEY (guid)


  		




  285
  287
  
    
);


  		




  
  288
  
    
CREATE INDEX identifier_guid on identifier(guid);


  		




  
  289
  
    
CREATE INDEX identifier_docid on identifier(docid);


  		




  
  290
  
    
CREATE INDEX identifier_rev on identifier(rev);


  		




  
  291
  
    
CREATE INDEX identifier_docid_rev on identifier(docid, rev);


  		




  286
  292
  
    

  		




  287
  293
  
    
/*


  		




  288
  294
  
    
 * Table used to store all document identifiers for system metadata objects


  		




  ......




  425
  431
  
    
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);


  		




  426
  432
  
    
CREATE INDEX xml_path_index_idx4 ON xml_path_index (upper(nodedata));


  		




  427
  433
  
    
CREATE INDEX xml_path_index_idx5 ON xml_path_index (nodedatadate);


  		




  
  434
  
    
CREATE INDEX xml_path_index_idx6 ON xml_path_index (docid);


  		




  428
  435
  
    

  		




  429
  436
  
    
/*


  		




  430
  437
  
    
 * harvest_site_schedule -- table to store harvest sites and schedule info


  		












  

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





  5
  5
  
    
ALTER TABLE systemMetadataReplicationStatus RENAME TO smReplicationStatus;


  		




  6
  6
  
    

  		




  7
  7
  
    
/*


  		




  
  8
  
    
 * Add some additional indexes for search


  		




  
  9
  
    
 */


  		




  
  10
  
    
CREATE INDEX xml_documents_idx5 ON xml_documents (docid, rev);


  		




  
  11
  
    

  		




  
  12
  
    
CREATE INDEX xml_access_idx6 on xml_access(guid);


  		




  
  13
  
    

  		




  
  14
  
    
CREATE INDEX identifier_guid on identifier(guid);


  		




  
  15
  
    
CREATE INDEX identifier_docid on identifier(docid);


  		




  
  16
  
    
CREATE INDEX identifier_rev on identifier(rev);


  		




  
  17
  
    
CREATE INDEX identifier_docid_rev on identifier(docid, rev);


  		




  
  18
  
    

  		




  
  19
  
    
CREATE INDEX xml_path_index_idx6 ON xml_path_index (docid);


  		




  
  20
  
    

  		




  
  21
  
    
/*


  		




  8
  22
  
    
 * update the database version


  		




  9
  23
  
    
 */


  		




  10
  24
  
    
UPDATE db_version SET status=0;


  		












  

    
      src/upgrade-db-to-2.0.5-oracle.sql
    
  





  6
  6
  
    
 * for use in 2.0.5. So, we do nothing here for Oracle deployments.


  		




  7
  7
  
    
 */


  		




  8
  8
  
    

  		




  
  9
  
    
/*


  		




  
  10
  
    
 * Add some additional indexes for search


  		




  
  11
  
    
 */


  		




  
  12
  
    
CREATE INDEX xml_documents_idx5 ON xml_documents (docid, rev);


  		




  9
  13
  
    

  		




  
  14
  
    
CREATE INDEX xml_access_idx6 on xml_access(guid);


  		




  
  15
  
    

  		




  
  16
  
    
CREATE INDEX identifier_guid on identifier(guid);


  		




  
  17
  
    
CREATE INDEX identifier_docid on identifier(docid);


  		




  
  18
  
    
CREATE INDEX identifier_rev on identifier(rev);


  		




  
  19
  
    
CREATE INDEX identifier_docid_rev on identifier(docid, rev);


  		




  
  20
  
    

  		




  
  21
  
    
CREATE INDEX xml_path_index_idx6 ON xml_path_index (docid);


  		




  
  22
  
    

  		




  
  23
  
    

  		




  10
  24
  
    
/*


  		




  11
  25
  
    
 * update the database version


  		




  12
  26
  
    
 */


  		












Also available in:  Unified diff